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

* Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> [Thu, 12 Feb
2009 19:34:46 +0200]:
> It's certainly not ideal. It's an implementation artifact of the way
> MVCC and RI triggers work. The purpose is to protect from this
potential
> bug:

As I can see you are agree with problem existence and problem will not
be solved soon.
May be some workaround exists (without using `for share` locks)?
Additionally take a look at example below close to real world DB usage.
I just want to emphasize that mentioned implementation artifact born not
obvious
issues at applications side.
Pay attention on foreign keys order in tables.
============
Preparation:
-- Application CORE tables
CREATE TABLE account (aid integer PRIMARY KEY, temp integer);
CREATE TABLE plan (pid integer PRIMARY KEY, temp integer );
-- Plugin1 table
CREATE TABLE bill1 (bid integer PRIMARY KEY, aid integer REFERENCES
account, pid integer REFERENCES plan, temp integer );
-- Plugin2 table
CREATE TABLE bill2 (bid integer PRIMARY KEY, pid integer REFERENCES
plan, aid integer REFERENCES account, temp integer );
insert into account values(1,1);
insert into plan values(1,1);
insert into bill1 values(1,1,1,1);
insert into bill2 values(1,1,1,1);
Session1:
============
test=# begin; select * from plan where pid=1 for update;
BEGIN
pid | temp
-----+------
1 | 1
(1 row)
============

Session2:
============
test=# begin; select temp from bill1 where bid=1 for update;
BEGIN
temp
------
1
(1 row)
test=# update bill1 set temp=1 where bid=1;
UPDATE 1
test=# update bill1 set temp=1 where bid=1;
============
Transaction of session2 is locked

Session1:
============
test=# select * from account where aid=1 for update;
ERROR: deadlock detected
DETAIL: Process 2836 waits for ShareLock on transaction 3329597;
blocked by process 2838.
Process 2838 waits for ShareLock on transaction 3329596; blocked by
process 2836.
============
Now repeat the same using `bill2` table instead of `bill1` in session2 -
no deadlock.
Thank you for help.
Please let me know if workaround exists.