Re: Debugging deadlocks

On Wed, Mar 30, 2005 at 10:59:52PM +0200, frank(at)joerdens(dot)de wrote:
> On Sun, Mar 27, 2005 at 01:37:44AM -0700, Michael Fuhr wrote:
> > The current implementation supports only
> > exclusive row-level locks (SELECT FOR UPDATE), but I think Alvaro
> > might be working on shared row-level locks for a future release.
>
> Hmm ... are you saying that SELECT FOR UPDATE exquires an exclusive lock
> on the row in question in the sense that it conflicts with other
> *readers* trying to access that row? The documentation would appear to
> say otherwise:

I'm saying that foreign key checks use SELECT FOR UPDATE to ensure
that the referenced key doesn't change while the transaction is
pending, and that SELECT FOR UPDATE conflicts with other SELECT FOR
UPDATE queries. Therefore, if concurrent transactions insert records
into a table that has a non-deferred foreign key constraint, and
if the foreign key values are the same, then one of the transactions
will block. Example:

CREATE TABLE foo (
fooid integer PRIMARY KEY
);

CREATE TABLE bar (
barid serial PRIMARY KEY,
fooid integer NOT NULL REFERENCES foo
);

INSERT INTO foo (fooid) VALUES (1);

If we now have two transactions that both insert records into bar
with the same value for fooid, then one of the transactions will
block:

T1: BEGIN;
T2: BEGIN;
T1: INSERT INTO bar (fooid) VALUES (1);
T2: INSERT INTO bar (fooid) VALUES (1); -- blocks

Transaction T2 blocks because both transactions have done something
like "SELECT 1 FROM foo WHERE fooid = 1 FOR UPDATE" to ensure that
the referenced key can't change until the transaction completes.
So even though both transactions are only reading the referenced
table (foo), one has blocked the other.

Note that SELECT queries without FOR UPDATE won't block -- it's
other FOR UPDATE queries that block, even though they're only
reading.

I think Alvaro is working on a new locking mechanism that will allow
transactions to prevent a record from being modified without blocking
other transactions doing the same.

Alvaro (or somebody else), please correct me if I'm mistaken, but
that's what I've understood from discussions elsewhere.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/