Deadlock detection and prevention in PostgreSQL - Step-by-Step Execution

Concept Flow - Deadlock detection and prevention

Transaction A requests Lock on Resource 1

↓

Transaction B requests Lock on Resource 2

↓

Transaction A requests Lock on Resource 2

↓

Transaction B requests Lock on Resource 1

↓

Deadlock detected by system

↓

System chooses a victim transaction to rollback

↓

Victim transaction rolled back, locks released

↓

Other transaction proceeds

↓

Deadlock resolved

Transactions request locks on resources; if each waits for the other, a deadlock occurs. The system detects this cycle and aborts one transaction to resolve it.

Execution Sample

PostgreSQL

-- Session A:
BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
-- A then tries: LOCK TABLE orders IN ACCESS EXCLUSIVE MODE; (will wait)

-- Session B:
BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
-- B then tries: LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE; (will wait)

-- Deadlock detected and resolved

Two transactions acquire locks on different tables (accounts and orders) first, then request each other's table in exclusive mode, causing a deadlock cycle that PostgreSQL detects and resolves by aborting one.

Execution Table

Step	Transaction	Action	Lock Requested	Lock Granted	Waits For	Deadlock Detected	Result
1	A	Begin transaction	None	None	None	No	Transaction A started
2	A	Lock accounts table	Access Exclusive	Granted	None	No	Lock on accounts acquired by A
3	B	Begin transaction	None	None	None	No	Transaction B started
4	B	Lock orders table	Access Exclusive	Granted	None	No	Lock on orders acquired by B
5	A	Request lock on orders table	Access Exclusive	Waiting	Held by B	No	A waits for B
6	B	Request lock on accounts table	Access Exclusive	Waiting	Held by A	Yes	B waits for A; deadlock detected
7	System	Choose victim	N/A	N/A	N/A	Yes	Transaction B aborted
8	B	Rollback	N/A	N/A	N/A	No	Locks released by B
9	A	Proceed	N/A	Granted	None	No	A acquires orders lock and continues
10	A	Commit	N/A	Released	None	No	Transaction A committed

💡 Deadlock detected at step 6; system aborts Transaction B to resolve.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	After Step 8	Final
Transaction A locks	None	accounts: Granted	accounts: Held	accounts: Held, orders: Waiting	accounts: Held, orders: Granted	Released
Transaction B locks	None	None	orders: Granted	orders: Held, accounts: Waiting	Aborted	None
Deadlock Status	No	No	No	Yes	No	No

Key Moments - 3 Insights

Why does Transaction A wait at step 5 even though it requested a lock?

Because Transaction B already holds an Access Exclusive lock on the orders table, so A must wait until B releases it (see execution_table step 5).

How does the system detect the deadlock at step 6?

The system notices that Transaction A waits for a lock held by B (orders), and Transaction B waits for a lock held by A (accounts), forming a cycle, which is a deadlock (execution_table step 6).

What happens to Transaction B after deadlock detection?

Transaction B is chosen as the victim and aborted to release its locks, allowing Transaction A to continue (execution_table steps 7 and 8).

Visual Quiz - 3 Questions

Look at the execution table, what lock does Transaction A hold after step 2?

A No lock

B Shared lock

C Access Exclusive lock on accounts table

D Access Exclusive lock on another table

💡 Hint

Check the 'Lock Granted' column at step 2 for Transaction A.

At which step is the deadlock detected according to the execution table?

A Step 4

B Step 6

C Step 8

D Step 10

💡 Hint

Look at the 'Deadlock Detected' column for the first 'Yes' value.

If Transaction A released its lock earlier, how would the execution table change?

A Transaction B would not wait and no deadlock would occur

B Deadlock would still occur at step 6

C Transaction A would be aborted instead

D Both transactions would wait indefinitely

💡 Hint

Refer to the 'Waits For' and 'Deadlock Detected' columns and consider lock availability.

Concept Snapshot

Deadlock occurs when two transactions wait for each other's locks.
PostgreSQL detects deadlocks automatically.
It aborts one transaction to break the cycle.
Use explicit locking carefully to avoid deadlocks.
Monitor and handle deadlock errors in applications.

Full Transcript

Deadlock detection and prevention in PostgreSQL involves transactions requesting locks on resources. When two transactions each hold locks the other needs, they wait indefinitely, causing a deadlock. PostgreSQL detects this cycle and resolves it by aborting one transaction, releasing its locks so the other can proceed. This process ensures the database does not freeze. The execution trace shows Transaction A locking accounts, B locking orders, then cross-requests causing waits, deadlock detection, and B rolled back. Understanding this helps avoid and handle deadlocks in real applications.

Practice

1. What is a deadlock in PostgreSQL?

easy

A. A performance optimization technique for faster queries.

B. A syntax error in SQL statements causing query failure.

