Skip site navigation (1) Skip section navigation (2)

How is this possible? (more on deadlocks)

From: Carlos Moreno <moreno(at)mochima(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How is this possible? (more on deadlocks)
Date: 2004-08-24 20:41:23
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Ok, now I'm really intrigued by what looks to me
(possibly from a naive point of view) like a bug,
or rather, a limitation on the implementation.

I can't find a reasonable justification why the
following would cause a deadlock:

I run two instances of psql using the same DB on
the same machine.  On one of them, I run:

create table A (id int primary key);
create table B (id int primary key);
create table AB
     A_id int references A(id),
     B_id int references B(id)

Then I add a few records (all this from the same
instance of psql):

insert into A values (1);
insert into A values (2);
insert into B values (10);
insert into B values (11);

Ok, now, I try two concurrent transactions, by
executing commands alternating from one psql
instance to the other one:

I'll prefix each line with 1: or 2: indicating
which console I execute it on -- the commands were
executing in the time sequence corresponding to the
lines below:

1:  begin;
2:  begin;

1:  insert into AB values (1,10);
2:  insert into AB values (2,10);

1: insert into AB values (2,11);

At this point, console 1 blocks for a second or
two, and then I get an error, reporting that a
deadlock was detected;  then, console 2 unblocks.

I can't see how it is justified that the above
causes a deadlock.

I do understand how the deadlock is happening:
trans. 1 puts a lock on rows 1 of A and row 10
of B -- meaning, "nobody touches these rows until
I'm finished";  then trans 2. locks row 2 of A,
but is put on hold waiting to lock row 10 of B,
since there is already a lock on it.  When trans.
A now tries to put a lock on row 2 of A, the
deadlock happens.

The thing is, why?  Why is this a deadlock?  When
we look at the low-level details, sure; but when
you look at the nature of what's happening at a
conceptual level, a deadlock is not justified,

Trans. 1 doesn't really need to put a mutex type
of lock around row 1 of A -- it simply needs to
atomically flag the order:  "nobody delete or
modify this row of table A"...  Another trans.
that attempts to place the same order should
not block -- it should succeed and return
immediately and continue with the transaction;
there is no conflict in the above example -- the
first transaction does not want to allow anyone
to mess with row 1 of A;  the other transaction
wants exactly the same, so it seems to me that
the lock is more restrictive than it needs to be.

I don't know about the internals of how transactions
and locks and FK constraints are handled, but I'm
analyzing it and describing what seems to be
happening internally, based on the behaviour I

Any comments?


In response to


pgsql-general by date

Next:From: Carlos MorenoDate: 2004-08-24 20:52:23
Subject: Is this legal SQL? Is it a good practice?
Previous:From: Thomas HallgrenDate: 2004-08-24 20:24:56
Subject: Re: Unsupported 3rd-party solutions (Was: Few questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group