[Building Sakai] Add Particicpants MySQL table lock; two possible solutions

[Charles Hedrick]

OK, here's my diagnosis:

In theory all Sakai code that does updates ends with either a commit or a rollback, and it restores autocommit to the default, which should be true.

In practice there are cases where this doesn't happen. This results in the transaction remaining open, with rows locked. I've seen it for OSP presentation layouts, and a couple of different realm tables. The realm table is what causes the problem with add participants.

For some reason this doesn't happen with our Mysql 4.1 system, but it happens repeatably on startup under Mysql 5.1.

Returning a connection calls passivateObject, which in dbcp would normally set autocommit=1. That will commit any transaction that remains open. However Sakai has overridden the default implementation with  SakaiPoolableConnectionFactory.  In the Sakai version, passivateObject does not set autocommit true. activate object still sets it true, so connections are properly initialized, but if a transaction is not properly closed, it isn't finally closed until the next time the connection is reused by the pool. Because of the way dbcp uses the pool, this can take quite some time if you have lots of connections in the pool.

Solution 1: 

In sakai.properties, add

rollbackOnBorrow at javax.sql.BaseDataSource=true

That will cause any transaction that hasn't been closed to be rolled back. This is the theoretically correct thing. A few connections seem to use the default dbcp factory. Those will be committed rather than rolled back, which in practice should be good enough.

Solution 2:

In SakaiPoolableConnectionFactory:passivateObject, uncomment conn.setAutoCommit(true);  

This is probably more likely to produce effect that the author intended, although in theory it's probably more dangerous.

This solution will cause extra database transactions, except the we normally run mysql with elideSetAutoCommits=true&useLocalSessionState=true, which should reduce the number of extra set autocommits (and also rollbacks, I believe).

For the moment I'm going to set our 5.1 test system to use 

rollbackOnBorrow at javax.sql.BaseDataSource=true
url at javax.sql.BaseDataSource=jdbc:mysql://sakai-db.oirt.rutgers.edu:3306/sakai?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096&elideSetAutoCommits=true&useLocalSessionState=true

However I need to do more testing to verify that it doesn't actually generate significantly more commands to the server,  nor cause any other odd problems.

I think this is actually a fairly serious problem in Sakai. We need to find and fix the code that doesn't close off transactions, but I recommend adopting one of the solutions mentioned here by default.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2421 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20100310/a389530d/attachment.bin