Mastering MySQL Pessimistic Locks: How SELECT … FOR UPDATE Works

MySQL is frequently asked in large tech companies; this article provides a detailed explanation of MySQL pessimistic locks.

MySQL Pessimistic Lock

Pessimistic locking assumes that conflicts will always occur, so it locks data during processing to prevent other transactions from modifying the same data simultaneously. In other words, lock first, then operate.

Implementation principle of MySQL pessimistic lock

In MySQL (InnoDB engine), pessimistic locks are mainly implemented through row‑level locks.

Before modifying or querying data, the current transaction places an exclusive lock on the relevant rows.

A typical SQL statement is SELECT ... FOR UPDATE, which locks the selected rows.

<ol><li>-- 开启事务</li><li>BEGIN;</li><li>-- 悲观锁：锁定账户为 id = 123 的行，防止其他事务修改</li><li>SELECT balance FROM accounts WHERE id = 123 FOR UPDATE;</li><li>-- 假设查到的余额为 1000</li><li>-- 业务逻辑：进行扣款，扣除 500</li><li>UPDATE accounts SET balance = balance - 500 WHERE id = 123;</li><li>-- 提交事务</li><li>COMMIT;</li></ol>

In this example, the SELECT ... FOR UPDATE statement immediately places an exclusive lock on the row where id = 123.

If another transaction attempts a FOR UPDATE on the same row, it will wait until the first transaction commits or rolls back, releasing the lock.