Why concurrency control matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why is concurrency control important in databases?

Imagine multiple people trying to update the same bank account balance at the same time. What problem does concurrency control help to prevent?

A It automatically backs up the database every time a user makes a change.

B It prevents data from being lost or overwritten incorrectly when multiple users access the same data simultaneously.

C It encrypts the data to protect it from unauthorized access.

D It speeds up the database by allowing unlimited simultaneous writes without restrictions.

💡 Hint

Think about what happens if two people try to change the same number at once without coordination.

Concurrency control ensures that when multiple users try to change the same data at the same time, their changes do not conflict or cause incorrect results. This prevents data loss or corruption.

❓ query_result

intermediate

What is the output of this transaction sequence?

Two transactions run concurrently on a PostgreSQL database with a table accounts having a row with id=1 and balance=100.

Transaction 1: BEGIN; UPDATE accounts SET balance = balance + 50 WHERE id = 1; COMMIT;

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

If both transactions run at the same time without concurrency control, what could be the final balance?

PostgreSQL

SELECT balance FROM accounts WHERE id = 1;

A 120

B 150

C 100

D 80

💡 Hint

Consider what happens if one update overwrites the other without seeing it.

If concurrency control is missing, one update might overwrite the other, causing lost updates. The correct final balance after both updates should be 120.

📝 Syntax

advanced

Which SQL statement correctly locks a row to prevent concurrent updates?

In PostgreSQL, which statement locks a specific row in the accounts table to prevent other transactions from modifying it until the current transaction finishes?

A SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

B LOCK TABLE accounts IN EXCLUSIVE MODE;

C SELECT * FROM accounts WHERE id = 1 LOCK ROW;

D UPDATE accounts SET balance = balance WHERE id = 1 LOCK;

💡 Hint

Look for the standard SQL syntax that locks rows for update.

The FOR UPDATE clause locks the selected rows so other transactions cannot modify them until the lock is released.

❓ optimization

advanced

How can you reduce deadlocks in concurrent transactions?

Deadlocks happen when two transactions wait for each other’s locks. Which practice helps reduce deadlocks in a PostgreSQL database?

A Use random order to access tables to avoid predictability.

B Avoid using transactions altogether.

C Access tables and rows in the same order in all transactions.

D Increase the timeout for locks to wait longer.

💡 Hint

Think about how consistent ordering can prevent circular waits.

Accessing tables and rows in the same order prevents circular waiting, which is the main cause of deadlocks.

🔧 Debug

expert

Why does this transaction cause a serialization failure?

Two concurrent transactions run these commands:

-- Transaction 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

-- Transaction 2
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

Both use SERIALIZABLE isolation level. Why might one transaction fail with a serialization error?

A Because the transactions are missing explicit <code>LOCK TABLE</code> statements.

B Because the <code>SELECT</code> statement locks the row exclusively, blocking the other transaction indefinitely.

C Because <code>SERIALIZABLE</code> isolation allows dirty reads, causing inconsistent data.

D Because both transactions read the same initial balance and then update it, causing a conflict that PostgreSQL detects to maintain correctness.

💡 Hint

Think about how PostgreSQL ensures transactions appear as if they ran one after another.

Under SERIALIZABLE isolation, PostgreSQL detects when concurrent transactions could cause inconsistent results and aborts one with a serialization failure to keep data correct.