Understanding SQL Transactions: ACID, Isolation Levels, and How to Handle Locks

Modern backend systems rely on transactions to guarantee correctness especially in financial operations, delivery platforms, and any workflow where multiple users modify shared data.
A solid understanding of ACID, isolation levels, and locking is essential for designing reliable systems and performing well in backend interviews.

This article explains how transactions work, how isolation is implemented, and how to handle locks in real SQL databases such as PostgreSQL.

1. What Is a Transaction?
A Transaction is a group of SQL operations that must be executed as a single atomic unit.
Either:

• all operations succeed → COMMIT, or
• all operations fail → ROLLBACK

Example: Paying for a pizza
When customer clicks Buy:

1. Deduct $20 from their balance
2. Transfer $20 to the restaurant
3. Create the order
4. Commit

If the server crashes after step 1, the database rolls back the deduction.
This prevents partial updates and ensures data correctness.

2. ACID Properties

A — Atomicity
All steps succeed or none do.

C — Consistency
The database always moves from one valid state to another.

I — Isolation
Each transaction behaves as if it’s the only one running.

D — Durability
Once committed, data survives crashes.

3. Isolation Problems
When multiple transactions run concurrently, anomalies can occur:

• Dirty Read — reading uncommitted data
• Non‑repeatable Read — reading the same row twice with different results
• Phantom Read — new rows appear between identical queries

Isolation levels define which anomalies are allowed.

PostgreSQL uses MVCC (Multi‑Version Concurrency Control), which allows:

• readers to avoid blocking writers
• writers to avoid blocking readers
• This improves performance and reduces unnecessary locking.

4. How to Deal with Isolation in SQL?

Isolation is handled by choosing the appropriate isolation level for the workload.
For high‑throughput systems, Read Committed is usually enough.
For operations requiring strict correctness, Repeatable Read or Serializable is used.
PostgreSQL relies on MVCC snapshots to avoid unnecessary locking, and optimistic concurrency (versioning) can be used to prevent lost updates without blocking other transactions.

5. Isolation Levels

• Read Uncommitted: all violations allowed.
• Read Committed: prevents dirty reads only.
• Repeatable Read: prevents dirty reads and Non‑repeatable Read.
• Serializable: prevents all violations.

6. How to Deal with Locks in SQL?

Locks are handled by using them intentionally and keeping them as short as possible.
When exclusive access is required for example, assigning a driver to an order — SELECT … FOR UPDATE is used to lock the row.
Long‑running transactions are avoided, optimistic locking is used to reduce contention, and deadlock errors are caught and retried.

Scenario
Two drivers attempt to claim the same delivery order simultaneously.

Step 1 — Lock the row

BEGIN;

SELECT *
FROM orders
WHERE id = 42
FOR UPDATE;

This places a row‑level exclusive lock.
Other transactions attempting to lock or update this row will wait.

Step 2 — Update safely

UPDATE orders
SET driver_id = 7, status = 'assigned'
WHERE id = 42;

Because the row is locked:

• no two drivers can claim the same order
• no race conditions occur
• no lost updates happen

Then the lock is released.

7. Preventing Lost Updates

Three common strategies:

1. Atomic Update

UPDATE orders
SET status = 'done'
WHERE id = 10 AND status = 'pending';

1. Optimistic Locking (Versioning)

UPDATE orders
SET status='done', version=version+1
WHERE id=10 AND version=5;

1. Explicit Locking

SELECT * FROM orders WHERE id=10 FOR UPDATE;

8. Deadlocks

A deadlock occurs when:

• Transaction A waits for B
• Transaction B waits for A

PostgreSQL resolves this automatically:

• one transaction is terminated
• an error is returned
• the application retries the operation Deadlocks are normal in high‑concurrency systems

Conclusion
Transactions are the foundation of reliable backend systems.
A strong understanding of:

• ACID
• isolation levels
• MVCC
• locking
• deadlocks
• optimistic concurrency

…is essential for building correct, scalable, and safe applications.

This knowledge is frequently tested in backend interviews, especially for roles involving distributed systems, financial operations, or high‑concurrency workloads.