[Building Sakai] autocommit

[Speelmon, Lance Day]

Normally a connection-pool would run rollback by default (i.e. DBCP or  
C3P0), but this was turned off long ago to reduce the amount of  
database overhead.  The tradeoff is of course having db connections in  
a known state.  L


Lance Speelmon
Scholarly Technologist

On Jul 29, 2009, at 9:14 AM, Charles Hedrick wrote:

> Wouldn't that add significantly to the number of operations with the  
> database server? If not, I'd be in favor, but I think that might be  
> a cure that's worse than the disease.
>
> On Jul 29, 2009, at 9:09:59 AM, Speelmon, Lance Day wrote:
>
>> 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"
>>>
>>
>