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
