Understanding Optimistic and Pessimistic Locks in MySQL/InnoDB

Optimistic Lock

Optimistic locking assumes that concurrent updates are rare, so it does not acquire a physical lock on the data. When updating, the system checks whether another thread has modified the row; if not, the update proceeds, otherwise it is rejected.

The most common implementation uses a numeric version column. Each read retrieves the current version value, and each successful update increments this version.

When submitting an update, the current version stored in the row is compared with the version read earlier; the update succeeds only if they match.

Example

Table schema: id , value , version

select id, value, version from TABLE where id = #{id}

Update statement that checks version and increments it:

update TABLE
set value = 2, version = version + 1
where id = #{id} and version = #{version}

In Laravel you can maintain a lock_version field, verify it on each update, and increment it after a successful write.

Pessimistic Lock

Pessimistic locking assumes that conflicts are likely, so it acquires a lock before accessing the data. Only one thread can modify a locked row; other threads wait until the lock is released.

Shared Lock (LOCK IN SHARE MODE)

A shared lock (read lock) allows multiple transactions to read the same rows concurrently, but prevents any transaction from modifying those rows until all shared locks are released.

Laravel provides the sharedLock() method:

DB::table('users')
    ->where('votes', '>', 100)
    ->sharedLock()
    ->get();

The above query is equivalent to:

select * from `users` where `votes` > '100' lock in share mode;

Adding LOCK IN SHARE MODE places a read lock on each row returned; other transactions can still read the rows but cannot acquire exclusive locks on them.

Exclusive Lock (FOR UPDATE)

An exclusive lock (write lock) prevents any other transaction from reading or writing the locked rows until the lock is released.

Laravel offers the lockForUpdate() method:

DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();

This translates to:

select * from `users` where `votes` > '100' for update;

While both FOR UPDATE and LOCK IN SHARE MODE prevent other transactions from modifying the selected rows, FOR UPDATE also blocks other transactions from acquiring shared locks, whereas LOCK IN SHARE MODE allows concurrent reads.

Conclusion

Optimistic locks are ideal for read‑heavy, low‑conflict scenarios because they avoid the overhead of locking and increase overall throughput.

If conflicts occur frequently, pessimistic locks provide a more stable and reliable way to ensure data consistency.