Database Query Contains Old Data

[Paul Boddie]

On 3 Jun, 00:17, James A. Donald <jam... at echeque.com> wrote:
> On Wed, 21 May 2008 07:23:04 -0700 (PDT), Paul Boddie
>
> > 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.
>
> If one has transactions open for a long time, or transactions that
> involve a great deal of data, this will result in poor performance or
> poor scalability.

I think you need to explain this to me. If there's a long-running
transaction happening in the background and my own transactions get
created and rolled back periodically, how would the long-running
transaction be affected? If, on the other hand, my own transaction is
long-running, I can see that rolling it back would incur a cost, but
what choice do I have other than to perform a rollback more often (or
to disable transactions, which might incur other costs)? I don't want
to perform a commit instead merely for performance reasons, especially
if it impacts correctness.

I was actually thinking of lock acquisition in PostgreSQL when I made
the remark. With lots of tables in a database, it's possible to
acquire a large number of locks, and retaining locks can also prevent
other operations from being carried out.

>                    But one may have such large transactions without
> being aware of it.  Is there any way to make transaction size salient
> to the developer?  Any way to make sure one is committing as early and
> often as possible?

I'm not aware of anything which will tell you how big your transaction
is, but there may be some kind of table or function which provides
some details about such things. However, it is possible to see how
many locks your transaction has, and on which tables.

Paul