Skip to content

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: ): "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).

Beyond databases: in-memory metadata locks

The shared-vs-exclusive distinction transfers cleanly to in-memory metadata locks in any concurrent system. The canonical wiki instance at the storage-engine planner altitude is ClickHouse's MergeTreeData parts mutex (Source: sources/2026-05-14-cloudflare-clickhouse-query-plan-contention): the mutex protects a mutable std::vector of part descriptors, but it had been used as an exclusive (std::unique_lock) lock by every query planner — even though planners only read the parts list. Cloudflare (2026-05-14) diagnosed this as the load-bearing slowdown after a partition-key migration grew the list size, and switched to a shared (std::shared_lock) lock as the load-bearing fix:

"The query planner doesn't modify the parts list; it just reads it. It had no business using an exclusive lock. We modified the code to acquire a shared lock instead. This allowed all query planners to enter the critical section concurrently."

The compatibility matrix above applies verbatim — multiple planner-readers now coexist; insert / merge writers still take exclusive locks; readers and writers remain mutually exclusive. "A massive, immediate drop in query duration. The lock contention vanished."

This is the canonical wiki instance of patterns/shared-lock-for-read-only-metadata applied to a database server's in-memory metadata mutex (rather than to InnoDB row locks). The same pedagogical primitive — use a shared lock when access is read-only — applies at any altitude where a readers-writer lock is available; see also concepts/lock-contention-in-query-planning for the broader failure-class framing.

Seen in

  • sources/2026-05-14-cloudflare-clickhouse-query-plan-contention — canonical wiki instance at the OLAP-database planner altitude. ClickHouse's MergeTreeData parts mutex was used as an exclusive lock by every query planner reading the parts list. At hundreds of concurrent queries × tens of thousands of parts, planners serialised on the lock — "more than half of leaf query duration is spent waiting for a mutex that protects the table's list of active parts." Cloudflare's Optimization 1 swapped std::unique_lock for std::shared_lock; lock contention vanished immediately. Ships upstream with a deferred-copy snapshot follow-up as ClickHouse PR #85535. Demonstrates the shared/exclusive primitive applying unchanged from InnoDB row locks to in-memory metadata mutexes in a database server's runtime.
  • — 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.
Last updated · 542 distilled / 1,571 read