[Building Sakai] autocommit

[Charles Hedrick]

I'm a bit interested in how auto-commit it used in Sakai. Code  
typically does

oldcommit = conn.getAutoCommit.
conn.setAutoCommit(false);

do transacftion

conn.commit
conn.setautocommit(oldcommit);

The problem with this is the it tends to make the normal state of  
autocommit undefined. All it takes is one application to not put it  
back, and it ends up false. I've seen a number of cases where either  
in an error code on sometimes in all causes, the restoration of  
autocommit is not done.

We just ran into a situation in Samigo (deleting uploaded files) that  
fails in production but works in the 2.6 QA servers. It does an update  
without either starting a transaction or committing. This is probably  
a bug, but in theory doing a single update is OK in autocommit mode.  
You don't really need a transaction unless you're doing several things  
that need to be consistent. However if the system isn't in autocommit  
mode, a single update without a commit will not have the desired effect.

I maintain that we need a convention that says when you're not in a  
transaction, the system should be autocommit on. Then the code would be

conn.setAutoCommit(false);

do transacftion

conn.commit
conn.setautocommit(true)

That would be much more likely to leave the system in a known state.  
However I would also suggest that code ought not to rely on the  
current state of autocommit, but if you want to use autocommit you  
should set it. At least with Mysql, the optimal setting on the  
connector keeps track of the state of autocommit and doesn't send  
unnecessary ones to the server.

Another option, which might result in the  most reliable code, would  
be to set autocommit to an explicitly random value, 50% true and 50%  
false. That would make it more likely that code that depends upon the  
current state of autocommit would be found in testing.