Thread: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
From
PG Bug reporting form
Date:
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
Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
From
Dmitry Koval
Date:
Additional attachments for previous email. -- With best regards, Dmitry Koval Postgres Professional: http://postgrespro.com
Attachment
Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
From
David Rowley
Date:
On Fri, 7 Jun 2024 at 20:35, PG Bug reporting form <noreply@postgresql.org> wrote: > The main question: is the "deadlock detected" error (1) correct? > (I.e. is it necessary to look for the reason for this behavior?) Looks like a fairly standard lock upgrade problem to me. s1: create table t (); s1: begin; s2: begin; s1: lock table t in access share mode; s2: lock table t in access share mode; s1: lock table t in access exclusive mode; s2: lock table t in access exclusive mode; -- deadlock Isn't this more or less the same as what's going on in your scenario? David
Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
From
David Rowley
Date:
On Fri, 7 Jun 2024 at 20:58, David Rowley <dgrowleyml@gmail.com> wrote: > s1: create table t (); > s1: begin; > s2: begin; > s1: lock table t in access share mode; > s2: lock table t in access share mode; > s1: lock table t in access exclusive mode; > s2: lock table t in access exclusive mode; -- deadlock > > Isn't this more or less the same as what's going on in your scenario? I didn't explain why this needs to happen... s1 can't get the AEL on "t" because it's blocked by s2's share lock. Subsequently, s2 then must wait on s1's AEL request and that creates a deadlock cycle that needs to be broken. David
Re: BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
From
Dmitry Koval
Date:
David, thanks for the detailed explanation! I missed the fact that there were two different locks. Issue resolved. -- With best regards, Dmitry Koval Postgres Professional: http://postgrespro.com
RE: [EXTERNAL] BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or the feature?
From
"Wetmore, Matthew (CTR)"
Date:
Functions are marked Parallel UNSAFE by default.
I'm not sure if you can make the trigger unsafe, but if the trigger is called from the function, make sure you didn't
addPARALLEL SAFE.
-----Original Message-----
From: PG Bug reporting form <noreply@postgresql.org>
Sent: Friday, June 7, 2024 12:55 AM
To: pgsql-bugs@lists.postgresql.org
Cc: d.koval@postgrespro.ru
Subject: [EXTERNAL] BUG #18498: Locking a table from a trigger (with using two sessions) causes a deadlock: the bug or
thefeature?
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
waitsfor 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
functiontest_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/pgSQLfunction 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:
Process219563 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
forquery 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
adeadlock 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.patchin 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:
https://urldefense.com/v3/__http://postgrespro.com__;!!GFE8dS6aclb0h1nkhPf9!6wjoXl3ClfzeTnLlOt9xRDNi77L9TyDNX5UxN67fv3IodNX_NpA_FNIR2MScTXKzq1cVNqCP1G4b23inNxUG7mlZ5cIQes8BpQ$
----------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the
addressshown. This email transmission may contain confidential information. This information is intended only for the
useof the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your
filesif you are not the intended recipient. Thank you for your compliance. Copyright (c) 2024 Evernorth