Deadlock detection and prevention in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is a deadlock in database systems?

Imagine two people trying to use two shared tools, but each holds one tool and waits for the other to release the second tool. What does this situation represent in databases?

A A deadlock where two transactions wait indefinitely for each other to release locks.

B A successful transaction commit without conflicts.

C A database backup process running in the background.

D A query optimization step that speeds up execution.

💡 Hint

Think about what happens when two processes wait forever for resources held by each other.

A deadlock occurs when two or more transactions hold locks that the others need, causing them to wait forever unless the system intervenes.

❓ query_result

intermediate

Which query shows current deadlocks in PostgreSQL?

PostgreSQL provides system views to monitor locks and deadlocks. Which query correctly lists current deadlocks?

A SELECT * FROM pg_locks WHERE NOT granted;

B SELECT * FROM pg_locks WHERE NOT granted AND pid IN (SELECT pid FROM pg_stat_activity WHERE wait_event_type = 'Lock');

C SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

D SELECT * FROM pg_stat_activity WHERE state = 'idle';

💡 Hint

Look for locks that are waiting and relate them to processes waiting on locks.

Option B combines waiting locks with processes waiting on locks, helping identify deadlocks.

📝 Syntax

advanced

Which transaction block prevents deadlocks by locking tables in a consistent order?

Two transactions lock tables in different orders causing deadlocks. Which code block correctly locks tables in the same order to prevent deadlocks?

A BEGIN; LOCK TABLE orders IN EXCLUSIVE MODE; LOCK TABLE customers IN SHARE MODE; COMMIT;

B BEGIN; LOCK TABLE customers IN EXCLUSIVE MODE; LOCK TABLE orders IN EXCLUSIVE MODE; COMMIT;

C BEGIN; LOCK TABLE orders IN EXCLUSIVE MODE; LOCK TABLE customers IN EXCLUSIVE MODE; COMMIT;

D BEGIN; LOCK TABLE customers IN SHARE MODE; LOCK TABLE orders IN EXCLUSIVE MODE; COMMIT;

💡 Hint

Consistent locking order means all transactions lock tables in the same sequence and mode.

Option C locks 'orders' then 'customers' both in EXCLUSIVE MODE, ensuring consistent order and preventing deadlocks.

❓ optimization

advanced

How can you reduce deadlock chances in high-concurrency environments?

In a busy database, deadlocks happen often. Which approach best reduces deadlock frequency?

A Keep transactions short and access tables in a consistent order.

B Increase transaction isolation level to SERIALIZABLE for all queries.

C Use long-running transactions to hold locks longer.

D Disable all locking mechanisms to avoid waiting.

💡 Hint

Think about how transaction length and lock order affect waiting.

Short transactions reduce lock time, and consistent lock order prevents circular waits, both reducing deadlocks.

🔧 Debug

expert

What error does this PostgreSQL transaction raise?

Two transactions run concurrently:
Transaction 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Transaction 2:
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

What error will occur if both run at the same time?

A No error; both transactions commit successfully

B ERROR: syntax error at or near "UPDATE"

C ERROR: division by zero

D ERROR: deadlock detected; transaction aborted

💡 Hint

Think about what happens when two transactions lock rows in opposite order.

Both transactions try to lock rows in opposite order causing a deadlock, so PostgreSQL aborts one with a deadlock error.