The database locks exist to protect shared resources or objects, This could be Tables, Data Rows, Data Blocks, Cached Items, Connections or even the Entire Database Systems . The locks ensure reliable transaction that adhere to ACID properties of transactional systems . ACID is an acronym that stands for Atomicity, Consistency, Isolation and Durability . In this post I have explained how InnoDB locks works.
What are the different kinds of locking supported in InnoDB (MySQL 5.7) ?
- Shared and Exclusive Locks
- Intention Locks
- Record Locks
- Gap Locks
- Next-Key Locks
- Insert Intention Locks
- AUTO-INC Locks
- Predicate Locks for Spatial Indexes
Row-level locking in InnoDB
InnoDB handles row-level locking with types of locks, shared (S) locks and exclusive (X) locks . The shared (S) lock allows transaction that holds the lock to read a row and exclusive (X) lock permits the transaction that holds the lock to update or delete a row . I have explained this below with an example
Consider transactions T1 and T2 . Transaction T1 is holding shared (S) lock on row R1, Let’s see how MySQL handles locks now when Transaction T2 request for lock on same row R1 :
A request for shared (S) lock from T2 will be granted immediately so now both transactions T1 and T2 are holding an S lock on R1
What if Transaction T2 is holding an exclusive (X) lock on row R1 ? Then Transaction T2 have to wait for an lock of either type – shared (S) locks and exclusive (X) locks , So Transaction T2 has to wait for Transaction T1 to release it’s lock on row R1 .
Intention Locks
InnoDB permits coexistence of both row locks and table locks. Intention locks in InnoDB are table-level locks that indicates which type of lock (shared or exclusive) a transaction needs in the future for a row in a table . We have two types of intention locks :
Intention shared lock (IS) indicates that a transaction intends to set a shared (S) lock on individual rows of a table
Intention exclusive lock (IX) indicates that a transaction intends to set an exclusive (X) lock on individual rows of a table
In Intention Locks, Before a transaction can acquire a shared lock on row of a table, It must acquire either Intention shared lock (IS) or Intention exclusive lock (IX) on the table and to acquire an exclusive lock on rows of a table, It must first acquire an Intention exclusive lock (IX) lock on the table . The lock is granted to a requesting transaction only if it is compatible with existing lock, not definitely if it conflicts with existing locks (to avoid possible deadlocks) . The only purpose of intention locks is to show some transaction is locking a row or going to lock a row of a table . You can monitor transaction data of an intention lock with SHOW ENGINE INNODB STATUS .
Record Locks
A record lock is a lock on index record . For example, SELECT C1 FROM T1 WHERE C1=50 FOR UPDATE; prevents any other transactions from inserting or updating or deleting rows where T1.C1 is 50 . Records locks always locks index records, If your table don’t have indexes then InnoDB creates hidden clustered index and use that index for record locking . You can monitor transaction data for record locking with SHOW ENGINE INNODB STATUS .
Gap Locks
A gap lock is a lock on a gap between index records . For example, SELECT C1 FROM T1 WHERE C1 BETWEEN 30 and 60 FOR UPDATE ; In this case, the gaps between all existing values in the range are locked . Gap locks are actually the tradeoffs between performance and concurrency .
Next-Key Locks
In Next-Key Locks InnoDB performs row-level locking in such a way that when it searches or scans a table index, It sets shared or exclusive locks on the index records it encounters . In short, A next-key lock is a combination of a record lock on the index record and gap lock on the gap before the index record . Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R1 in an index, another session cannot insert a new index record in the gap immediately before R1 in the index order .
Insert Intention Locks
An insert intention locks are an type of gap lock set by INSERT operations prior to row insertion . These lock signals intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Let’s consider that there are index records with values of 15 and 20 . Separate transactions that attempt to insert values of 17 and 19 respectively, each lock gap between 15 and 20 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each because the rows are nonconflicting .
AUTO-INC Locks
An AUTO-INC lock is table-level lock happens on transactions inserting to table with AUTO_INCREMENT columns . In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values. The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking . It allows you to choose the trade off between predictable sequences of auto-increment values and concurrency for insert-operations .
Predicate Locks for Spatial Indexes
InnoDB supports SPATIAL indexing of columns containing spacial columns . To enable support of isolation levels for tables with SPATIAL indexes, InnoDB uses predicate locks . A SPATIAL index contains minimum bounding rectangle (MBR) values, so InnoDB enforces consistent read on the index by setting a predicate lock on MBR values used for a query, Else transactions cannot insert or modify a row that would match the query condition
