Reproducable deadlock situation (possibly with foreign keys)
От | Mario Weilguni |
---|---|
Тема | Reproducable deadlock situation (possibly with foreign keys) |
Дата | |
Msg-id | 200511161056.18506.mweilguni@sime.com обсуждение исходный текст |
Ответы |
Re: Reproducable deadlock situation (possibly with foreign keys)
|
Список | pgsql-hackers |
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.
В списке pgsql-hackers по дате отправления: