CONCEPT Cited by 1 source

Shared lock vs exclusive lock¶

Two of the foundational row-level lock kinds in InnoDB (and most row-locking RDBMS engines): shared locks (S) and exclusive locks (X). They govern the rules for concurrent transactions reading and writing the same row, and together with gap locks form the substrate of InnoDB's lock-based isolation enforcement.

Shared lock (`S`)¶

A shared lock lets the holding transaction read the locked row without blocking other transactions from also reading it. Multiple shared locks can coexist on the same row — any number of readers can share read access simultaneously. What shared locks prevent is writes: no transaction can acquire an exclusive lock (and therefore cannot modify the row) while any shared lock is held on it.

Canonical framing from PlanetScale's Brian Morrison II (Source: sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work): "A shared lock is one that can be created by transactions for ensuring the data they are reading doesn't change. When a shared lock is created on a row, that row is still readable by other transactions. Other transactions, however, cannot modify the data within that row. Any number of transactions can create a shared lock on a row and all locks will need to be released before the data can be modified."

Acquired via:

SELECT … FOR SHARE (modern MySQL 8 syntax; SELECT … LOCK IN SHARE MODE is the legacy spelling).
Implicit shared locks on every SELECT under the SERIALIZABLE isolation level.

Released when the transaction commits or rolls back.

Exclusive lock (`X`)¶

An exclusive lock lets the holding transaction read and write the locked row — and blocks every other transaction from both reading and writing it. Only one exclusive lock can exist on a row at a time, and it cannot coexist with any shared lock.

Canonical framing: "When a transaction creates an exclusive lock on a row, only that transaction can read or write the data. If another transaction attempts to read or write data to that row, it will be prevented from doing so. This is especially useful during transactions where you expect data to be updated to prevent some of the violations outlined above."

Acquired via:

SELECT … FOR UPDATE — explicit acquisition by a reader that intends to update.
Implicit acquisition by any UPDATE or DELETE on the row.
INSERT takes exclusive locks on the new rows.

Released when the transaction commits or rolls back.

Compatibility matrix¶

Holding / Requesting	`S` (shared)	`X` (exclusive)
none	grant	grant
`S` (shared)	grant	block
`X` (exclusive)	block	block

Multiple readers sharing S locks coexist freely.
A reader with S blocks any writer wanting X, and vice versa.
Only one X holder at a time; everyone else queues.

Why locks exist: the two-phase locking framing¶

InnoDB implements a variant of strict two-phase locking (S2PL) for write coordination: locks are acquired as needed during transaction execution and held until commit or rollback. This ensures serializable-in-effect behaviour for writes regardless of the actual isolation level.

For reads, the picture differs by isolation level:

READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ non-locking reads — no S locks; reads go through MVCC.
SERIALIZABLE — every SELECT implicitly takes an S lock, so reads + writes both coordinate via the lock manager.
Locking reads (FOR SHARE / FOR UPDATE) at any isolation level — explicitly acquire locks.

The hierarchy with gap locks¶

InnoDB has more lock modes than the two basic kinds. The full taxonomy, in order of granularity:

Row lock (S or X on a specific row).
Gap lock — locks the space between rows (see concepts/gap-locking); prevents inserts into the gap.
Next-key lock = row lock + gap lock before the row; the default REPEATABLE READ lock mode on locking reads with range predicates.
Insert-intention lock — a special gap lock used by INSERT to indicate intent without blocking concurrent inserts into non-overlapping gaps.
Table-level metadata lock — coordinates DDL (ALTER TABLE) with DML.

The two-type (S/X) framing from the PlanetScale post is the pedagogical simplification; InnoDB's SHOW ENGINE INNODB STATUS output shows lock_mode X locks rec but not gap and lock_mode S and lock_mode X locks gap before rec and others. See concepts/row-level-lock-contention for the full SHOW ENGINE INNODB STATUS diagnostic.

Relationship to isolation levels¶

Isolation level	Shared locks on plain `SELECT`?	Exclusive locks held till commit?
`READ UNCOMMITTED`	no	yes
`READ COMMITTED`	no	yes
`REPEATABLE READ`	no	yes
`SERIALIZABLE`	yes, implicit	yes

Only SERIALIZABLE adds implicit S locks on every SELECT; in all weaker levels, S locks are acquired only when the developer explicitly uses FOR SHARE (a locking read). See concepts/mysql-transaction-isolation-levels.

Deadlock risk¶

Lock-based isolation introduces the possibility of deadlock: two transactions each hold a lock the other wants, so neither can proceed. InnoDB detects deadlocks and rolls back one of the transactions (the "victim") to break the cycle. The application sees the victim's failure as a deadlock error that can be retried.

Deadlock risk increases with:

Higher isolation levels (SERIALIZABLE's implicit S locks produce vastly more lock interactions).
Longer transactions (more locks held simultaneously).
Transactions acquiring locks in inconsistent orders.
Explicit FOR UPDATE on wide row sets without index support.

See concepts/deadlock-vs-lock-contention for the distinction between deadlock (mutual blocking) and simple row-level lock contention (serial blocking).

Seen in¶

sources/2026-04-21-planetscale-mysql-isolation-levels-and-how-they-work — Brian Morrison II's PlanetScale pedagogical post canonicalises shared vs exclusive locks as the two foundational lock types backing InnoDB's isolation-level enforcement. Shared locks = many readers coexist, block writers; exclusive locks = single holder, blocks everyone. Acquired explicitly via SELECT … FOR SHARE / FOR UPDATE, implicitly by every SELECT under SERIALIZABLE, and by every UPDATE/DELETE/INSERT at any level. Released at commit or rollback.