Very weird behavior in MySQLdb "execute"

[Steve Holden]

John Nagle wrote:
> Carsten Haese wrote:
>> On Mon, 2008-02-04 at 11:30 -0800, John Nagle wrote:
>>> Restarting the MySQL instance changes the database.  The entry "google.com"
>>> disappears, and is replaced by "www.google.com".  This must indicate a hanging
>>> transaction that wasn't committed.
>>>
>>> But that transaction didn't come from the Python IDLE session I've been
>>> making test calls from.  Those queries should match the graphical client
>>> exactly.
>>>
>>> So why don't they agree?
>> I don't have a definitive answer, but I do have a piece of generic
>> advice. If two database sessions receive differing results, the cause
>> could be any of the following factors:
>>
>> 1) One session is in a transaction, but the other is not.
>>
>> 2) Both sessions are in a transaction, but they are at different
>> isolation levels.
>>
>> 3) Both sessions are in a transaction, but the transactions were started
>> at different times.
> 
>      I see what's wrong, I think.  I haven't been doing a commit after
> a SELECT.  I've been careful to commit after write-type actions,
> but not after read-only actions.  I'm using InnoDB in default mode,
> which is REPEATABLE READ, and I've recently switched to long-running
> processes which keep the database connection open for hours.  So the
> data view for a given connection never changes, regardless of what's
> happening in other threads.
> 


I believe you are correct in your belief that a commit() is never 
required after SELECT. Once a database change is committed it should 
become visible to all other connections (under normal circumstances).

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/