Must COMMIT after SELECT (was: Very weird behavior in MySQLdb "execute")

[John Nagle]

Steve Holden wrote:
> 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).

    No.  If you're using InnoDB in default mode, you're in "repeatable read"
mode, which means the same SELECT on the same connection without an
intervening commit will produce the same result, regardless of any
other transactions.

    I have one FCGI program which does only SELECT calls, for processing quick
lookup-only requests. This program wasn't picking up changes made by
transactions from other programs, because it's a long-running program
with a persistent database connection, and it didn't do a COMMIT.
Once I made it do a COMMIT after each SELECT, it started picking up
the changes being made elsewhere.

    So you really do have to COMMIT after a SELECT, if you are reusing
the database connection.  CGI programs usually don't have this issue,
because their connections don't live long, but long-running FCGI (and maybe
Twisted) programs do.

				John Nagle