On 3 Jun, 00:17, James A. Donald <[EMAIL PROTECTED]> 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 -- http://mail.python.org/mailman/listinfo/python-list
