[Building Sakai] deadlock when storing events

[Berg, Alan]

Hi,

Because the issue has occurred  before there is a Sakai startup check on the session table that should warn in the logs. I believe it was added to trunk about 6 months ago. Not sure if it is in 2.7.1 or 2.8, should be.  Aaron Z wrote the code, Aaron is this in 2.8 alpha 3?

Interestingly we had the same issue for another LMS about 6 years ago at UvA, so it is a common issue across systems.

Alan

Alan Berg
QA Director - The Sakai Foundation

Senior Developer / Quality Assurance
Group Education and Research Services
Central Computer Services
University of Amsterdam

http://home.uva.nl/a.m.berg

________________________________________
From: sakai-dev-bounces at collab.sakaiproject.org [sakai-dev-bounces at collab.sakaiproject.org] on behalf of Steve Swinsburg [steve.swinsburg at gmail.com]
Sent: 05 November 2010 12:34
To: David Horwitz
Cc: sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] deadlock when storing events

Interestingly, we've never cleaned out the event or session table and don't have performance issues. And it's been running since January 2007. I'd have to get some stats to tell you how many rows we have. MySQL db too.

Cheers
Steve

Sent from my iPhone

On 05/11/2010, at 18:06, David Horwitz <david.horwitz at uct.ac.za> wrote:

> Hi Joshua,
>
> Yes if you just let the event table grow you will hit issues. We move
> old events and sessions to another db on another server so we have the
> historical data but keep good performance in production.
>
> The perl script we use is here:
>
> http://source.cet.uct.ac.za/svn/sakai/scripts/trunk/archive_stats.pl
>
> Basically its safe to remove events and sessions where the session is
> closed. You however do need the events service for Sakai to run properly
> a number of low level services rely on it to run properly in clusters.
>
> David
>
> On 11/05/2010 12:54 AM, 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"
>>
> _______________________________________________
> 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"
_______________________________________________
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"