[Building Sakai] autocommit

[Charles Hedrick]

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"
>>
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2421 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090729/ad4d8ff5/attachment.bin