[Building Sakai] autocommit

[Speelmon, Lance Day]

Maybe we should have rollback executed upon return to the pool then?   
Things should not get committed unless explicitly told to do so...   
Thoughts?  L


Lance Speelmon
Scholarly Technologist

On Jul 28, 2009, at 9:50 PM, Stephen Marquard wrote:

> If an application is not handling its transactions correctly and  
> leaves a transaction open when the request thread ends, when another  
> app or service comes along and the db connection gets recycled, the  
> open transaction will implicitly get committed when the new  
> transaction starts.
>
> However, this is a little random because the behaviour depends on  
> how the connections in the db pool get recycled, so it's not easily  
> predictable.
>
> Basically application code always needs to ensure that its  
> transactions are handled correctly.
>
> Cheers
> Stephen
>
>>>> Charles Hedrick <hedrick at rutgers.edu> 7/28/2009 11:26 PM >>>
> 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"
>>
>
> _______________________________________________
> 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"
>