Deadlock detection and prevention in PostgreSQL - Mini Project: Build & Apply

Deadlock Detection and Prevention in PostgreSQL

📖 Scenario: You are managing a small online bookstore database. Sometimes, when multiple users try to update orders and inventory at the same time, the system experiences deadlocks. This causes delays and errors in processing orders.To improve the system, you will create a setup to detect and prevent deadlocks using PostgreSQL features.

🎯 Goal: Build a simple PostgreSQL setup that simulates transactions causing deadlocks, then implement a query to detect deadlocks, and finally apply a prevention technique using transaction ordering.

📋 What You'll Learn

Create two tables: orders and inventory with specified columns

Insert initial data into both tables

Write two concurrent transactions that can cause a deadlock

Write a query to detect deadlocks from PostgreSQL system views

Apply a transaction ordering strategy to prevent deadlocks

💡 Why This Matters

🌍 Real World

Deadlocks happen in real databases when multiple users or processes try to access the same data in conflicting ways. Detecting and preventing deadlocks keeps applications running smoothly without errors or delays.

💼 Career

Database administrators and backend developers must understand deadlocks to maintain data integrity and system performance in multi-user environments.

1

Create tables and insert initial data

Create a table called orders with columns order_id (integer primary key) and product_id (integer). Also create a table called inventory with columns product_id (integer primary key) and stock (integer). Insert one row into orders with order_id 1 and product_id 101. Insert one row into inventory with product_id 101 and stock 50.

PostgreSQL

-- Create tables orders and inventory
-- Insert initial data

Hint

Use CREATE TABLE statements for both tables with the specified columns and types. Then use INSERT INTO to add one row to each table.

2

Write transactions that can cause deadlock

Write two SQL transaction blocks named transaction1 and transaction2. In transaction1, lock the row in orders where order_id = 1 using SELECT FOR UPDATE, then lock the row in inventory where product_id = 101. In transaction2, lock the row in inventory where product_id = 101 first, then lock the row in orders where order_id = 1. Use BEGIN and COMMIT to define each transaction.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    product_id INTEGER
);

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    stock INTEGER
);

INSERT INTO orders (order_id, product_id) VALUES (1, 101);
INSERT INTO inventory (product_id, stock) VALUES (101, 50);

-- Write transaction1 and transaction2 blocks that lock rows in opposite order

Hint

Use BEGIN; and COMMIT; to define transactions. Use SELECT ... FOR UPDATE to lock rows. Make sure the locking order is reversed between the two transactions.

3

Detect deadlocks using PostgreSQL system views

Write a SQL query that selects all columns from pg_locks joined with pg_stat_activity on pid, filtering to show only rows where granted is false. This query will help detect waiting locks that may cause deadlocks.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    product_id INTEGER
);

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    stock INTEGER
);

INSERT INTO orders (order_id, product_id) VALUES (1, 101);
INSERT INTO inventory (product_id, stock) VALUES (101, 50);

-- Transaction 1
BEGIN;
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
SELECT * FROM inventory WHERE product_id = 101 FOR UPDATE;
COMMIT;

-- Transaction 2
BEGIN;
SELECT * FROM inventory WHERE product_id = 101 FOR UPDATE;
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
COMMIT;

-- Write query to detect waiting locks

Hint

Use pg_locks and pg_stat_activity joined on pid. Filter with WHERE NOT l.granted to find locks that are waiting.

4

Prevent deadlocks by consistent transaction ordering

Modify the transactions so both transaction1 and transaction2 lock rows in the same order: first lock the row in orders where order_id = 1, then lock the row in inventory where product_id = 101. Use BEGIN and COMMIT to define each transaction.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    product_id INTEGER
);

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    stock INTEGER
);

INSERT INTO orders (order_id, product_id) VALUES (1, 101);
INSERT INTO inventory (product_id, stock) VALUES (101, 50);

-- Modify transactions to lock rows in the same order to prevent deadlocks

Hint

Ensure both transactions lock orders first, then inventory. This consistent order prevents deadlocks.

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