Skip to content
Tutorials Center
My learning

Topics

Lesson 41 of 79 ยท Concurrency Control

Two-Phase Locking

Learn the two-phase locking protocol, the growing and shrinking phases, why it guarantees serializability, strict and rigorous 2PL and lock conversion, with live demos.

Taking locks is not enough to get correct results. A transaction that locks an item, uses it and unlocks it immediately gives other transactions a chance to slip in between its steps, exactly the interleavings that cause anomalies. What matters is the rule for when locks may be released. The classic rule is two-phase locking (2PL): a transaction must obtain all the locks it needs before it releases any. That one restriction is enough to guarantee that every schedule is conflict serializable.

You already know the lock modes and the precedence graph. In this lesson you will see why the two-phase rule works, the stricter variants that real systems use, and run a scheduler that shows the difference.

The two phases

Growing Acquire locks. Never release.
Shrinking Release locks. Never acquire.

The moment of the last acquisition is the lock point.

1 / 2

The rule and the lock point

A transaction obeys two-phase locking if all of its lock requests come before its first unlock. Its life then has two phases:

  • the growing phase, where it only acquires locks, and
  • the shrinking phase, where it only releases locks.

The boundary between them is the lock point: the moment the transaction holds its largest set of locks, in other words the instant of its last lock acquisition. A transaction that locks A, unlocks A, then locks B is not two-phase, because it acquires a lock after releasing one.

Lock conversion fits in the same framework. Upgrading a shared lock to an exclusive one (S to X) counts as acquiring, so it must happen in the growing phase. Downgrading (X to S) counts as releasing, so it is allowed only in the shrinking phase. Upgrades are a common source of deadlock, since two readers that both try to upgrade each wait for the other's shared lock to go away; see Deadlocks.

Why 2PL guarantees serializability

Here is the argument in a few lines. Suppose the precedence graph of a 2PL schedule has an edge Ti โ†’ Tj. That means Ti performed an operation on some item and Tj later performed a conflicting operation on it. For Tj to get its lock, Ti must already have released its lock on that item, and by the two-phase rule, a transaction releases only after its lock point. So Ti's lock point comes before Tj's lock point. Every edge therefore goes from an earlier lock point to a later one. A cycle would need a transaction whose lock point is earlier than itself, which is impossible. The graph is acyclic, and ordering the transactions by lock point gives an equivalent serial schedule.

Two caveats are important. First, 2PL is sufficient for serializability, not necessary: some serializable schedules cannot be produced by 2PL, so it sometimes makes transactions wait unnecessarily. Second, 2PL does not prevent deadlocks, and basic 2PL does not give recoverable schedules.

Watching it work

The scheduler below runs two transactions. T1 transfers 10 from account A to account B (ADD A -10, ADD B +10). T2 is an audit that reads A and then B and adds them. The true total is always 300. A script of turns decides who goes next, a transaction whose lock is unavailable simply loses its turn, and the program records the resulting schedule. Three locking policies are compared: releasing each lock immediately after use (early, not two-phase), basic 2PL (release after the lock point), and strict 2PL (release at commit or abort).

The second workload makes T1 abort after its updates, to expose the difference between basic and strict 2PL.

Python Early release versus 2PL versus strict 2PL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With early release, T1 unlocks A right after changing it. T2 reads the new A (90) and the not-yet-updated B (200) and reports 290, a total that never existed. The schedule has the cycle T1 โ†’ T2 โ†’ T1, so the serializability test fails. With 2PL and strict 2PL, T1 keeps its lock on A until it has locked B, so T2 loses its turns and reads 90 and 210 afterwards: 300, serializable.

Workload 2 shows why basic 2PL is not enough in practice. T1 passes its lock point, releases its locks and only then aborts. Before the abort, T2 read A = 90, a value that never became real: a dirty read. In the printed schedule T2 even commits before T1 aborts, so that schedule is not recoverable; if T2 had still been running, it would have had to abort too (a cascading abort). Strict 2PL holds the exclusive locks until the transaction ends, so T2 waits, and then sees the restored value 100.

Strict and rigorous 2PL

The two stricter variants fix the weaknesses of the basic protocol by delaying releases until the transaction is over:

  • Strict 2PL: a transaction keeps all its exclusive locks until it commits or aborts. Shared locks may be released earlier, in the shrinking phase. This produces strict schedules (see recoverability), so no one ever reads or overwrites uncommitted data, aborts never cascade, and rollback is a simple restore of before-images.
  • Rigorous 2PL (strong strict): a transaction keeps all its locks, shared and exclusive, until commit or abort. The serialization order then equals the commit order, which is convenient for distributed commit protocols.

Most database engines that use locking implement something very close to rigorous 2PL at their strongest isolation level, with the lock manager releasing everything when the transaction ends. Weaker isolation levels cheat on purpose: READ COMMITTED releases shared locks as soon as the row has been read, which is why non-repeatable reads appear (see Isolation Levels and Concurrency Anomalies).

The price is concurrency. Holding locks to the end makes long transactions block others for a long time, so a good habit is to keep transactions short and touch hot rows as late as possible. A variant called conservative 2PL (static 2PL) acquires all locks before starting, which avoids deadlock entirely, but it requires knowing the access set in advance and is rarely practical.

Checking a lock history

Given a transaction's sequence of lock events you can decide which flavour of 2PL, if any, it follows. The checker below reads events such as S(A) (shared lock on A), X(B), U(A) (unlock A) and C (commit).

Python Classify a lock history

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first history locks B after unlocking A, so it is not two-phase. The second is two-phase but gives up the exclusive lock on B before committing, which is only basic 2PL. The third releases only a shared lock early, so it is strict. The fourth keeps everything until the commit, so it is rigorous, and the last shows a lock conversion: X(A) after S(A) is an upgrade inside the growing phase.

2PL in real systems

You never write lock calls in ordinary SQL; the engine takes them as statements run. SELECT ... FOR UPDATE and LOCK TABLE exist in several server engines to ask for stronger locks explicitly, but they are not available in the browser, so they are shown here as code only:

SQL
-- PostgreSQL / MySQL (read-only here): lock the row you intend to change
BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;  -- exclusive row lock until COMMIT
UPDATE account SET balance = balance - 10 WHERE id = 1;
COMMIT;

Locking is only one route to serializability. Engines such as PostgreSQL mostly rely on versions rather than read locks (MVCC), and timestamp ordering avoids locks entirely.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which transaction follows two-phase locking?

    In a two-phase transaction every lock request comes before the first unlock. Only the second sequence acquires everything first and then releases.
  2. 2. What is the lock point of a transaction?

    The lock point is when the transaction holds all its locks. Ordering transactions by lock point gives an equivalent serial schedule.
  3. 3. What problem of basic 2PL does strict 2PL solve?

    Strict 2PL holds exclusive locks until the transaction ends, so others never see uncommitted data. It does not solve deadlock, which remains possible.
  4. 4. Which statement about 2PL is true?

    Two-phase locking proves serializability through the lock-point order, but transactions waiting for each other's locks can deadlock.

Summary

  • Two-phase locking: acquire all locks (growing), then release (shrinking); the last acquisition is the lock point.
  • Ordering transactions by lock point gives an equivalent serial schedule, so 2PL guarantees conflict serializability.
  • Basic 2PL can produce dirty reads and cascading aborts; strict 2PL holds exclusive locks until commit, and rigorous 2PL holds all locks.
  • Lock upgrades belong to the growing phase and downgrades to the shrinking phase.
  • 2PL allows deadlocks and can make transactions wait more than necessary.

Next: Deadlocks: Detection, Prevention and Recovery.