Understanding MySQL Shared vs Exclusive Locks: A Hands‑On Demo

Conclusion

Shared lock (S lock) : also called read lock. The first transaction that acquires an S lock on a row can read and modify it; other transactions may also acquire S locks on the same row but cannot acquire X locks until the first transaction releases its S lock. Use SELECT … LOCK IN SHARE MODE.

Exclusive lock (X lock) : also called write lock. The transaction that acquires an X lock can read and modify the row; no other transaction can acquire any lock on that row until it is released. Use SELECT … FOR UPDATE.

Validate the Conclusion

We created a test table test (id auto‑increment, columns a, b, c) and disabled autocommit ( SET autocommit=0) to simulate two concurrent sessions.

Two MySQL windows (session 1 and session 2) were opened. The following steps were executed:

Session 1: SELECT * FROM test WHERE id=1 LOCK IN SHARE MODE; Session 2: SELECT * FROM test WHERE id=1 FOR UPDATE; – this fails because session 1 holds an S lock.

Session 2: SELECT * FROM test WHERE id=1 LOCK IN SHARE MODE; – succeeds, acquiring another S lock.

The result confirms that when a transaction is the first to acquire an S lock on a row, other transactions can only acquire S locks on that row and cannot acquire X locks until the first transaction releases its lock.

Further operations demonstrated lock behavior and deadlock:

Both sessions can read the row ( SELECT * FROM test WHERE id=1) while the S lock is held.

Session 1 updates the row successfully.

Session 2 attempts to update the same row and fails, causing a deadlock.

Session 1 commits, releasing its S lock.

Session 2 then updates and commits successfully.

These results show that if a transaction is the earliest to acquire an S lock on a data object, other transactions can read the object but cannot modify it until the S lock is released; concurrent S locks on the same data can lead to deadlocks when both try to upgrade to X locks.

Key reminders :

Row‑level locks (shared and exclusive) are implemented by InnoDB.

Set autocommit=0 to control transaction boundaries.

A transaction ends only after COMMIT or ROLLBACK.

Row locks are applied to index records, not to the physical row; using the same index key can cause lock conflicts.

If MySQL decides a full table scan is cheaper, it may use a table lock instead of a row lock.

Gap locks (next‑key locks) are used under REPEATABLE READ when the query uses an index but finds no matching row.

Example queries illustrating different lock scopes: SELECT * FROM test WHERE id=1 FOR UPDATE; – row lock (index present, row exists). SELECT * FROM test WHERE id='100' FOR UPDATE; – next‑key (gap) lock when no matching row. SELECT * FROM test WHERE a='test' FOR UPDATE; – table lock (no index). SELECT * FROM test WHERE id<>2 FOR UPDATE; – table lock (index not used). SELECT * FROM test WHERE id LIKE '%3%' FOR UPDATE; – table lock (index not usable).