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
--
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