C. A backup process that locks tables during data export.

D. A situation where two or more transactions wait indefinitely for each other to release locks.

Solution

1. Step 1: Understand transaction locking

   Transactions acquire locks on resources to maintain data integrity.

2. Step 2: Define deadlock

   A deadlock occurs when transactions wait on each other in a cycle, causing indefinite waiting.

3. Final Answer:

   A situation where two or more transactions wait indefinitely for each other to release locks. -> Option D

4. Quick Check:

   Deadlock = cyclic waiting [OK]

Hint: Deadlock means transactions wait forever on each other [OK]

Common Mistakes:

• Confusing deadlock with syntax errors
• Thinking deadlock improves performance
• Mixing deadlock with backup locking

2. Which of the following is the correct way to acquire locks to prevent deadlocks in PostgreSQL?

easy

A. Acquire locks on resources in random order.

B. Acquire locks on resources in the same order in all transactions.

C. Never acquire any locks in transactions.

D. Acquire locks only after committing the transaction.

Solution

1. Step 1: Understand lock acquisition order

   Acquiring locks in a consistent order prevents circular waiting.

2. Step 2: Identify correct practice

   All transactions should acquire locks on resources in the same order to avoid deadlocks.

3. Final Answer:

   Acquire locks on resources in the same order in all transactions. -> Option B

4. Quick Check:

   Consistent lock order = no deadlock [OK]

Hint: Always lock resources in the same order [OK]

Common Mistakes:

• Locking resources randomly
• Not locking resources at all
• Locking after commit

3. Consider two transactions in PostgreSQL:
-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
-- waits here

-- Transaction 2
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- waits here
What will PostgreSQL do when both transactions wait for each other?

medium

A. Both transactions will wait forever causing a deadlock.

B. Both transactions will succeed without any issue.

C. PostgreSQL will detect the deadlock and abort one transaction automatically.

D. PostgreSQL will merge both transactions into one.

Solution

1. Step 1: Identify deadlock scenario

   Both transactions hold locks and wait for the other's lock, creating a cycle.

2. Step 2: PostgreSQL deadlock detection

   PostgreSQL automatically detects deadlocks and aborts one transaction to break the cycle.

3. Final Answer:

   PostgreSQL will detect the deadlock and abort one transaction automatically. -> Option C

4. Quick Check:

   Deadlock detected = abort one transaction [OK]

Hint: PostgreSQL aborts one transaction on deadlock detection [OK]

Common Mistakes:

• Assuming infinite waiting without abort
• Thinking transactions merge automatically
• Believing both succeed without conflict

4. You have the following PostgreSQL code causing a deadlock:

BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
UPDATE customers SET name = 'Alice' WHERE id = 1;
-- Transaction 2 starts here
BEGIN;
LOCK TABLE customers IN ACCESS EXCLUSIVE MODE;
UPDATE orders SET status = 'shipped' WHERE id = 10;

What is the main issue causing the deadlock?

medium

A. Transactions lock tables in different orders causing circular wait.

B. Using SHARE MODE lock instead of EXCLUSIVE MODE.

C. Updating different tables in the same transaction.

D. Missing COMMIT statements after updates.

Solution

1. Step 1: Analyze lock order

   Transaction 1 locks orders first, then updates customers; Transaction 2 locks customers first, then updates orders.

2. Step 2: Identify circular wait

   Each transaction waits for the other's locked table, causing deadlock due to different lock order.

3. Final Answer:

   Transactions lock tables in different orders causing circular wait. -> Option A

4. Quick Check:

   Different lock order = deadlock risk [OK]

Hint: Lock tables in same order to avoid deadlock [OK]

Common Mistakes:

• Blaming lock mode instead of order
• Thinking updating different tables causes deadlock
• Ignoring missing COMMIT as cause

5. You want to prevent deadlocks in a multi-user PostgreSQL system updating inventory and sales tables. Which strategy is best?

hard

A. Keep transactions short and acquire locks on inventory then sales in all transactions.

B. Acquire locks on sales first, then inventory, but only in some transactions.

C. Avoid using transactions to prevent locking.

D. Use long transactions to batch updates and reduce lock frequency.

Solution

1. Step 1: Understand deadlock prevention

   Keeping transactions short reduces lock time; consistent lock order prevents cycles.

2. Step 2: Apply best practice

   Always lock inventory first, then sales, in all transactions to avoid deadlocks.

3. Final Answer:

   Keep transactions short and acquire locks on inventory then sales in all transactions. -> Option A

4. Quick Check:

   Short transactions + consistent lock order = deadlock prevention [OK]

Hint: Short transactions + consistent lock order prevent deadlocks [OK]

Common Mistakes:

• Locking in inconsistent order
• Avoiding transactions entirely
• Using long transactions increasing lock time