MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
One of MySQL InnoDB's greatest architectural strengths is its ability to perform Row-Level Locking. Older engines (like MyISAM) locked the entire table for every write operation, causing massive contention.
However, InnoDB's row-level locking mechanism does not lock arbitrary data rows on disk; it locks the index records!
To prevent phantom reads under REPEATABLE READ, InnoDB employs three distinct locking mechanisms: Record Locks, Gap Locks, and Next-Key Locks.
A Record Lock locks a specific physical index entry:
If user_id has a unique index, InnoDB applies a Record Lock only to the index leaf entry where user_id = 10. Other transactions can freely insert, update, or delete user_id = 9 or user_id = 11.
A Gap Lock locks the open "gap" (the empty space) between two consecutive index records, or before the first / after the last index record:
In this query:
10.(5, 10).(10, 15).If Transaction 2 attempts to INSERT INTO users (user_id) VALUES (8);, Transaction 2 is blocked! Gap locking prevents other transactions from inserting new rows into the range, eliminating Phantom Reads!
In InnoDB, a Next-Key Lock is an index Record Lock combined with a Gap Lock on the space immediately preceding that index record:
$$ ext{Next-Key Lock on Record } R = ext{Gap } (R_{prev}, R] + ext{Record Lock on } R$$
REPEATABLE READ isolation level, InnoDB uses Next-Key Locks by default when searching or scanning index ranges.=), InnoDB automatically degrades the Next-Key Lock to a simple Record Lock for maximum concurrency.UPDATE or SELECT ... FOR UPDATE with a WHERE filter on an unindexed column, InnoDB cannot navigate an index tree.
It must scan every index record in the table, locking EVERY SINGLE ROW AND GAP!
An unindexed update on a single row effectively locks the ENTIRE TABLE, bringing your entire production application to a dead stop!Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
| Previous Lesson | Next Lesson |
|---|---|
| Shared Locks (FOR SHARE) vs Exclusive Locks (FOR UPDATE) | Detecting & Mitigating Database Deadlocks |
Test your understanding of this lesson with 5 questions. Each question has one correct answer.