deadlock detected with concurrent update/delete when gp_enable_global_deadlock_detector set to off
Article ID: 417296
Updated On:
Products
Issue/Introduction
Issue: Local Deadlock Detected Due to Lock Mode Upgrade
Concurrent DML (Data Manipulation Language) operations running on a Greenplum Heap Table may encounter a "deadlock detected" error, particularly when the Global Deadlock Detector (GDD) is disabled/default (gp_enable_global_deadlock_detector = off) .
This type of deadlock is a local deadlock happened on the coordinator, originating from conflicting lock mode requirements within the same segment.
Example Error Log Snippet:
2025-10-21 00:00:01.780553 CST,"<username>","<dbname>",p1408928,th1537012864,"xx.xx.xx.xx","60664",2025-10-20 23:59:55 CST,0,con13821124,cmd50,seg-1,,dx165631782,,sx1,"ERROR","40P01","deadlock detected","Process 1408928 waits for ExclusiveLock on relation <relation_oid> of database <database_oid>; blocked by process 1408957.
Environment
GPDB 6X
Such symptom is observed with the following SQL pattern, in short, doing a INSERT and then UPDATE/DELETE in a transaction block.
begin;
insert into t values (1);
update t set b = b + 1;
end;
Cause
Cause: Conflicting Lock Modes
The root cause is the Greenplum implementation of lock mode acquisition for UPDATE statements when GDD is disabled, leading to a lock-mode upgrade pattern that causes a cycle of mutual waiting.
1. DML Locking Patterns Based on GDD Status
The lock acquired by an UPDATE statement on the target relation (rel) differs based on the GDD state. An INSERT statement always acquires a non-conflicting RowExclusiveLock until the transaction ends.
| GDD State | Target Table Type | UPDATE/DELETE Statement Lock Mode |
GDD Disabled (off) |
Any | ExclusiveLock |
GDD Enabled (on) |
Heap Table | RowExclusiveLock |
GDD Enabled (on) |
AO Table (Append Optimized) | ExclusiveLock |
2. Local Deadlock Mechanism (GDD Disabled)
Consider a transaction block with two DMLs: INSERT followed by UPDATE. When GDD is disabled, the locking pattern is problematic: the transaction first acquires the less restrictive RowExclusiveLock for the INSERT, and then attempts to acquire the more restrictive ExclusiveLock for the UPDATE (a lock-mode upgrade)
Two concurrent transactions (A and B) running these SQLs will cause a local deadlock:
| Time | Transaction A Action | Transaction B Action | Result |
| t1 | Executes INSERT, holds RowExclusiveLock. |
Executes INSERT, holds RowExclusiveLock. |
Success: RowExclusiveLock does not conflict. |
| t2 | Tries UPDATE (Requires ExclusiveLock). |
(Continuing) | A Blocked: A is blocked by B's held RowExclusiveLock. A hangs. |
| t3 | (Continuing to hang) | Tries UPDATE (Requires ExclusiveLock). |
B Blocked: B is blocked by A's held RowExclusiveLock. B hangs. |
Result: A and B wait for each other, leading to a local deadlock detected by the Segment's local lock manager. If GDD were enabled, the UPDATE would also use RowExclusiveLock, preventing the conflicting upgrade attempt.
Resolution
Resolution
In this particular case:
Action: Set gp_enable_global_deadlock_detector to on.
Effect: This allows concurrent updates to run using the non-conflicting RowExclusiveLock on Heap Tables,preventing the lock-mode upgrade issue, and the lock during execution logic (ExecUpdate, ExecDelete) happens on segments. The GDD will then actively monitor for any complex global deadlocks that may arise.
More general suggestion:
To avoid deadlocks, make sure all transactions acquire locks on the same objects in the same order, and if multiple lock modes are involved for a single object, then transactions should always acquire the most restrictive mode first.
for example, doing UPDATE and then INSERT will not result in deadlock even when GDD set to off.
begin;
update t set b = b + 1;
insert into t values (1);
end;
Additional Information
GDD Design Rationale
3. GDD Design Rationale: Preventing Global Deadlocks
Unlike standard Postgres, where INSERT, UPDATE, and DELETE all acquire the non-conflicting RowExclusiveLock, Greenplum (GPDB) sometimes requires a higher-level ExclusiveLock for UPDATE/DELETE.
This design choice is a safety measure against Global Deadlocks when the Global Deadlock Detector (GDD) is disabled:
- MPP Architecture: GPDB is an MPP database where data is stored on Segments and DML execution happens on those Segments (where heap tuple locks are placed). The initial table lock occurs on the Coordinator during the parse-analyze-optimization phase.
- Global Deadlock Risk: Because GPDB has many Segments, two transactions (A and B) can deadlock across nodes:
- A locks a tuple on seg0.
- B locks a tuple on seg1.
- A attempts to lock B's tuple on seg1 (A waits for B).
- B attempts to lock A's tuple on seg0 (B waits for A).
- Forced Serialization: Since Global deadlocks cannot be automatically resolved without GDD, Greenplum forces UPDATE/DELETE on the same table to hold the higher-level ExclusiveLock when GDD is disabled, thereby making them run in serial to prevent the global deadlock scenario.
Feedback
