Locking, deadlocks, and concurrency bugs

Locking, deadlocks, and concurrency bugs. Transactions protect data integrity, but under load they also queue behind each other. Understanding which locks MySQL takes – and why sessions sometimes deadlock – saves hours of guessing.

InnoDB lock types (practical view)

• Row locks – on indexed rows during UPDATE/DELETE/SELECT … FOR UPDATE
• Gap locks – lock the gap between index records (repeatable read isolation)
• Next-key locks – row plus gap; default for many UPDATE/DELETE paths
• Metadata locks – DDL vs DML; an ALTER TABLE blocks writers longer than you expect

Reads in READ COMMITTED or REPEATABLE READ use consistent reads (MVCC snapshots) unless you explicitly lock:

START TRANSACTION;

SELECT stock FROM products WHERE id = 10 FOR UPDATE;
-- Other sessions block on id=10 until you COMMIT or ROLLBACK

UPDATE products SET stock = stock - 1 WHERE id = 10;

COMMIT;

Deadlocks

A deadlock is two (or more) transactions waiting on each other’s locks. InnoDB detects this and rolls back one transaction – the victim. The other proceeds. Your app must retry the victim’s work.

-- Session A                          -- Session B
START TRANSACTION;                     START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
                                       UPDATE accounts SET balance = balance - 5 WHERE id = 2;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
                                       UPDATE accounts SET balance = balance + 5 WHERE id = 1;
-- Deadlock: one session gets ERROR 1213 (40001)

Fix: lock rows in a consistent order everywhere. Always update account id 1 before id 2. Same rule for any pair of resources.

Inspecting locks

-- Current InnoDB transactions
SELECT * FROM information_schema.innodb_trx;

-- Lock waits (MySQL 8.0+)
SELECT * FROM performance_schema.data_lock_waits;

-- Recent deadlock from error log or:
SHOW ENGINE INNODB STATUSG
-- Look for LATEST DETECTED DEADLOCK section

Common concurrency bugs

Lost update – two sessions read stock = 5, both subtract 1, both write 4. Result should be 3. Fix with atomic UPDATE or SELECT FOR UPDATE inside a transaction:

-- Atomic (preferred for simple counters)
UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock >= 1;

-- Check ROW_COUNT() or affected rows in app code

Phantom reads – rare in practice with InnoDB’s next-key locking on indexed ranges. Still possible with gap-free scans or READ COMMITTED.

Long transactions – holding locks while calling external APIs. Keep transactions short: read and decide in app if you must, but lock and write in one tight block.

Isolation level choice

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Fewer gap locks, less deadlock risk on some workloads
-- Default REPEATABLE READ is fine for most OLTP if queries are indexed

Do not change isolation globally without measuring. READ COMMITTED can expose non-repeatable reads in long reports inside a transaction.

App-level habits

• Retry deadlocks with exponential backoff (MySQL expects this)
• Use unique constraints to make INSERT idempotent where duplicates are the race
• Avoid SELECT * then UPDATE in PHP/Python loops – batch or lock once
• Index columns in WHERE of locking reads – otherwise InnoDB locks more rows than you think

The next tutorial covers full-text search in MySQL – and when a dedicated search engine is the better tool.