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

transactions not closed off, tables left locked

    XMLWordPrintable

    Details

    • 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:
      None
    • Previous Issue Keys:
      KNL-433

      Description

      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@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@javax.sql.BaseDataSource=true
      url@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.

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  k1team KERNEL TEAM (Inactive)
                  Reporter:
                  hedrick Charles Hedrick
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration