Lock Based Concurrency Control Protocol

“Lock-Based Concurrency Control” and “Multiversion Concurrency Control” are two different approaches to implementing transaction isolation levels. This article will introduce the former one.

In two-phase locking, a transaction is divided into the growing phase for acquiring locks and the shrinking phase for releasing locks. Different rules and restrictions can be set in different phases to achieve different isolation levels. For example:

1. Do you need a lock to read? What type of lock is needed to read?
2. When to unlock?

When writing data, transactions at all isolation levels always require an X lock, which is unlocked at the end of the transaction. This prevents dirty writes, which are not allowed in databases, from occurring between two write transactions. Therefore, X locks may be used at all isolation levels, but S locks are not always required.

---

READ UNCOMMITTED

1. Transactions do not need an S lock when reading data, only an X lock when writing data. This allows the current transaction’s read operations not to be blocked by other transactions’ X locks;
2. The X lock is released at the end of the transaction, marking the transition to the shrinking phase.

Since read operations hold NO LOCKS AT ALL, X locks will not block them, data modified by other transactions can be read at any time, leading to the dirty read problem.

---

READ COMMITTED

1. The current transaction needs an S lock when reading data to block subsequent modifications by other transactions. Similarly, if another transaction has already acquired an X lock and modified the data, the current transaction’s acquisition of the S lock will be blocked until the X lock is released.
2. The X lock is released at the end of the transaction, marking the transition to the shrinking phase. Releasing the S lock does not change the transaction’s phase, so it can be released at any time (?)

READ COMMITTED resolves the dirty read problem: the S lock is blocked by the X lock + the X lock is released only at the end of the transaction = only after committed/rolled-back data can be read. However, it does not resolve the non-repeatable read problem: after the current transaction reads the data, it can release the S lock, allowing other transactions to modify and commit the data. When the current transaction reads again, different data is obtained, leading to the non-repeatable read problem.

---

REPEATABLE READ

1. Same as READ COMMITTED.
2. The X and S locks are BOTH released at the end of the transaction, marking the transition to the shrinking phase.

Since the S lock is also retained until the end of the transaction, other transactions cannot modify the corresponding data, ensuring that the same data is obtained in multiple reads within the current transaction.

	S Lock	X Lock
Read Uncommitted	N/A	Released at end
Read Committed	Released after read	Released at end
Repeatable Read	Released at end	Released at end