foreign keys and deadlock
От | Jakub Ouhrabka |
---|---|
Тема | foreign keys and deadlock |
Дата | |
Msg-id | Pine.LNX.4.33.0111041947470.22916-100000@u-pl0 обсуждение исходный текст |
Список | pgsql-general |
hi, i've got several tables, say table A and others. there are foreign keys in table A referencing columns in the other tables. i've got 2 processes running simultaneously: process P and Q. P inserts rows into table A. Q updates inserted rows in table A. i'm seeing errors deadlock detected. i discovered from the logs that last thing process Q did before deadlock is one of the "select oid from only for update" checking the foreign keys constraints. what was exactly the last query executed by process P i don't know because in the log there is only select insert_into_A() and not the queries inside the function (why? i have turned query_logging on and i can see the subqueries from other functions...). i think that this may be a foreign key issue because when i removed all foreign key constraints everything works fine (i know this is not a proof but when using foreign keys deadlock is reported sooner or later each test run with certain settings but i have never saw it with foreign keys removed and any settings yet...). is it possible that the deadlock is caused by the foreign keys checking in these two simultaneous transactions? any other ideas? thank you, kuba more details: (hope didn't oversimplify it and eliminated the real cause...) table A has columns id, state, and fkey1,fkey2,... processes P and Q are the only 2 processes accessing this database. process P pseudo-code: (written in delphi, using odbc) while (1) { begin work; getNext(id, fkey1, ...); - not database function... select insert_into_ A(id, 0, fkey1,...); commit work; } pl/pgsql insert_into_A(...) inserts row into table A process Q pseudo-code: (written in C using ecpg) while (1) { open cursor for select * from A where state=0 on conn1; while (not empty) { fetch into id...; begin work on conn2; select update_A_set_state_1(id) on conn2; commit work on conn2; } close cursor; } pl/pgsql update_A_set_state_1 updates one row in table A. i'm using pg 7.1.3 installed from debian package.
В списке pgsql-general по дате отправления: