Row locks with SELECT ... FOR UPDATE
50 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)Two users book the last seat at the same moment. Each reads "1 seat left", decides it is free, and writes, so the seat is sold twice. How do you make "read, decide, write" safe?
Locking the rows you will change
SELECT ... FOR UPDATE reads the matching rows and puts an exclusive lock on each of them until the transaction ends. Another transaction that tries to lock or change those rows must wait.
Read the stock, decide, then write: that is how a lost update starts. If the read itself locks the row, nobody can slip in between.
Example 1 · FOR UPDATE turns the lost update into a wait
-- Setup, once, in either session:DROP TABLE IF EXISTS train_quota;CREATE TABLE train_quota ( train_no INT PRIMARY KEY, train_name VARCHAR(40) NOT NULL, seats_left INT NOT NULL);INSERT INTO train_quota (train_no, train_name, seats_left) VALUES (12627, 'Karnataka Express', 10);
-- Step 1 · Session A: booking 1 locks the rowSTART TRANSACTION;SELECT seats_left FROM train_quota WHERE train_no = 12627 FOR UPDATE;
-- Step 2 · Session B: a plain read does not waitSELECT seats_left FROM train_quota WHERE train_no = 12627;
-- Step 3 · Session B: booking 2 asks for the lockSTART TRANSACTION;SELECT seats_left FROM train_quota WHERE train_no = 12627 FOR UPDATE;
-- Step 4 · Session A: the app computed 10 - 1UPDATE train_quota SET seats_left = 9 WHERE train_no = 12627;COMMIT;
-- Step 5 · Session B: the app computed 9 - 1UPDATE train_quota SET seats_left = 8 WHERE train_no = 12627;COMMIT;SELECT seats_left FROM train_quota WHERE train_no = 12627;-- Step 1 · Session A +------------+ | seats_left | +------------+ | 10 | +------------+ -- Step 2 · Session B +------------+ | seats_left | +------------+ | 10 | +------------+ -- Step 3 · Session B (waited until step 4) +------------+ | seats_left | +------------+ | 9 | +------------+ -- Step 5 · Session B +------------+ | seats_left | +------------+ | 8 | +------------+
What this code does
- Step 3 waits until A commits, then returns 9, the newest committed value, not B's snapshot. So B's app computes 8 and both bookings count.
- Step 2 shows that a plain SELECT is never blocked by a row lock: it reads its snapshot at once.
A locking read also skips the snapshot: it returns the newest committed data, so the decision is made on data that cannot change under you.
Three rules. Use it inside a transaction: with autocommit on, the lock ends with the statement. A plain SELECT in another session does not wait; it still reads its snapshot. And SELECT ... FOR SHARE (MySQL 8.0; older versions write LOCK IN SHARE MODE) takes a shared lock: others may also read-lock the row, but nobody can change it.
A seat booking without double booking
Double booking is when two users book the same seat or item because both saw it as free at the same moment. A transaction that locks the row before checking it prevents this.
The booking pattern: start a transaction, lock the seat with SELECT ... FOR UPDATE, check that it is free, mark it booked, commit.
Example 2 · Two passengers, one berth
-- Setup, once, in either session:DROP TABLE IF EXISTS berths;CREATE TABLE berths ( train_no INT NOT NULL, coach CHAR(3) NOT NULL, berth_no TINYINT NOT NULL, status ENUM('FREE', 'BOOKED') NOT NULL DEFAULT 'FREE', passenger VARCHAR(40) NULL, PRIMARY KEY (train_no, coach, berth_no));INSERT INTO berths (train_no, coach, berth_no) VALUES (12627, 'S4', 21), (12627, 'S4', 22), (12627, 'S4', 23), (12627, 'S4', 24);
-- Step 1 · Session A: Asha locks berth S4-21START TRANSACTION;SELECT berth_no, status, passenger FROM berthsWHERE train_no = 12627 AND coach = 'S4' AND berth_no = 21 FOR UPDATE;
-- Step 2 · Session B: Ravi wants the same berthSTART TRANSACTION;SELECT berth_no, status, passenger FROM berthsWHERE train_no = 12627 AND coach = 'S4' AND berth_no = 21 FOR UPDATE;
-- Step 3 · Session A: it was FREE, so book itUPDATE berths SET status = 'BOOKED', passenger = 'Asha Rao'WHERE train_no = 12627 AND coach = 'S4' AND berth_no = 21;COMMIT;
-- Step 4 · Session B: it is BOOKED: the app shows "seat taken"ROLLBACK;-- Step 1 · Session A +----------+--------+-----------+ | berth_no | status | passenger | +----------+--------+-----------+ | 21 | FREE | NULL | +----------+--------+-----------+ -- Step 2 · Session B (waited until step 3) +----------+--------+-----------+ | berth_no | status | passenger | +----------+--------+-----------+ | 21 | BOOKED | Asha Rao | +----------+--------+-----------+
What this code does
- Ravi's locking read in step 2 waits for Asha's transaction. When Asha commits, it returns the berth as BOOKED by Asha Rao, so Ravi's app rolls back.
- Without FOR UPDATE, both would read FREE and both would book it. The lock is taken through the primary key, so only this one berth is locked.
Example 3 · One berth, one statement: a conditional UPDATE
DROP TABLE IF EXISTS berths;CREATE TABLE berths ( train_no INT NOT NULL, coach CHAR(3) NOT NULL, berth_no TINYINT NOT NULL, status ENUM('FREE', 'BOOKED') NOT NULL DEFAULT 'FREE', passenger VARCHAR(40) NULL, PRIMARY KEY (train_no, coach, berth_no));INSERT INTO berths (train_no, coach, berth_no) VALUES (12627, 'S4', 21), (12627, 'S4', 22), (12627, 'S4', 23), (12627, 'S4', 24);
-- Ravi books S4-21: one statement checks and booksUPDATE berths SET status = 'BOOKED', passenger = 'Ravi Kumar'WHERE train_no = 12627 AND coach = 'S4' AND berth_no = 21 AND status = 'FREE';SELECT ROW_COUNT() AS ravi_booked;
-- Meena tries the same berth a moment laterUPDATE berths SET status = 'BOOKED', passenger = 'Meena Iyer'WHERE train_no = 12627 AND coach = 'S4' AND berth_no = 21 AND status = 'FREE';SELECT ROW_COUNT() AS meena_booked;
SELECT berth_no, status, passenger FROM berths ORDER BY berth_no;+-------------+ | ravi_booked | +-------------+ | 1 | +-------------+ +--------------+ | meena_booked | +--------------+ | 0 | +--------------+ +----------+--------+------------+ | berth_no | status | passenger | +----------+--------+------------+ | 21 | BOOKED | Ravi Kumar | | 22 | FREE | NULL | | 23 | FREE | NULL | | 24 | FREE | NULL | +----------+--------+------------+
What this code does
AND status = 'FREE'makes the UPDATE book the berth only if it is still free;ROW_COUNT()tells the app who won. Ravi gets 1, Meena gets 0.- This works for a single row because the UPDATE locks, checks and writes in one step. When the decision needs several reads, use SELECT ... FOR UPDATE.
Two people who click the same berth at the same moment are served one after the other. When the second one continues, it sees the committed result of the first, the berth already BOOKED, and the application rolls back and shows "seat taken".
InnoDB locks the index entries it scans, not only the rows it returns. Lock rows through the primary key or another index; a WHERE on a column with no index scans, and locks, every row of the table, which blocks everyone.
Order of execution
Book one berth safely
Put the steps of a safe seat booking in the order the code runs them.
NOWAIT and SKIP LOCKED
NOWAIT and SKIP LOCKED are options for locking reads in MySQL 8.0. NOWAIT fails at once with error 3572 instead of waiting. SKIP LOCKED leaves locked rows out of the result and returns the rest.
By default a locking read waits for a locked row, up to innodb_lock_wait_timeout seconds (50 by default). Sometimes waiting is the wrong answer.
Example 4 · NOWAIT fails fast, SKIP LOCKED moves on
-- Setup, once, in either session:DROP TABLE IF EXISTS berths;CREATE TABLE berths ( train_no INT NOT NULL, coach CHAR(3) NOT NULL, berth_no TINYINT NOT NULL, status ENUM('FREE', 'BOOKED') NOT NULL DEFAULT 'FREE', passenger VARCHAR(40) NULL, PRIMARY KEY (train_no, coach, berth_no));INSERT INTO berths (train_no, coach, berth_no) VALUES (12627, 'S4', 21), (12627, 'S4', 22), (12627, 'S4', 23), (12627, 'S4', 24);
-- Step 1 · Session ASTART TRANSACTION;SELECT berth_no, status, passenger FROM berthsWHERE train_no = 12627 AND coach = 'S4' AND berth_no = 21 FOR UPDATE;
-- Step 2 · Session B: do not wait for S4-21START TRANSACTION;SELECT berth_no, status, passenger FROM berthsWHERE train_no = 12627 AND coach = 'S4' AND berth_no = 21 FOR UPDATE NOWAIT;
-- Step 3 · Session B: give me any free berth that is not lockedSELECT berth_no FROM berthsWHERE train_no = 12627 AND coach = 'S4' AND status = 'FREE'ORDER BY berth_no LIMIT 1FOR UPDATE SKIP LOCKED;
-- Step 4 · Session ACOMMIT;
-- Step 5 · Session BCOMMIT;-- Step 1 · Session A +----------+--------+-----------+ | berth_no | status | passenger | +----------+--------+-----------+ | 21 | FREE | NULL | +----------+--------+-----------+ -- Step 2 · Session B ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set. -- Step 3 · Session B +----------+ | berth_no | +----------+ | 22 | +----------+
What this code does
- Step 2 fails at once with error 3572 because A holds the lock on berth 21. The app can say "being booked, try another" without making Ravi wait.
- Step 3 skips the locked berth 21 and returns 22, the first free berth nobody holds. Both options need MySQL 8.0.1 or later.
Example 5 · A delivery queue with SKIP LOCKED
-- Setup, once, in either session:DROP TABLE IF EXISTS delivery_jobs;CREATE TABLE delivery_jobs ( job_id INT PRIMARY KEY, product VARCHAR(40) NOT NULL, agent VARCHAR(30) NULL);INSERT INTO delivery_jobs (job_id, product, agent) VALUES (1, 'Redmi Note 13', NULL), (2, 'boAt Airdopes 141', NULL), (3, 'Clean Code', NULL), (4, 'Running Shoes', NULL);
-- Step 1 · Session A: agent Ravi takes the next jobSTART TRANSACTION;SELECT job_id, product FROM delivery_jobsWHERE agent IS NULL ORDER BY job_id LIMIT 1FOR UPDATE SKIP LOCKED;
-- Step 2 · Session B: agent Imran, at the same momentSTART TRANSACTION;SELECT job_id, product FROM delivery_jobsWHERE agent IS NULL ORDER BY job_id LIMIT 1FOR UPDATE SKIP LOCKED;
-- Step 3 · Session AUPDATE delivery_jobs SET agent = 'Ravi' WHERE job_id = 1;COMMIT;
-- Step 4 · Session BUPDATE delivery_jobs SET agent = 'Imran' WHERE job_id = 2;COMMIT;SELECT job_id, product, agent FROM delivery_jobs ORDER BY job_id;-- Step 1 · Session A +--------+---------------+ | job_id | product | +--------+---------------+ | 1 | Redmi Note 13 | +--------+---------------+ -- Step 2 · Session B +--------+-------------------+ | job_id | product | +--------+-------------------+ | 2 | boAt Airdopes 141 | +--------+-------------------+ -- Step 4 · Session B +--------+-------------------+-------+ | job_id | product | agent | +--------+-------------------+-------+ | 1 | Redmi Note 13 | Ravi | | 2 | boAt Airdopes 141 | Imran | | 3 | Clean Code | NULL | | 4 | Running Shoes | NULL | +--------+-------------------+-------+
What this code does
- Ravi locks job 1. Imran's query skips it and locks job 2, without waiting. Two agents, two different jobs, no double delivery.
- With plain FOR UPDATE, Imran would wait for Ravi and then, after Ravi committed, his query would find job 2 anyway, but much more slowly under load.
Use NOWAIT when waiting is pointless: the app can say "someone else is booking this seat, pick another" straight away. Use SKIP LOCKED for work queues: many delivery agents each take the next free job with ... ORDER BY job_id LIMIT 1 FOR UPDATE SKIP LOCKED, and no two agents get the same job.
SKIP LOCKED gives a deliberately incomplete view of the table, so never use it for reports or totals.
Optimistic locking with a version column
Optimistic locking takes no lock while the user thinks. Each row has a version number, and the save works only if the version is still the one you read; otherwise the user reloads.
Two admins edit the same product price for two minutes. A row lock held that long would block every order.
Example 6 · Optimistic locking: the second save changes nothing
DROP TABLE IF EXISTS catalog_items;CREATE TABLE catalog_items ( item_id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, price DECIMAL(7,2) NOT NULL, version INT NOT NULL DEFAULT 1);INSERT INTO catalog_items (item_id, name, price) VALUES (1, 'Clean Code', 499.00);
-- Both admins open the edit screen and read version 1.SELECT item_id, name, price, version FROM catalog_items WHERE item_id = 1;
-- Admin Kiran saves first: price 549.UPDATE catalog_items SET price = 549.00, version = version + 1WHERE item_id = 1 AND version = 1;SELECT ROW_COUNT() AS kiran_saved;
-- Admin Lata saves second, still holding version 1: price 529.UPDATE catalog_items SET price = 529.00, version = version + 1WHERE item_id = 1 AND version = 1;SELECT ROW_COUNT() AS lata_saved;
SELECT item_id, name, price, version FROM catalog_items WHERE item_id = 1;+---------+------------+--------+---------+ | item_id | name | price | version | +---------+------------+--------+---------+ | 1 | Clean Code | 499.00 | 1 | +---------+------------+--------+---------+ +-------------+ | kiran_saved | +-------------+ | 1 | +-------------+ +------------+ | lata_saved | +------------+ | 0 | +------------+ +---------+------------+--------+---------+ | item_id | name | price | version | +---------+------------+--------+---------+ | 1 | Clean Code | 549.00 | 2 | +---------+------------+--------+---------+
What this code does
- Kiran's UPDATE matches
version = 1, saves 549.00 and moves the version to 2. Lata's UPDATE still asks for version 1, so it changes 0 rows instead of silently overwriting Kiran's price. - The app reads
ROW_COUNT(): 0 means "someone changed this item; reload and try again". No lock was held while the admins were typing.
The save is UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = <the version you read>. If someone else saved in between, the UPDATE changes 0 rows and the app asks the user to reload. Pessimistic locking is SELECT ... FOR UPDATE: lock first, because a conflict is likely and the transaction is short.
Choose with two questions: how often do two users change the same row, and how long is the gap between reading and writing? Use pessimistic locking (FOR UPDATE) for frequent conflicts and short transactions, such as seat booking, stock at a sale and money transfers. Use optimistic locking (a version column) for rare conflicts and long think time, such as profile and settings edits and admin screens. With both, always check the affected row count, and decide what the user sees when you lose.
Next step
Check your understanding
12 quick questions