Demonstrate Deadlock Conditions with Conflicting Transactions

Simulate a Deadlock Scenario

Write a PostgreSQL query to simulate a potential deadlock by updating rows in two tables in conflicting orders.

Solution:

-- Transaction 1:
BEGIN;
UPDATE TableA -- Update a record in TableA.
SET value = value + 1
WHERE id = 1;
-- (Pause before executing next update)

-- Transaction 2 (in a separate session):
BEGIN;
UPDATE TableB -- Update a record in TableB.
SET value = value + 1
WHERE id = 2;
-- (Pause before executing next update)

-- Transaction 1 (continued):
UPDATE TableB -- Attempt to update TableB.
SET value = value + 1
WHERE id = 2;
-- Transaction 1 will now wait for Transaction 2.

-- Transaction 2 (continued):
UPDATE TableA -- Attempt to update TableA.
SET value = value + 1
WHERE id = 1;
-- This creates a deadlock situation.

Explanation:

• Purpose of the Query:
• The goal is to illustrate how two transactions updating tables in different orders can lead to a deadlock.
• This demonstrates the importance of consistent locking order to prevent deadlocks.
• Key Components:
• Two transactions updating TableA and TableB in conflicting sequences.
• The use of BEGIN; and multiple UPDATE statements in each transaction.
• Real-World Application:
• Critical in systems with complex transactional dependencies where deadlock prevention is essential.

Notes:

• In practice, PostgreSQL will detect the deadlock and abort one transaction.
• Ensure that application logic handles deadlock errors by retrying the transaction.

For more Practice: Solve these Related Problems:

• Write two PostgreSQL queries that update TableA and TableB in reverse order in two separate transactions to simulate a deadlock.
• Write a PostgreSQL query that first locks a row in TableX and then attempts to update a related row in TableY, while another transaction does the reverse.
• Write a PostgreSQL query to update the balance in Accounts table while a concurrent transaction updates the corresponding transaction log in reverse order to simulate deadlock.
• Write a PostgreSQL query to simulate deadlock by having two transactions lock rows in the Orders and Shipments tables in conflicting orders.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Use SKIP LOCKED to Process Available Rows.
Next PostgreSQL Exercise: Enforce Consistent Lock Ordering.