Locking, Concurrency and Deadlocks

Article · 3:00

Row locks, what they block, and how to read a deadlock report rather than just retrying.

InnoDB locks rows, not tables, which is most of why it replaced MyISAM. A write takes an exclusive lock on the rows it touches, held until the transaction ends. Plain SELECT takes no lock at all - it reads a consistent snapshot - which is why readers do not block writers and writers do not block readers.

When you need a read that does lock, there are two forms. SELECT ... FOR UPDATE takes an exclusive lock, which is what you want before reading a stock level you are about to decrement. SELECT ... FOR SHARE takes a shared lock, letting other readers in but keeping writers out.

The detail that surprises people is that InnoDB locks index records, not rows as such. A statement whose WHERE cannot use an index may end up locking far more rows than it changes, because it locked everything it examined. This is one of the ways a missing index becomes a concurrency problem rather than merely a slow query.

A deadlock is two transactions each holding what the other needs. InnoDB detects it, kills the cheaper one with error 1213, and the other proceeds - so a deadlock is not corruption, it is a transaction that must be retried. The fix is to make it rarer: acquire locks in a consistent order across your application, keep transactions short, and index the columns your WHERE clauses use. SHOW ENGINE INNODB STATUS prints the last deadlock in full, including both statements, which is how you find out what order to standardise on.

-- A plain SELECT takes no locks: it reads a snapshot. Readers and
-- writers do not block each other.
SELECT stock FROM products WHERE id = 1;

-- A read you intend to act on must lock. Otherwise two sessions both
-- read stock = 1 and both sell the last one.
START TRANSACTION;
  SELECT stock FROM products WHERE id = 1 FOR UPDATE;
  UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- The deadlock, written out. Two sessions, opposite order:
--
--   Session A                          Session B
--   START TRANSACTION;                 START TRANSACTION;
--   UPDATE accounts ... WHERE id = 1;  UPDATE accounts ... WHERE id = 2;
--   UPDATE accounts ... WHERE id = 2;  UPDATE accounts ... WHERE id = 1;
--                 -> ERROR 1213: Deadlock found
--
-- The fix is not a retry loop. It is a consistent ORDER:
START TRANSACTION;
  UPDATE accounts SET balance = balance - 100
   WHERE id IN (1, 2) ORDER BY id;      -- every session, lowest id first
COMMIT;

-- The last deadlock in full, with both statements and the locks held.
SHOW ENGINE INNODB STATUS;

-- What is waiting on what, right now.
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits;

-- Do not wait forever for a lock.
SET SESSION innodb_lock_wait_timeout = 5;