Deadlock detection and prevention in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to check for deadlocks using PostgreSQL system views.

PostgreSQL

SELECT * FROM pg_locks WHERE [1] = true;

Drag options to blanks, or click blank then click option'

Awaiting

Blocked

Cgranted

Dactive

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not exist in pg_locks.

Confusing 'waiting' with 'granted'.

The pg_locks view shows lock information. The granted column indicates if the lock is currently held. Checking for granted locks helps identify potential deadlocks.

2fill in blank

medium

Complete the query to find transactions waiting for locks in PostgreSQL.

PostgreSQL

SELECT pid, blocked_pid FROM pg_blocking_pids([1]);

Drag options to blanks, or click blank then click option'

Atransaction_id

Bsession_id

Clock_id

Dpid

Attempts:

3 left

💡 Hint

Common Mistakes

Passing transaction or session IDs instead of process IDs.

Using a column that does not exist in the context.

The function pg_blocking_pids(pid) returns the process IDs blocking the given process ID. So, you need to pass a pid to check who is blocking it.

3fill in blank

hard

Fix the error in the query to detect deadlocks by joining pg_locks and pg_stat_activity.

PostgreSQL

SELECT a.pid, a.query, l.locktype FROM pg_stat_activity a JOIN pg_locks l ON a.[1] = l.pid WHERE NOT l.[2];

Drag options to blanks, or click blank then click option'

Apid

Bprocess_id

Csession_pid

Dgranted

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect column names for join.

Filtering on the wrong lock status column.

The correct join is on pid columns from both views. The condition NOT l.granted filters locks that are waiting, which helps detect deadlocks.

4fill in blank

hard

Fill both blanks to write a query that detects deadlocks by checking waiting and granted locks.

PostgreSQL

SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_locks blocked_locks JOIN pg_locks blocking_locks ON blocked_locks.[1] = blocking_locks.[2] WHERE NOT blocked_locks.granted AND blocking_locks.granted;

Drag options to blanks, or click blank then click option'

Atransactionid

Bpid

Clocktype

Dmode

Attempts:

3 left

💡 Hint

Common Mistakes

Joining on process IDs instead of transaction IDs.

Using locktype or mode for join which is incorrect.

Deadlocks often occur when transactions wait on the same transactionid. Joining on transactionid helps find blocking and blocked transactions.

5fill in blank

hard

Fill all three blanks to write a query that prevents deadlocks by ordering lock acquisition.

PostgreSQL

BEGIN; LOCK TABLE [1] IN [2] MODE; LOCK TABLE [3] IN SHARE MODE; COMMIT;

Drag options to blanks, or click blank then click option'

Atable_a

BEXCLUSIVE

Ctable_b

DACCESS SHARE

Attempts:

3 left

💡 Hint

Common Mistakes

Locking tables in different orders causing deadlocks.

Using incorrect lock modes.

To prevent deadlocks, acquire locks in a consistent order. Here, table_a is locked first in EXCLUSIVE mode, then table_b in SHARE mode.

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