Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-39346

transactions not closed off, tables left locked



    • Type: Bug
    • Status: CLOSED
    • Priority: Critical
    • Resolution: Won't Fix
    • Affects Version/s: 2.7.x, 2.8.x
    • Fix Version/s: None
    • Component/s: Kernel
    • Labels:
    • Previous Issue Keys:


      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


      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


      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.

        Gliffy Diagrams



              Issue Links



                  k1team KERNEL TEAM (Inactive)
                  hedrick Charles Hedrick
                  0 Vote for this issue
                  4 Start watching this issue



                      Git Integration