Database Query Contains Old Data

[Paul Boddie]

On 21 Mai, 15:22, giraffe... at gmail.com wrote:
>
> I did and I confirmed this by modifying the data, selecting it from
> the mysql command line client to verify the changes, then running the
> report again. If I exit the application and then start it again,
> everything works as expected until the second instance of the report
> is run.

Note that if you have a connection open in your program, especially if
that connection has already been used to select data, it may be the
case that you then have to perform a rollback or commit before
attempting to access newly added data. The reason for this behaviour
is that the DB-API modules will have begun a transaction on your
behalf, and while that transaction is open, changes committed in other
transactions may be unavailable to your own transaction, depending on
the transaction isolation level.

MySQL appears to use "repeatable read" by default [1] as its
transaction isolation level, whereas PostgreSQL (for example) uses
"read committed" by default [2]. I would guess that if you were using
PostgreSQL, this particular problem would not have occurred, but there
are other reasons to be aware of the effects of long duration
transactions in PostgreSQL, and the practice of periodically
performing a rollback would still be worth considering with that
database system.

Paul

[1] http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-isolation.html
[2] http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html