BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
От | PG Bug reporting form |
---|---|
Тема | BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? |
Дата | |
Msg-id | 18498-b95f3ee4c156de45@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
(Dmitry Koval <d.koval@postgrespro.ru>)
Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? (David Rowley <dgrowleyml@gmail.com>) RE: [EXTERNAL] BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature? ("Wetmore, Matthew (CTR)" <Matthew.Wetmore@evernorth.com>) |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 18498 Logged by: Dmitry Koval Email address: d.koval@postgrespro.ru PostgreSQL version: 17beta1 Operating system: Ubuntu 22.04 Description: Hi! I see strange behavior of the LOCK TABLE command when executed from a trigger. If LOCK TABLE (for the same table) is executed from a trigger in two different sessions, then there is a possibility of a “deadlock detected” error. Is this correct? (I'm guessing the correct behavior is this one: one session getting a table lock, and another session waits for the first session's transaction to complete.) Example. -------- 1) I wrote a tap-test to reproduce the problem (see attached file v1-0001-LOCK-TABLE-with-deadlocks.patch in next email). After applying the patch you need to run the test using the command PROVE_TESTS=t/006_parallel_inserts.pl make check -C src/test/modules/test_misc Test explanations. ------------------ The test creates BEFORE INSERT trigger. Trigger function contains rows: RAISE LOG 'Before lock (pid %)', pg_backend_pid(); LOCK TABLE test; RAISE LOG 'After lock (pid %)', pg_backend_pid(); The test starts 4 threads that insert records, causing the trigger. As a result of running the test, we see several "deadlock detected" errors with debug messages in the server log, like these: [219562] 006_parallel_inserts.pl LOG: Before lock (pid 219562) [219562] 006_parallel_inserts.pl CONTEXT: PL/pgSQL function test_func() line 5 at RAISE [219562] 006_parallel_inserts.pl STATEMENT: INSERT INTO test VALUES (10, 'worker_2'); [219563] 006_parallel_inserts.pl LOG: Before lock (pid 219563) [219563] 006_parallel_inserts.pl CONTEXT: PL/pgSQL function test_func() line 5 at RAISE [219563] 006_parallel_inserts.pl STATEMENT: INSERT INTO test VALUES (11, 'worker_1'); [219563] 006_parallel_inserts.pl ERROR: deadlock detected [219563] 006_parallel_inserts.pl DETAIL: Process 219563 waits for AccessExclusiveLock on relation 16384 of database 5; blocked by process 219562. Process 219562 waits for AccessExclusiveLock on relation 16384 of database 5; blocked by process 219563. Process 219563: INSERT INTO test VALUES (11, 'worker_1'); Process 219562: INSERT INTO test VALUES (10, 'worker_2'); [219563] 006_parallel_inserts.pl HINT: See server log for query details. [219563] 006_parallel_inserts.pl CONTEXT: SQL statement "LOCK TABLE test" PL/pgSQL function test_func() line 6 at SQL statement As we can seen, two sessions 219562 and 2195623 received an AccessExclusiveLock lock on the the same table, after which a deadlock was immediately diagnosed. ---- 2) I have attached a similar tap-test using the function, but without using the trigger (file v1-0001-LOCK-TABLE-wo-errors.patch in next email). When running this test, no "deadlock detected" errors occur. It's strange because the tests are similar. ---- The main question: is the "deadlock detected" error (1) correct? (I.e. is it necessary to look for the reason for this behavior?) -- With best regards, Dmitry Koval Postgres Professional: http://postgrespro.com
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Andres FreundДата:
Сообщение: Re: error "can only drop stats once" brings down database
Следующее
От: Dmitry KovalДата:
Сообщение: Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?