Understanding Optimistic and Pessimistic Locks in MySQL

Optimistic and pessimistic locks are conceptual concurrency‑control techniques commonly used in database management systems and also in caching solutions such as Memcache, Hibernate, and Tair.

Although they are not physical locks like row‑level or table‑level locks, they represent different philosophies: pessimistic locking assumes conflicts and acquires a lock before processing, while optimistic locking assumes conflicts are rare and checks for them only at commit time.

In MySQL, a pessimistic lock can be implemented with an exclusive lock using SELECT ... FOR UPDATE . The transaction must disable autocommit ( set autocommit=0; ) and then issue a SELECT ... FOR UPDATE statement to lock the target row before updating it.

This ensures that only one transaction can modify the row (e.g., id = 1 ) at a time, preventing concurrent updates.

Optimistic locking, on the other hand, is typically realized by adding a version column to the table and checking that the version has not changed during the update. An example update statement is:

update t_goods
set status=2, version=version+1
where id=#{id} and version=#{version}

If the version condition fails, the update is rejected and the application must handle the conflict.

The two approaches differ in several aspects: pessimistic locking acquires a blocking lock at the start of the transaction, which can lead to thread blocking, increased response time, and possible deadlocks under high contention; optimistic locking does not block but may generate many failed updates that the business logic must handle, and it relies on compare‑and‑swap (CAS) for higher efficiency.

For high‑concurrency scenarios, especially with MySQL 5.x where NOWAIT is unavailable, the article recommends using optimistic locking. MySQL 8.0 introduces SELECT ... FOR UPDATE NOWAIT , which can mitigate blocking but does not eliminate the overhead and deadlock risk associated with pessimistic locks.