Locking & concurrency

Isolation levels decide what a transaction sees. Locks decide what it can change and who must wait. Understanding InnoDB locking helps you avoid lost updates, double-booked seats, oversold stock and mysterious deadlocks.

How InnoDB locks#

• Row-level locks. InnoDB locks individual index records, not whole tables. Two transactions updating different rows don't block each other.
• Writes lock automatically. UPDATE, DELETE and INSERT take exclusive locks on the rows they touch and hold them until COMMIT or ROLLBACK.
• Plain SELECT doesn't lock. It reads a consistent snapshot (MVCC), so readers never wait for writers.
• Lock types. A shared (S) lock allows others to read-lock the row too. An exclusive (X) lock blocks all other locks on that row.

When a lock is held, other transactions wanting a conflicting lock wait, for up to innodb_lock_wait_timeout seconds (50 by default), then fail with ERROR 1205: Lock wait timeout exceeded.

The lost-update problem#

Two shoppers buy the last unit of an item at the same moment:

-- Session A                               -- Session B
SELECT stock FROM products WHERE id = 1;   SELECT stock FROM products WHERE id = 1;
--  → 1 (app thinks: in stock!)           --  → 1 (app thinks: in stock!)
UPDATE products SET stock = 0 WHERE id=1;
                                           UPDATE products SET stock = 0 WHERE id=1;
-- Both orders succeed: oversold.

Plain reads don't lock, so both sessions saw 1. There are two standard fixes.

Fix 1: make the update atomic and conditional. Let the database check and change the value in one statement:

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(30), stock INT NOT NULL);
INSERT INTO products VALUES (1, 'Last-edition vinyl', 1);

UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1;
SELECT ROW_COUNT() AS rows_changed;   -- 1 = we got it
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1;
SELECT ROW_COUNT() AS rows_changed;   -- 0 = sold out, tell the user

+--------------+
| rows_changed |
+--------------+
|            1 |
+--------------+
+--------------+
| rows_changed |
+--------------+
|            0 |
+--------------+

The second session's UPDATE waits for the first one's row lock, then re-checks stock >= 1 against the latest committed value and changes nothing.

Fix 2: lock the row while you decide, with a locking read.

SELECT ... FOR UPDATE and FOR SHARE#

UPDATE products SET stock = 3 WHERE id = 1;

START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;   -- X-lock: others wait here
-- … application logic: is there enough stock? compute prices, etc.
UPDATE products SET stock = stock - 2 WHERE id = 1;
COMMIT;                                               -- lock released

SELECT stock FROM products WHERE id = 1;

+-------+
| stock |
+-------+
|     3 |
+-------+
+-------+
| stock |
+-------+
|     1 |
+-------+

• FOR UPDATE takes an exclusive lock on the rows read. Another transaction's FOR UPDATE, UPDATE or DELETE on those rows waits until you commit. (Their plain SELECTs still read the snapshot without waiting.)
• FOR SHARE (the older spelling is LOCK IN SHARE MODE) takes a shared lock. Others can read-lock the row too, but nobody can modify it until you finish. Use it to make sure a parent row isn't deleted while you insert a child.
• Locking reads always see the latest committed data, not your transaction's snapshot.

Only use locking reads inside a transaction. With autocommit, the lock is released as soon as the statement finishes.

NOWAIT and SKIP LOCKED (MySQL 8.0+)#

By default, a locking read waits. Two options change that:

-- Session A                                       -- Session B
START TRANSACTION;
SELECT * FROM jobs WHERE id = 1 FOR UPDATE;
                                                   SELECT * FROM jobs WHERE id = 1 FOR UPDATE NOWAIT;
                                                   -- ERROR 3572 (HY000): Statement aborted because lock(s)
                                                   -- could not be acquired immediately and NOWAIT is set.

NOWAIT fails immediately instead of waiting. That's useful for "someone else is editing this record" messages. SKIP LOCKED silently skips locked rows, which makes it ideal for job queues where many workers each grab a different job:

CREATE TABLE jobs (
    id INT PRIMARY KEY,
    status VARCHAR(10) NOT NULL,
    INDEX (status, id)
);
INSERT INTO jobs VALUES (1, 'queued'), (2, 'queued'), (3, 'queued');

-- Each worker runs:
START TRANSACTION;
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- → worker A gets 1; a concurrent worker B gets 2 instead of waiting
UPDATE jobs SET status = 'running' WHERE id = 1;
COMMIT;

Gap locks and next-key locks#

Under REPEATABLE READ, InnoDB locks not just matching rows but also the gaps between index records in the scanned range. This stops other transactions from inserting "phantom" rows into a range you've read with a locking read:

SELECT * FROM jobs WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- other sessions now can't INSERT id 15 until this transaction ends

Gap locks are why inserts sometimes wait even though no existing row conflicts. Under READ COMMITTED, InnoDB mostly turns them off, trading phantom protection for more concurrency.

Indexes matter for locking. InnoDB locks the index records it examines, not just the rows that end up changed. UPDATE jobs SET status = 'x' WHERE note = 'abc' on an unindexed column scans and locks every row, effectively locking the whole table for everyone else. Make sure the WHERE of your updates and deletes can use an index.

Deadlocks#

A deadlock happens when two transactions each hold a lock the other needs:

-- Session A                                     -- Session B
START TRANSACTION;                               START TRANSACTION;
UPDATE accounts SET … WHERE id = 1;  -- locks 1
                                                 UPDATE accounts SET … WHERE id = 2;  -- locks 2
UPDATE accounts SET … WHERE id = 2;  -- waits for B
                                                 UPDATE accounts SET … WHERE id = 1;  -- waits for A → cycle!
                                                 -- ERROR 1213 (40001): Deadlock found when trying
                                                 -- to get lock; try restarting transaction
-- A's UPDATE now succeeds

InnoDB detects the cycle instantly, rolls back one transaction (the victim) and lets the other continue. Deadlocks aren't bugs in MySQL; they're a normal part of concurrent systems. Your job is to make them rare and handle them:

1. Retry the whole transaction on error 1213 (and 1205). Most frameworks can do this for you.
2. Access rows in a consistent order. If every transfer locks the lower account id first, the cycle above can't happen.
3. Keep transactions short and touch as few rows as possible.
4. Use good indexes so statements lock only what they need.
5. Inspect the most recent deadlock with SHOW ENGINE INNODB STATUS\G (see the LATEST DETECTED DEADLOCK section).

Table-level locks#

You'll rarely need them with InnoDB, but you should recognise them:

• LOCK TABLES t WRITE; … UNLOCK TABLES; locks whole tables. It's mostly for MyISAM and maintenance, and it implicitly commits the open transaction.
• Metadata locks (MDL). ALTER TABLE needs an exclusive metadata lock, so it waits for every open transaction that has touched the table. One forgotten open transaction can make an ALTER hang, and then every new query on that table queues up behind the ALTER. Check SHOW PROCESSLIST for "Waiting for table metadata lock".

Seeing who is blocking whom#

SELECT * FROM performance_schema.data_lock_waits;      -- MySQL 8: current lock waits
SELECT * FROM sys.innodb_lock_waits\G                   -- friendlier view: who waits, who blocks, the SQL
SHOW PROCESSLIST;                                       -- all connections and what they're doing
KILL 1234;                                              -- end a stuck connection by its Id

(The performance_schema and sys views require the Performance Schema, which is enabled by default.)

What's next#

You can now write correct, concurrent SQL. Next you'll move logic into the database with stored procedures.