[Building Sakai] autocommit

[Charles Hedrick]

Then some behavior I saw is very mysterious.

I've just run into the same problem in two different applications: my  
own Adobe Connect integration (I'm doing my own because I have a  
hosted license), and Samigo. In both cases the code failed to do a  
commit. In production, which is Mysql 4.1, it fails. In my development  
system, which is Mysql 5.0, the Adobe Connect actually worked. In one  
of the QA servers, the Samigo code worked. I thought the problem was  
that by chance we got autocommit = false, but that it was typically  
true.

If it starts out as false, then it should remain that way. The normal  
query code is

save old value
set to false
do action
commit
restore old value

If the initial value is false, then it should remain that way even if  
someone forgets the restore.

So the mystery is why the buggy code ever worked. I'm going to assume  
that someone is doing a commit implicitly, but only in some  
configurations. Without further investigation, it could be the  
connector, mysql, or dbcp.


On Jul 28, 2009, at 4:21 PM, Speelmon, Lance Day wrote:

> I am a bit rusty here, but we want to ensure that autocommit is never
> enabled.  It creates unpredictable behavior.  Perhaps it would be
> better to introduce code (if needed) that ensures autocommit is always
> off.  Maybe that is what you are saying Chuck?  Best, L
>
>
> Lance Speelmon
> Scholarly Technologist
>
> On Jul 28, 2009, at 11:37 AM, Charles Hedrick wrote:
>
>> 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.
>>
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org
>> with a subject of "unsubscribe"
>