From: | Dilip Kumar <dilipbalaut(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Deadlock due to locking order violation while inserting into a leaf relation |
Date: | 2024-09-08 10:20:27 |
Message-ID: | CAFiTN-vPvHHsjfRMHCeJYrzTWsh0KLa-pbU_senAvRY=R4KxKA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Basically, when we are inserting into a leaf relation (or lower level
relation of the partitioned relation), we acquire the lock on the leaf
relation during parsing time itself whereas parent lock is acquire
during generate_partition_qual(). Now concurrently if we try to drop
the partition root then it will acquire the lock in reverse order,
i.e. parent first and then child so this will create a deadlock.
Below example reproduce this case.
Setup:
--------
CREATE TABLE test(a int, b int) partition by range(a);
CREATE TABLE test1 partition of test for values from (1) to (100000);
Test:
------
--Session1:
INSERT INTO test1 VALUES (1, 4);
-- let session is lock the relation test1 and make it wait before it
locks test (put breakpoint in ExecInitModifyTable)
--Session2:
-- try to drop the top table which will try to take AccessExclusive
lock on all partitions
DROP TABLE test;
--session3
-- see PG_LOCKS
-- we can see that session1 has locked locked root table test(16384)
waiting on test1(16387) as session1 is holding that lock
locktype | database | relation | pid | mode | granted
---------------+----------+---------------+-------+---------------------+------------
relation | 5 | 16387 | 30368 | RowExclusiveLock | t
relation | 5 | 16387 | 30410 | AccessExclusiveLock | f
relation | 5 | 16384 | 30410 | AccessExclusiveLock | t
(11 rows)
--Session1, now as soon as you continue in gdb in session 1 it will
hit the deadlock
ERROR: 40P01: deadlock detected
DETAIL: Process 30368 waits for AccessShareLock on relation 16384 of
database 5; blocked by process 30410.
Process 30410 waits for AccessExclusiveLock on relation 16387 of
database 5; blocked by process 30368.
HINT: See server log for query details.
LOCATION: DeadLockReport, deadlock.c:1135
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2024-09-08 10:33:45 | Re: Sort functions with specialized comparators |
Previous Message | Stepan Neretin | 2024-09-08 08:50:55 | Re: Sort functions with specialized comparators |