Deadlock Checker doesn't see foreign-key locks

Поиск
Список
Период
Сортировка
От Andrew Biagioni
Тема Deadlock Checker doesn't see foreign-key locks
Дата
Msg-id 3E23593E.2060305@e-greek.net
обсуждение исходный текст
Ответы Re: Deadlock Checker doesn't see foreign-key locks  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-admin
It appears to me that the Deadlock Checker doesn't see (and thus
release) foreign-key-based locks (see below for details).  Am I missing
something?  Is there a configuration item I am unaware of?

As far as I can tell (yes, I tested thoroughly) if I create a normal
deadlock situation as follows, the deadlock detector will work properly:

Thread A:  BEGIN WORK;
            UPDATE [table A, row X]

Thread B:  BEGIN WORK;
            UPDATE [table B, row Y]

Thread A:  UPDATE [table B, row Y]

Thread B:  UPDATE [table A, row X]

This is recognized by the deadlock detector (after the specified delay),
one of the two is rolled back, and the other completes happily.

HOWEVER, if I have a foreign-key-related lock, as follows, it is not
recognized:

Thread A:  BEGIN WORK;
            UPDATE [table A, row W]
            /* This has a foreign key into table F, row P */

Thread B:  BEGIN WORK;
            UPDATE [table B, row Y]
            /* This has a foreign key into table G, row Q */

Thread A:  UPDATE [table B, row Z]
            /* This has a foreign key into table F, row P */

Thread B:  UPDATE [table A, row X]
            /* This has a foreign key into table G, row Q */

Note that none of the UPDATEs step on the same actual row of the same
table, but they step (and lock) the same rows in the same tables via
foreign keys.

In this case (specifically tested), there is no deadlock detection.

NOTE - I am currently using v7.2.1, although my search through the
release notes lead me to believe that the problem was not addressed
subsequently.





В списке pgsql-admin по дате отправления:

Предыдущее
От: Tim Ellis
Дата:
Сообщение: Re: Encrypted Passwords
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Deadlock Checker doesn't see foreign-key locks