MVCC lets readers and writers stay out of each other's way, but two writers to the same row must take turns, and some readers need to block writers too. That is the job of locks. Most of the time they are invisible; then one day a checkout page hangs for fifty seconds, or the logs fill with Deadlock found when trying to get lock. This lesson covers InnoDB's lock types, locking reads, what a lock wait and a deadlock look like, how InnoDB breaks a deadlock, and the habits that keep both rare — plus the optimistic alternative to locking at all.

Shared and exclusive row locks

InnoDB locks rows (precisely: index records), not whole tables, for ordinary DML.

• A shared (S) lock lets the holder read the row; other transactions may also take S locks but not X.
• An exclusive (X) lock lets the holder change the row; nobody else can take S or X on it.

UPDATE and DELETE take X locks on every row they change, held until COMMIT or ROLLBACK. Plain SELECT takes no locks under the default isolation levels — it reads a snapshot (the next lesson). Table-level intention locks (IS, IX) are taken automatically to say "this transaction holds row locks in here", so that a LOCK TABLES or DDL knows to wait.

Locking reads

When you read a value in order to write based on it, a snapshot read is not enough — another transaction could change the row after you read it. Ask for a lock while reading:

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- X lock on row 1
-- application checks balance >= 500
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

• FOR UPDATE takes X locks: others cannot change or lock the rows until you finish.
• FOR SHARE (MySQL 8; LOCK IN SHARE MODE before) takes S locks: others can read-lock but not change them — useful for "this parent row must still exist when I insert the child".
• NOWAIT fails at once instead of waiting if a row is locked.
• SKIP LOCKED skips locked rows — the standard way to build a job queue where several workers each grab a different pending job:

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Gap locks and next-key locks

Locking existing rows cannot stop a new row being inserted into the range you queried — the phantom problem. Under REPEATABLE READ, InnoDB's locking reads and updates therefore lock gaps too:

• a record lock locks one index record;
• a gap lock locks the open interval between two index records, so nothing can be inserted there;
• a next-key lock is a record lock plus the gap before it — the default for range scans under REPEATABLE READ;
• an insert intention lock is the gap-level lock an INSERT takes; it waits behind another transaction's gap lock.

-- REPEATABLE READ; index on age; existing ages 20, 30, 40
SELECT * FROM members WHERE age BETWEEN 25 AND 35 FOR UPDATE;
-- locks record 30 and the gaps (20, 30) and (30, 40):
-- another session's INSERT of age 27 or 33 now waits

An equality lookup on a unique index for a row that exists locks only that record — no gap. Under READ COMMITTED, gap locking is disabled for searches, which is one reason it has fewer deadlocks.

Locks follow the index

InnoDB locks the index records a statement scans, not just the rows it finally changes. Without a usable index:

UPDATE orders SET status = 'cancelled' WHERE customer_email = 'x@y.com';
-- no index on customer_email: every row is scanned, and under
-- REPEATABLE READ every row (and gap) is locked until commit

One missing index can turn a one-row update into a table-wide lock. Indexing the WHERE columns of your updates is a concurrency fix, not just a speed fix.

Lock waits

When a lock is unavailable, the transaction waits. If it waits longer than innodb_lock_wait_timeout (50 seconds by default), the statement fails with error 1205, Lock wait timeout exceeded; try restarting transaction. By default only that statement is rolled back, not the whole transaction (innodb_rollback_on_timeout changes that).

To see who blocks whom in MySQL 8, query performance_schema.data_locks and performance_schema.data_lock_waits, or the friendlier sys.innodb_lock_waits view, which pairs each waiting transaction with its blocker. The usual culprit is a transaction someone left open.

Deadlocks

A deadlock is a cycle of waits: