Understanding DB2, MySQL, and Oracle Concurrency Control: Isolation Levels and Locks Explained

1. Transaction Fundamentals

In relational databases, a transaction is the smallest unit of work that must be executed completely or not at all. Transactions guarantee the ACID properties:

Atomicity : all operations succeed or the whole transaction is rolled back.

Consistency : data remains in a valid state after the transaction.

Isolation : concurrent transactions do not interfere with each other.

Durability : once committed, changes persist permanently.

Transactions are automatically initialized when the first executable SQL statement runs and must be terminated with COMMIT or ROLLBACK. If a system failure occurs before commit, the DBMS rolls back uncommitted changes to restore consistency.

2. Isolation Levels and Their Anomalies

Isolation levels define how strictly a transaction is isolated from others. Lower isolation improves concurrency but can cause anomalies:

Dirty read : a transaction reads uncommitted changes from another transaction.

Non‑repeatable read : the same query returns different results within a single transaction.

Phantom read : new rows appear in a subsequent query with the same condition.

Lost update : two concurrent updates overwrite each other, causing data loss.

The SQL standard defines four isolation levels, each preventing a subset of these problems:

READ UNCOMMITTED : allows dirty reads, non‑repeatable reads, and phantom reads.

READ COMMITTED : prevents dirty reads but allows non‑repeatable and phantom reads.

REPEATABLE READ : prevents dirty and non‑repeatable reads; phantom reads may still occur.

SERIALIZABLE : prevents all three anomalies by forcing a total order of transactions, at the cost of higher lock contention.

3. Isolation Levels in DB2

DB2 supports several isolation levels, each with specific lock behavior:

CS (Cursor Stability) : locks the current row while it is being processed; the lock is released when the cursor moves to the next row. Suitable for high concurrency when only committed data is needed.

RR (Repeatable Read) : holds locks on all rows read until the transaction commits, guaranteeing repeatable reads but reducing concurrency.

RS (Read Stability) : locks rows that satisfy the query predicate; other rows remain unlocked. Inserts are allowed, which can cause phantom reads.

UR (Uncommitted Read) : no locks are taken; dirty reads are possible. Typically used for read‑only tables where consistency is not critical.

4. Isolation Levels in MySQL

MySQL (InnoDB engine) implements the standard four isolation levels. The default is REPEATABLE READ , but InnoDB uses next‑key locking to prevent phantom reads, effectively achieving SERIALIZABLE isolation for most workloads.

Locking behavior: READ COMMITTED and REPEATABLE READ perform non‑locking (consistent) reads; rows are not locked during SELECT. SELECT ... LOCK IN SHARE MODE acquires a shared (S) lock. SELECT ... FOR UPDATE acquires exclusive (X) locks on the selected rows. SERIALIZABLE forces shared locks on all rows read.

5. Isolation Levels in Oracle

Oracle supports only READ COMMITTED and SERIALIZABLE . Both use consistent non‑locking reads similar to MySQL’s implementation, so Oracle does not provide a separate READ UNCOMMITTED level.

6. Basic Lock Types

S‑LOCK (Shared lock) : allows multiple transactions to read the same data concurrently.

X‑LOCK (Exclusive lock) : used for INSERT/UPDATE/DELETE; only one transaction can hold it.

U‑LOCK (Update lock) : acquired when a cursor SELECT includes an UPDATE clause; it blocks other updates until the transaction ends.

All three DBMSs support S‑LOCK and X‑LOCK; DB2 additionally supports U‑LOCK.

7. Locking Behavior by Isolation Level

When a SELECT runs under different isolation levels:

In UNCOMMITTED READ , no locks are taken.

In READ COMMITTED and REPEATABLE READ , InnoDB performs consistent reads without locking rows; only explicit locking clauses (FOR UPDATE, LOCK IN SHARE MODE) acquire locks.

In SERIALIZABLE , shared locks are taken on all rows read, preventing concurrent modifications.

8. Consistent Non‑Locking Reads (Snapshot Reads)

Both MySQL’s InnoDB and Oracle use snapshot data to provide isolation without holding row locks. When a row is locked by another transaction (X‑LOCK), the reading transaction accesses the previous version stored in the undo segment. This mechanism eliminates waiting for lock release and greatly improves concurrency.

Differences:

READ COMMITTED always reads the latest committed snapshot, so non‑repeatable reads can occur.

REPEATABLE READ reads the snapshot taken at transaction start, guaranteeing repeatable reads but still allowing phantom reads unless next‑key locking is used.

9. Summary

Concurrency control balances data consistency with performance. Higher isolation levels (e.g., SERIALIZABLE) ensure stronger consistency but reduce concurrency, while lower levels increase throughput at the risk of anomalies. Choosing the appropriate isolation level and understanding each DBMS’s lock implementation are essential for designing reliable, high‑performance database applications.