Pessimistic Locking
SELECT ... FOR UPDATE acquires exclusive row locks, preventing other transactions from modifying the selected rows until COMMIT or ROLLBACK.
MySQL
Intermediate
4 min read
Row Locking with SELECT FOR UPDATE
Example
START TRANSACTION;
-- Lock the row to prevent concurrent updates
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Now safely update
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
COMMIT;
-- Shared lock (read lock, blocks writes but not reads):
SELECT * FROM products WHERE id = 10 FOR SHARE; Pro Tip
FOR UPDATE prevents phantom reads in check-then-act patterns like inventory reservation.