[Building Sakai] kernel issues that you may want to deal with in 2.8.1

[Charles Hedrick]

Here are some issues unresolved in 2.8.1 that large sites may want to do something about:

KNL-815. deadlocks from realm updates. The jira has a recommended patch. Note that this patch assumes that transact always does either commit or rollback. This should be the case, but see my comments below.
* Rutgers production uses the patch, but will be moving to the second version of the patch (also in the jira) shortly.

KNL-811. need to background the email sent form announcements if in a large site. Otherwise you may get duplicate copies. The jira has a recommended patch
* Rutgers production uses the patch

KNL-433. make sure transactions are terminated. Without this tables can be left locked and unnecessary snapshots can lie around for a long time in innodb. I recommend uncommenting the conn.setAutoCommit(true); in SakaiPoolableConnectionFactory.java:passivateObject, or replacing it with conn.commit().
       The advantage of commit is that it produces one query, while setAutoCommit(true) produces two (because reallocating the connection from the pool generates setAutoCommit(false)). I believe that commit and setAutoCommit(true) do the same thing in this context.
       The ideal situation would be to make all transactions do either commit or rollback, but we aren't there yet. One slight improvement would be to modify the BasicSqlService.java:transact to do a rollback in a "finally" if commit wasn't done, rather than in a bunch of catches. However I'm not sure the unterminated transactions are actually coming from transact.
* Rurtgers production uses setAutoCommit(true), but will move to commit at some point


In addition, I recommend adding useLocalSessionState=true to the mysql URL in sakai.properties. That causes the jdbc connector to track the state of things such as transaction isolation level, and not transmit queries unless they are needed. Without this an unnecessary select is done every time a connection is borrowed from the pool, to check the current state of transaction isolation.
* Rutgers production uses useLocalSessionState=true.

I do not recommend adding useLocalTransactionState. I considered it, but found that it was not generating commits in some cases where I would have expected them. Furthermore, it normally won't do anything if you're running a mysql version < 6.0 (assuming you have the query cache on).