Fwd: [Bricolage-Devel] DB Deadlock Detected errors

Hi All,

Does this message make any sense? Is PostgreSQL in fact implicitly
locking tables for INSERTs, UPDATEs, and DELETEs? Or is Mark's analysis
totally off? This seems very strange tome, since there are so many
other high-volume applications running on PostgreSQL...

TIA,

David

Begin forwarded message:

> From: Mark Jaroski <mark(at)geekhive(dot)net>
> Date: Fri Jul 4, 2003 11:34:49 AM US/Pacific
> To: Bricolage Developers <bricolage-devel(at)lists(dot)sourceforge(dot)net>
> Cc: Sara Wood <woods(at)who(dot)ch>
> Subject: Re: [Bricolage-Devel] DB Deadlock Detected errors
>
> David Wheeler wrote:
>> Let us know what you find.
>
> Apparently PostgreSQL's Mutli Version Concurrency Control
> protects us agains Deadlocks on read transactions only. For
> UPDATES and INSERTS there are other levels of explicit
> locking that come into play.
>
> The problem we're having is apparently due to various
> Bricolage processes accessing the same tables in different
> orders. Here are the relavant paragraphs from the Postgres
> docs:
>
>
> 9.3.3. Deadlocks
>
> Use of explicit locking can cause deadlocks, wherein two (or
> more) transactions each hold locks that the other wants. For
> example, if transaction 1 acquires an exclusive lock on
> table A and then tries to acquire an exclusive lock on table
> B, while transaction 2 has already exclusive-locked table B
> and now wants an exclusive lock on table A, then neither one
> can proceed. PostgreSQL automatically detects deadlock
> situations and resolves them by aborting one of the
> transactions involved, allowing the other(s) to complete.
> (Exactly which transaction will be aborted is difficult to
> predict and should not be relied on.)
>
> The best defense against deadlocks is generally to avoid
> them by being certain that all applications using a database
> acquire locks on multiple objects in a consistent order. One
> should also ensure that the first lock acquired on an object
> in a transaction is the highest mode that will be needed for
> that object. If it is not feasible to verify this in
> advance, then deadlocks may be handled on-the-fly by
> retrying transactions that are aborted due to deadlock.
>
> So long as no deadlock situation is detected, a transaction
> seeking either a table-level or row-level lock will wait
> indefinitely for conflicting locks to be released. This
> means it is a bad idea for applications to hold transactions
> open for long periods of time (e.g., while waiting for user
> input).
>
>
>
> The explicit locking that it's talking about doesn't seem to
> be something that we can turn off. It just happens whenever
> we use INSERT or UPDATE.
>
> So as far as I can tell this problem should be solvable by
> making sure that all of the callbacks, SOAP, and the Dist
> processes do their INSERTS and UPDATES in exactly the same
> order.
>
> Suppose we have a situation like this, with four different
> processes that all acquire WRITE locks on the following
> three tables, but in different orders (this is supposition,
> I haven't investigated the actual orders):
>
>
> Callback1: begin, job, story, version, commit
>
> Callback2: begin, version, story, job, commit
>
> Dist: begin, story, job, version, commit
>
> SOAP: begin, version, job, story, commit
>
>
> all we need is for any two of these to happen
> simultainiously, and boom: Deadlock. Here's why: Say
> Callback1 and Dist happen at the same time. Callback1 gets
> the lock on job, and Dist gets the lock on story. But next
> Callback1 needs a lock on story, and Dist needs job. But
> both of these are locked and can't be released until the
> process which owns the lock commits, and that can't happen
> until the process gets the lock it wants. So we have a
> Deadlock. Postgres is smart enough to detect this and
> throws an error, aborting every query the transaction tries
> to execute from then on.
>
> I figure that the reason that this error is only showing up
> at WHO is that it is pretty rare. Actually it happens only
> about once in 1000 transactions, but when we get busy in the
> afternoon that can mean that we get deadlocks every couple
> of minutes or so.
>
>
> So, we either make sure that ever process acquires its locks
> in the *same exact order* or we figure out some way for a
> process to catch the error, abort any remaining queries, and
> start over.
>
>
> Mark sense?
>
>
> Oh, and uh, I guess we need it yesterday...

--
David Wheeler AIM: dwTheory
david(at)kineticode(dot)com ICQ: 15726394
http://kineticode.com/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]