Обсуждение: Reproducable deadlock situation (possibly with foreign keys)
I've a problem occurring daily for me, I get quite a few deadlocks every day, and the concurrency is not so high. Happens with postgresql 8.0 and 8.1. as well... Here's a self-contained testcase, which I think it might be the problem I have in our production database. While it might be some sort of theoretical problem, it happens, the referenced tables are never really updated, but are just lookup-tables. In the production systen it's a lot more complicated, there are at least 10 different lookup tables, and not all table contain references to all lookup-tables: create table lookup1 ( id int primary key, t text ); create table lookup2 ( id int primary key, t text ); insert into lookup1 values (1, 'test1'); insert into lookup1 values (2, 'test2'); insert into lookup2 values (3, 'test3'); insert into lookup2 values (4, 'test4'); create table master1 ( id int primary key, l1_id int references lookup1(id), l2_id int references lookup2(id), t text ); create table master2 ( id int primary key, l2_id int references lookup2(id), l1_id int references lookup1(id), t text ); insert into master1 values (1000, 1, 3); insert into master2 values (1001, 3, 1); T1: BEGIN; T2: BEGIN; -- these are the queries similar to those from the foreign key code T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; -- DEADLOCK OCCURS! T1: UPDATE master1 set t='foo' where id=1000; T2: UPDATE master2 set t='foo' where id=1001; IMO it should be possible to solve this IF the foreign key code reorders the "for update" queries in a well-defined order, maybe ordered by the oid of the pgclass entry. In my case, it always happens on INSERT activity (no updates on those tables, just inserts), but I hope the above problem might be the solution for the insert deadylock too. Does this sound reasonable? Regards,Mario Weilguni p.s. Is it possible to modify logging so that the "SELECT 1 FROM ONLY...." are logged? Maybe this could help me finding out which queries the foreign key code really issues.
Mario Weilguni wrote: Hi, > T1: BEGIN; > T2: BEGIN; > -- these are the queries similar to those from the foreign key code > T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; > T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; > T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; > T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; > -- DEADLOCK OCCURS! > T1: UPDATE master1 set t='foo' where id=1000; > T2: UPDATE master2 set t='foo' where id=1001; Actually, in 8.1 the FK code issues queries like T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR SHARE OF x; which takes only a share lock on the tuple, not an exclusive lock, which solves the blocking and deadlocking problem. If you have a test case where it fails on 8.1 I certainly want to see it. > p.s. Is it possible to modify logging so that the "SELECT 1 FROM ONLY...." are > logged? Maybe this could help me finding out which queries the foreign key > code really issues. Hmm, actually, those queries should be logged normally, because AFAIK they are issued just like any other query, via SPI. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Ok, this my fault, and you're right. I took the query from the error messages (a 8.0.3 DB) and applied it to a 8.1 DB on a testing system without thinking too much. Still I think reordering those queries might prevent a deadlock. Best regards Am Mittwoch, 16. November 2005 12:21 schrieb Alvaro Herrera: > Mario Weilguni wrote: > > Hi, > > > T1: BEGIN; > > T2: BEGIN; > > -- these are the queries similar to those from the foreign key code > > T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; > > T2: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; > > T1: SELECT 1 FROM ONLY lookup2 x WHERE "id" = 3 FOR UPDATE OF x; > > T2: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR UPDATE OF x; > > -- DEADLOCK OCCURS! > > T1: UPDATE master1 set t='foo' where id=1000; > > T2: UPDATE master2 set t='foo' where id=1001; > > Actually, in 8.1 the FK code issues queries like > > T1: SELECT 1 FROM ONLY lookup1 x WHERE "id" = 1 FOR SHARE OF x; > > which takes only a share lock on the tuple, not an exclusive lock, which > solves the blocking and deadlocking problem. If you have a test case > where it fails on 8.1 I certainly want to see it. > > > p.s. Is it possible to modify logging so that the "SELECT 1 FROM > > ONLY...." are logged? Maybe this could help me finding out which queries > > the foreign key code really issues. > > Hmm, actually, those queries should be logged normally, because AFAIK > they are issued just like any other query, via SPI.
Mario Weilguni wrote: > Ok, this my fault, and you're right. > > I took the query from the error messages (a 8.0.3 DB) and applied it to a 8.1 > DB on a testing system without thinking too much. > > Still I think reordering those queries might prevent a deadlock. Well, if we could reorder them, we could have solved the problem long ago. I'm not totally sure it can't be done, but we tackled the problem in a different way so it's moot now. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat)