Why Does MySQL Hold Locks Until Commit? A Deep Dive into InnoDB Locking

Origin

To demonstrate that MySQL uses locks, open two sessions, start two transactions, and update the same row. The second UPDATE blocks, showing the row is locked.

Do Single Updates Acquire Locks?

Even when an UPDATE is executed without an explicit BEGIN, MySQL still acquires a lock.

update t set c = c + 1 where id = 1;

Analogy with Multithreading

In a multithreaded program, ten threads each increment a shared variable i a thousand times. Without synchronization, the final value is less than the expected total. i++; Adding a synchronized block fixes the problem: synchronized { i++; } The same principle applies to databases: the row corresponds to the variable, the UPDATE corresponds to i++, and each transaction corresponds to a thread. To guarantee consistency, the row must be locked.

Why Locks Are Held Until Commit

Example scenario:

Initial row: {id:1, c:1} Transaction A executes SELECT ... FOR UPDATE and reads c=1.

Transaction A updates c to 3 but keeps the lock.

Transaction B updates the same row to c=4.

Transaction A reads the row again and sees c=4, contradicting its previous view.

This would violate the Isolation property of ACID, so MySQL must keep the lock until the transaction commits.

Even READ‑UNCOMMITTED Must Wait

A counter‑example shows that allowing a READ‑UNCOMMITTED transaction to release its lock early would let another transaction read uncommitted data, breaking isolation. Therefore MySQL does not release locks before COMMIT, even at the READ‑UNCOMMITTED level.

Row Locks and Intention Locks

InnoDB row locks have exclusive (X) and shared (S) modes, similar to Java’s ReadWriteLock, which permits multiple readers but only one writer.

It allows multiple threads to read a certain resource, but only one to write it, at a time.

When a DDL statement (e.g., adding a column) needs a table lock while rows are locked, MySQL uses an intention lock—a lightweight table‑level lock that signals the type of row lock a transaction will need.

Intention locks are table‑level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

References

InnoDB Locking – https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

ReadWriteLock – http://tutorials.jenkov.com/java-util-concurrent/readwritelock.html