Re: BUG #4648: needless deadlock on tables having foreign-key

* Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> [Thu, 12 Feb
2009 13:54:11 +0200]:
> Hmm, the first UPDATE should've blocked already. It should've fired a
RI
> trigger to lock the parent tuple in shared mode, but it looks like
> that's not happening for some reason.

To tell the truth I expected another behavior.
Consider example below. The difference with the first one is that there
are no attempts to modify foreign key at all, but result is the same.
================================================
Preparation:
a) Create tables and insert a few rows.
CREATE TABLE parent (pid integer PRIMARY KEY);
CREATE TABLE child (id integer PRIMARY KEY, pid integer REFERENCES
parent(pid), temp integer);
INSERT INTO parent VALUES(1);
INSERT INTO child VALUES(1,1,1);
b) open two sessions via psql to database.

Execute in Session1:
=======================
test=# BEGIN; SELECT * FROM parent WHERE pid = 1 FOR UPDATE;
BEGIN
pid
-----
1
(1 row)
=======================

Execute in Session2:
=======================
test=# BEGIN; UPDATE child set temp=1 where id = 1;
BEGIN
UPDATE 1
test=# UPDATE child set temp=1 where id = 1;
=======================

Transaction in Session2 is locked on attempt to execute the second
update statement.

Execute in Session1:
=======================
test=# UPDATE child set temp=1 where id = 1;
ERROR: deadlock detected
DETAIL: Process 28230 waits for ShareLock on transaction 14654800;
blocked by process 28232.
Process 28232 waits for ShareLock on transaction 14654799; blocked by
process 28230.
test=#
=======================
================================================

Are you sure parent tuple should be lock in shared mode if
a) foreign key is not modified explicitly at all
b) new value of foreign key is the same as old (not changed).

For example I tested MSSQL 2005 and MySQL 4.1 and they do not block
statement:
"UPDATE child set temp=1 where id = 1;"
of Session2.