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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
