Locking

Shared and exclusive row locks, gap and next-key locks, and how to take them deliberately with FOR UPDATE and FOR SHARE.

SyntaxSELECT ... FOR {UPDATE | SHARE} [OF tbl_name] [NOWAIT | SKIP LOCKED]

Consistent reads need no locks — InnoDB serves them from a snapshot. Locks exist for writes, and for reads that are about to become writes.

Shared and exclusive

A shared (S) lock lets its holder read a row; several transactions may hold S on the same row. An exclusive (X) lock lets its holder update or delete it, and excludes every other S and X lock on that row. Before taking a row lock, a transaction takes a table-level intention lock: IS for SELECT ... FOR SHARE, IX for SELECT ... FOR UPDATE.

Row, gap and next-key

  • A record lock locks one index record.
  • A gap lock locks the space between index records. It blocks inserts into that gap and nothing else.
  • A next-key lock is a record lock plus a gap lock on the gap before that record. This is the default in REPEATABLE READ, and it is what prevents phantoms.

Gap locking is disabled under READ COMMITTED except for foreign-key and duplicate-key checks. A unique index searched by an equality on the full key locks only the record, not the gap.

Taking locks yourself

SELECT ... FOR SHARE and the older SELECT ... LOCK IN SHARE MODE are the same thing; the new spelling is the one that accepts OF, NOWAIT and SKIP LOCKED. Locks are held until the transaction commits or rolls back.

Example

Example · sql
-- Read-then-write on one row, done safely.
START TRANSACTION;

-- FOR UPDATE takes an X lock on the matched row and reads the latest
-- committed version, not the transaction snapshot. No other session can
-- read-for-update, update or delete it until this transaction ends.
SELECT on_hand, reserved
INTO   @on_hand, @reserved
FROM   inventory
WHERE  sku = 'KB-88'          -- sku is the primary key: record lock only,
FOR UPDATE;                   -- no gap lock, because the match is unique

-- Decide in the application, then write while still holding the lock.
UPDATE inventory
SET    reserved = reserved + 1
WHERE  sku = 'KB-88';

COMMIT;   -- the X lock is released here, and only here

When to use it

  • Reserving stock or a seat with a read-then-write that no concurrent session can interleave with.
  • Letting several queue workers claim different jobs from one table without blocking each other.
  • Finding which session is blocking a stuck UPDATE before deciding whether to kill it.
  • Understanding why an UPDATE on an unindexed column froze every writer on the table.

More examples

FOR SHARE and the older spelling it replaced

LOCK IN SHARE MODE is kept for backward compatibility and is what you will find in older code and in most blog posts; FOR SHARE is the current spelling and the only one that takes OF, NOWAIT and SKIP LOCKED. Use a shared lock when you need a parent row to still exist while you insert a child, but do not intend to modify the parent.

Example · sql
START TRANSACTION;

-- These two lines are the same statement, written two ways.
SELECT id FROM customers WHERE id = 4821 FOR SHARE;
SELECT id FROM customers WHERE id = 4821 LOCK IN SHARE MODE;

-- Only the FOR SHARE form accepts these:
SELECT id FROM customers WHERE id = 4821 FOR SHARE NOWAIT;
-- ERROR 3572 (HY000): Statement aborted because lock(s) could not be
-- acquired immediately and NOWAIT is set.

INSERT INTO orders (customer_id, status) VALUES (4821, 'pending');
COMMIT;

A job queue with SKIP LOCKED

The standard multi-worker queue pattern, available since MySQL 8.0.1. SKIP LOCKED removes locked rows from the result set rather than waiting, so N workers do N times the work. NOWAIT is its sibling for the opposite case: fail instantly rather than block, useful behind a user-facing request that must not hang.

Example · sql
START TRANSACTION;

-- Each worker grabs rows nobody else has locked, instead of queueing
-- behind them. Without SKIP LOCKED every worker serialises on row 1.
SELECT id, payload
FROM   job_queue
WHERE  status = 'ready'
ORDER  BY id
LIMIT  10
FOR UPDATE SKIP LOCKED;

UPDATE job_queue
SET    status = 'running', claimed_at = NOW()
WHERE  id IN (/* the ids just returned */);

COMMIT;   -- claims are now visible; another worker skips these rows

Seeing which locks are actually held

The two tables to open when a write has been hanging for a minute. data_locks shows LOCK_MODE values such as X,GAP and S,REC_NOT_GAP, which is the quickest way to confirm whether a statement is taking gap locks. These replaced the old INFORMATION_SCHEMA.INNODB_LOCKS and INNODB_LOCK_WAITS tables, which were removed in MySQL 8.0.

Example · sql
-- Who is waiting on whom, right now:
SELECT r.trx_id           AS waiting_trx,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query        AS waiting_query,
       b.trx_id           AS blocking_trx,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query        AS blocking_query
FROM   performance_schema.data_lock_waits w
JOIN   information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN   information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

-- And the individual locks, including their type (RECORD / TABLE),
-- mode (S, X, GAP, REC_NOT_GAP) and the index they sit on:
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM   performance_schema.data_locks;

Related

sql isolationsql deadlockssql begin commitsql create indexsql sargable

Discussion

  • Be the first to comment on this lesson.