Shared Locks (FOR SHARE) vs Exclusive Locks (FOR UPDATE)
By default, MySQL InnoDB executes consistent non-locking reads using Multi-Version Concurrency Control (MVCC). A plain SELECT never locks rows and never waits for locks.
However, in concurrent transactional workflows (such as inventory deduction, payment capture, or seat reservations), relying on un-locked reads causes Race Conditions. To coordinate concurrent writes, you must use Locking Reads: Shared Locks (FOR SHARE) and Exclusive Locks (FOR UPDATE).
The Lock Compatibility Matrix
InnoDB provides two fundamental locking primitives at the row level:
1
Shared Lock (S Lock - FOR SHARE):
Permits other transactions to read the locked row.
Blocks other transactions from modifying or acquiring exclusive locks on the row.
Multiple transactions can hold Shared locks on the same row concurrently.
1
Exclusive Lock (X Lock - FOR UPDATE):
Acquired automatically by UPDATE and DELETE statements.
Acquired manually via SELECT ... FOR UPDATE.
Blocks ALL other transactions from acquiring either Shared or Exclusive locks on the row!
Only one transaction can hold an Exclusive lock on a row at any time.
Requested Lock Existing Lock
Shared Lock (S)
Exclusive Lock (X)
Shared Lock (S)
Compatible (Granted)
Conflict (Blocked / Waits)
Exclusive Lock (X)
Conflict (Blocked / Waits)
Conflict (Blocked / Waits)
Practical Walkthrough: Preventing Ticket Overbooking with FOR UPDATE
Suppose two customers attempt to book the last available concert seat simultaneously:
1-- Transaction 1 (Customer A):
2STARTTRANSACTION;
3
4-- Select AND lock the specific seat row exclusively!
5SELECT seat_id, is_booked, price
6FROM concert_seats
7WHERE seat_id =42
8FORUPDATE;
9
10-- Transaction 1 checks: is_booked is 0 (Available!)
11-- At the exact same microsecond, Transaction 2 (Customer B) runs:
12-- SELECT ... WHERE seat_id = 42 FOR UPDATE;
13-- RESULT: Transaction 2 is FORCED TO WAIT until Transaction 1 finishes!
14
15-- Transaction 1 books the seat:
16UPDATE concert_seats SET is_booked =1, booked_by ='Customer A'WHERE seat_id =42;
17
18-- Transaction 1 commits and releases the exclusive lock:
19COMMIT;
20
21-- NOW Transaction 2 wakes up, reads the seat, sees is_booked = 1, and aborts booking!
Non-Blocking Variations in MySQL 8.0: NOWAIT & SKIP LOCKED
Prior to MySQL 8.0, if a row was locked, a concurrent FOR UPDATE query blocked until innodb_lock_wait_timeout (default 50 seconds) expired.
MySQL 8.0 introduces high-throughput concurrency clauses:
1. NOWAIT: Fail Fast Without Waiting
1SELECT*FROM concert_seats WHERE seat_id =42FORUPDATE NOWAIT;
If locked, MySQL throws ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
2. SKIP LOCKED: High-Throughput Job Queues
1-- Fetch and lock 5 pending emails that are not already being processed by other worker nodes!
2SELECT*FROM email_queue
3WHEREstatus='PENDING'
4LIMIT5
5FORUPDATE SKIP LOCKED;
Any rows currently locked by background worker 1 are skipped, allowing worker 2 to process remaining rows instantly with zero lock contention!
Interactive Quiz• Knowledge Check
Multiple Choice Questions
Question 1
What type of lock is acquired when executing SELECT ... FOR UPDATE?
Question 2
Can multiple concurrent transactions hold a Shared Lock (FOR SHARE) on the exact same row simultaneously?
Question 3
What does the MySQL 8.0 NOWAIT clause do when appended to FOR UPDATE?
Question 4
How does SKIP LOCKED revolutionize distributed background task worker queues?
Question 5
What system variable configures how long an InnoDB transaction will wait for a row lock before timing out?
Next Lesson
InnoDB Row-Level Locking, Gap Locks & Next-Key Locks
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.