Database Query Contains Old Data

[Paul Boddie]

On 23 Mai, 17:18, John Nagle <na... at animats.com> wrote:
>
>      Exactly.  Although it seems counterintutive, it's not enough
> to do a COMMIT after UPDATE and INSERT operations.  You also have to
> do a COMMIT after a SELECT if you're going to reuse the database handle
> and do another SELECT.  Otherwise, you reread the same data forever.

You can also do a rollback, as I noted, since the aim is merely to
obtain a new transaction by discarding the current one. Upon
performing a new select using the DB-API such a new transaction will
then be obtained, since the rollback or commit will have ensured that
no transaction is currently open.

If the DB-API exposed the MySQL/PostgreSQL semantics with explicit
transactions (see [1] for pertinent material), then the technique
discussed above would either be superfluous (you might not be using
transactions at all) or more obvious (you would have issued an
explicit "start transaction" or "begin" command), but there are
obviously good arguments for exposing the standard semantics through
the API instead.

>      COMMIT, by the way, is per database handle, so if you have
> multiple database handles, each needs to handle its own COMMIT needs.

That's worth remembering, yes.

Paul

[1] http://www.postgresql.org/docs/8.1/static/sql-start-transaction.html