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

[Paul Boddie]

On 6 Feb, 16:04, Frank Aune <Frank.A... at broadpark.no> wrote:
>
> Whenever I did a SELECT() on the first connection, the cursor would
> stop "seeing" new entries commited in the log table by the other connection.
> I always assumed you needed COMMIT() after adding new content to the
> database, not after every single query, but this perhaps indicate otherwise?

No, you just need to be aware that the database module has probably
started a transaction on your behalf and that, as John points out, for
certain database systems in certain configurations, any repetition of
the same query in the same transaction will produce the same results,
thus avoiding the "nonrepeatable read" problem.

Really, the rule is this: always (where the circumstances described
above apply) make sure that you terminate a transaction before
attempting to read committed, updated data. If you religiously perform
a COMMIT (or ROLLBACK) after every query, you'll just create lots of
transactions for no good reason.

Paul