[Building Sakai] deadlock when storing events

[Drew Zhu]

Do you happen to have any bitmap index in the SAKAI schema? You can 
check with the following query:
select index_name,index_type from user_indexes where index_type='BITMAP';

BITMAP index in high transaction system can cause deadlock like that.

Thanks,
Drew

Joshua Swink wrote:
> 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
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
>
>