Understanding MySQL SELECT … FOR UPDATE Locks: Row, Gap, or Table?

Introduction

A few days ago a colleague asked: in MySQL, if transaction A locks a row with SELECT ... FOR UPDATE WHERE id=1 and hasn't committed, will transaction B's SELECT ... WHERE id=1 be blocked? This article explores the behavior of SELECT ... FOR UPDATE and how improper use can lock an entire table and degrade performance.

1. Why Use Row Locks?

Consider a scenario where user A transfers 2000 and user B transfers 3000 to an account initially holding 1000. Both transactions read the current balance, add their amount, and update the row: update account set money=#{money} where id=123; If both updates run concurrently, the final balance may be 3000, 4000, or 6000, leading to data inconsistency. To avoid this, a lock must ensure the transactions execute sequentially. MySQL provides table locks, row locks, and gap locks; for this scenario we need a row lock, achieved with SELECT ... FOR UPDATE.

2. Primary Key

When the WHERE clause uses the primary key, the lock is a row lock. Example:

begin;<br>select * from user where id=1 for update;<br>update user set age=22 where id=1;<br>commit;

Another transaction attempting to update the same row will wait until the first transaction releases the lock.