Concurrency Control & Locks

In a real-world enterprise database (like Amazon or Netflix), millions of transactions are executing simultaneously. If the DBMS executed these transactions serially (one after another), performance would be unacceptably slow. The DBMS must interleave the operations of multiple transactions—this is called Concurrency.

However, unmanaged concurrency leads to severe data anomalies. Concurrency Control is the mechanism the DBMS uses to ensure that concurrent transactions produce the same results as if they were executed sequentially.

1. Concurrency Problems

If concurrency is not controlled, three major problems can occur:

1. The Lost Update Problem (Write-Write Conflict)

Occurs when two transactions read the same data, then both update it. The second update permanently overwrites the first one.

• Example: T1 and T2 both read balance = 100. T1 adds $50 and writes 150. A millisecond later, T2 subtracts $20 from its original read and writes 80. T1's deposit is completely lost.

2. The Dirty Read Problem (Read-Write Conflict)

Occurs when a transaction reads data that has been updated by another uncommitted transaction. If the other transaction rolls back, the first transaction has read data that "never officially existed".

3. The Unrepeatable Read Problem

Occurs when a transaction reads the same row twice during its execution, and gets different data each time because another transaction updated the row in between the reads.

2. Lock-Based Protocols

To solve these problems, the DBMS uses Locks. A transaction must acquire a lock on a data item before it can access it.

Types of Locks

1. Shared Lock (S-Lock): If a transaction holds a shared lock on item $Q$, it can read $Q$, but cannot write to it. Multiple transactions can hold shared locks on the exact same item simultaneously.
2. Exclusive Lock (X-Lock): If a transaction holds an exclusive lock on item $Q$, it can both read and write to $Q$. Only ONE transaction can hold an exclusive lock on an item at any given time. No other locks (shared or exclusive) can be granted to other transactions until it is released.

3. The Deadlock Problem

The use of locks introduces a new problem: Deadlock.

Suppose transaction T1 holds an Exclusive Lock on row A and requests a lock on row B. Simultaneously, transaction T2 holds an Exclusive Lock on row B and requests a lock on row A.

Neither transaction can proceed. They will wait for each other infinitely. To solve this, the DBMS runs a Deadlock Detection algorithm in the background. If it detects a cycle, it automatically aborts and rolls back one of the transactions (the "victim"), releasing its locks and allowing the other to proceed.

4. Isolation Levels

SQL allows developers to choose exactly how strict the concurrency control should be for a specific transaction, trading off consistency for performance.

1. Read Uncommitted: No locks are acquired for reading. Dirty reads are possible. (Highest performance, lowest consistency).
2. Read Committed: (Default in Postgres/SQL Server). A transaction can only read committed data. Prevents dirty reads.
3. Repeatable Read: (Default in MySQL). The DBMS locks all rows read by the transaction until it finishes. Prevents dirty reads and unrepeatable reads.
4. Serializable: The strictest level. The DBMS executes transactions in a way that perfectly mimics sequential execution. (Lowest performance, highest consistency).