Обсуждение: BUG #4648: needless deadlock on tables having foreign-key
The following bug has been logged online: Bug reference: 4648 Logged by: Konstantin Email address: kostya2702@rambler.ru PostgreSQL version: 8.1.16 Operating system: Red Hat Enterprise Linux 4 Description: needless deadlock on tables having foreign-key Details: 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) ); INSERT INTO parent VALUES(1); INSERT INTO child VALUES(1,1); b) open two sessions via psql to database. Bug: 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 pid=1 where id = 1; BEGIN UPDATE 1 test=# UPDATE child set pid=1 where id = 1; ======================= Transaction in Session2 is locked on attempt to execute second update statement (actually the same as the frist one). Why? Execute in Session1: ======================= test=# UPDATE child set pid=1 where id = 1; ERROR: deadlock detected DETAIL: Process 20126 waits for ShareLock on transaction 14654779; blocked by process 20128. Process 20128 waits for ShareLock on transaction 14654778; blocked by process 20126. test=# ======================= Used PostgreSQL: PostgreSQL 8.1.16 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) Please give me advise how to solve/workaround problem. Thank you.
Konstantin wrote: > 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) ); > INSERT INTO parent VALUES(1); > INSERT INTO child VALUES(1,1); > > b) open two sessions via psql to database. > > Bug: > > 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 pid=1 where id = 1; > BEGIN > UPDATE 1 > test=# UPDATE child set pid=1 where id = 1; > ======================= 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. > Used PostgreSQL: > PostgreSQL 8.1.16 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 > 20060404 (Red Hat 3.4.6-9) I can reproduce this on CVS HEAD too. I'll try to figure out what's going on.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
* 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.
Konstantin wrote: > * 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. > ... > 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). Yeah, you're right, we do have that optimization. However, it doesn't kick in when you update a tuple that you've already updated in the same transaction. The reason is explained by this comment in trigger.c: > * There is one exception when updating FK tables: if the > * updated row was inserted by our own transaction and the > * FK is deferred, we still need to fire the trigger. This > * is because our UPDATE will invalidate the INSERT so the > * end-of-transaction INSERT RI trigger will not do > * anything, so we have to do the check for the UPDATE > * anyway. So this is an implementation artifact, due to the way foreign keys are implemented in PostgreSQL. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> 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.
Read the special code in AfterTriggerSaveEvent. This behavior is
exactly what is expected --- since the referencing field didn't
change, only the second update attempt actually fires the trigger.
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [Thu, 12 Feb 2009 10:54:34 -0500]: > Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes: > > 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. > > Read the special code in AfterTriggerSaveEvent. This behavior is > exactly what is expected --- since the referencing field didn't > change, only the second update attempt actually fires the trigger. Why? The second update is identical to the first one. What is the difference? Such behavior looks very strange. For example I wrote before to Heikki Linnakangas that I expect parent tuple will not be lock if a) foreign key is not modified explicitly at all b) new value of foreign key is the same as old (not changed).
Konstantin wrote: > * Tom Lane <tgl@sss.pgh.pa.us> [Thu, 12 Feb 2009 10:54:34 -0500]: >> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes: >> > 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. >> >> Read the special code in AfterTriggerSaveEvent. This behavior is >> exactly what is expected --- since the referencing field didn't >> change, only the second update attempt actually fires the trigger. > > Why? The second update is identical to the first one. > What is the difference? Such behavior looks very strange. 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: BEGIN; SET CONSTRAINTS ALL DEFERRED; -- This queues a trigger to check that there's a row in parent -- with matching parent id. Its execution is deferred to end -- of transaction. INSERT INTO child (temp, parentid) VALUES (1, 1); -- Update.the just-inserted row. Since we didn't change the foreign -- key column, no RI trigger is queued. UPDATE child SET temp = 1 WHERE pid = 1; - -- This tries to run the trigger queued by the INSERT. But it's -- not run because the row version doesn't exist anymore, because -- it was later updated. If there wasn't a row in parent table with -- id 1, we wouldn't throw an error like we should. COMMIT We're avoiding this scenario by always queuing the RI trigger, even if the key was not changed, if the updated tuple was inserted in the same transaction. That also applies to row versions that were not inserted, but are a result of an earlier update in the same transaction (UPDATE is internally very much like INSERT+DELETE) Hmm, the comment specifically talks about deferrable RI checks. I wonder if we could skip that when there's no deferred triggers queued? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Hmm, the comment specifically talks about deferrable RI checks. I wonder
> if we could skip that when there's no deferred triggers queued?
The point of the comment is that if the FK isn't deferred then it will
have been executed already (at completion of the preceding INSERT).
I'd be a bit worried about relying on that, though, because of nested
query scenarios (eg, ON INSERT trigger fires an update on some
previously-inserted row) and the possibility of deferral status changing
intra-transaction.
regards, tom lane
* Heikki Linnakangas <heikki.linnakangas@enterprisedb.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.
Konstantin <kostya2702@rambler.ru> writes:
> I just want to emphasize that mentioned implementation artifact born not
> obvious issues at applications side.
[ shrug... ] The "implementation artifact" is that you didn't get a
deadlock *earlier*. You can't expect to update referenced rows and
referencing rows in the same transaction and not risk deadlock against
other transactions doing the same thing.
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [Thu, 12 Feb 2009 19:10:34 -0500]: > [ shrug... ] The "implementation artifact" is that you didn't get a > deadlock *earlier*. I agree that such behavior is more plain rather than current. > You can't expect to update referenced rows and > referencing rows in the same transaction and not risk deadlock against > other transactions doing the same thing. No. One transaction works with referenced rows only. Another with referencing rows only. I expect predictability, then flexibility (locks optimization). So what is your recommendation? To avoid deadlock do I need lock all referencing rows if I need to update referenced row(s)? Something like ` select * from bill1 where pid = 1 or aid = 1 for update; ` in the first session of example?