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: 412BA7F3.7010303@mochima.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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);
<AT THIS POINT, CONSOLE 2 BLOCKS>

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,
IMHO:

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
observe.

Any comments?

Carlos
--

In response to

Responses

Browse pgsql-general by date

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