On Tue, Mar 15, 2005 at 01:24:51PM -0500, Ned Batchelder wrote: > The best solution is to rollback entire transactions when the database is > busy. This means structuring your code so that all transactions can > rollback and retry. In my experience, this is necessary (though *far* less > frequently) even with the "big boy" databases. See, for example, > http://staff.newtelligence.net/clemensv/PermaLink,guid,826bc7c9-8b0f-4df6-aabe-e6c5377a9446.aspx
Yet another reason why MVCC is such a beautiful thing. I've managed to accidentally get Oracle to abort a transaction due to detected deadlock only once or twice over the years, and I had to work pretty hard to do that - lots of complicated PL/SQL code which was taking row and/or table locks in differing table orders in different places. And that, of course, was an application bug. (You must take all locks in the same table by table order, everywhere. To damn bad that the RDBMS doesn't give you any real tools to help you verify that.) I don't remember ever seeing deadlock for any other reason in Oracle, and PostgreSQL (which has effectively the same MVCC model) should be the same. Databases using lock-based strategies rather than MVCC are, of course, more susceptible do deadlock problems under high concurrency. On the other hand, Oracle has more than once given me the joy of aborting my big huge special purpose transaction with a "not enough rollback space" error. Oops, gotta turn those annoyingly manual dba knobs some more. PostgreSQL is probably better in that respect, as its "rollback" space is effectively in the table itself, which will just keep getting bigger and bigger as necessary. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
