[Building Sakai] deadlock when storing events

[Joshua Swink]

We had a deadlock when writing to the event table today. Fortunately
the system recovered, but many users had slow service and/or HTTP 500
errors for a few minutes.

Any advice on preventing this would be appreciated. This table has
been troublesome for a while now. We're looking into dropping all but
the most recent data, such as the last two years, or possibly not
using the event service at all. Is this an important service that we'd
miss, or is it just there for forensics?

Thanks for any and all help with this.

--
Joshua Swink

2010-11-04 13:21:51,647  WARN
org.sakaiproject.event.impl.ClusterEventTracking$$EnhancerByCGLIB$$207e2f09
org.sakaiproject.db.impl.BasicSqlService - Sql.dbWrite(): error code:
60 sql: insert into SAKAI_EVENT
(EVENT_ID,EVENT_DATE,EVENT,REF,SESSION_ID,EVENT_CODE,CONTEXT) values
   (SAKAI_EVENT_SEQ.NEXTVAL,?, ?, ?, ?, ?, ?)  binds:
20101104202143788 user.login  879ccbca-8d5d-4037-be7c-8aad0bda8f34 m
null
java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3423)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
        at org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1205)
        at org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1060)
        at org.sakaiproject.event.impl.ClusterEventTracking.writeBatchEvents(ClusterEventTracking.java:335)
        at org.sakaiproject.event.impl.ClusterEventTracking.run(ClusterEventTracking.java:490)
        at java.lang.Thread.run(Thread.java:595)
2010-11-04 13:21:51,657  WARN
org.sakaiproject.event.impl.ClusterEventTracking$$EnhancerByCGLIB$$207e2f09
org.sakaiproject.event.impl.ClusterEventTracking -
org.sakaiproject.event.impl.ClusterEventTracking
$$EnhancerByCGLIB$$207e2f09 at 17d91382.writeBatchEvents:
java.lang.RuntimeException: SqlService.dbWrite failure