Very weird behavior in MySQLdb "execute"

[Gabriel Genellina]

On 5 feb, 01:42, Steve Holden <st... at holdenweb.com> 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).

As said before, that depends on the transaction isolation level in
use. You describe the "read committed" level, where non-repeatable
reads may occur. A higher level is "repeatable read" where the
original data rows are always included in the result set for the same
query, even if other concurrent transaction modified that same rows
(but new "phantom" rows may appear in later reads).
Unfortunately the Python DBAPI 2.0 does not define any way to set this
parameter.

--
Gabriel Genellina