Summary of MySQL InnoDB locks

1. Shared and Exclusive Locks

shared lock

exclusive lock

InnoDB implements standard row-level locks, which include two types of locks, shared locks and exclusive locks.

A shared (S) lock permits the transaction that holds the lock to read a row.

An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

A shared lock allows the transaction holding the lock to read a row.

An exclusive lock allows the transaction holding the lock to update or delete the row.

If transaction T1 holds a shared lock (S) on row r, then a request from a different transaction T2 is processed as follows:

• T2's request for the S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on row r.
• T2's request for the X lock cannot be granted immediately.

If transaction T1 holds an exclusive lock (X) on row r, then a request from a different transaction T2 cannot be immediately granted either type of lock on r. Instead, transaction T2 must wait for transaction T1 to release its lock on row r.

2. Intention Locks

Intention Locks

InnoDB supports multi-granularity locks, allowing row locks and table locks to coexist. For example, a statement such as LOCK TABLES ... WRITE takes an exclusive lock (X lock) on the specified table. To implement locking at multiple levels of granularity, InnoDB uses intention locks. An intention lock is a table-level lock that indicates to a transaction what type of lock (shared or exclusive) it needs to use later on a row in a table.

There are two types of intention locks:

• An intention shared lock (IS) indicates that a transaction intends to set a shared lock on a single row in a table.
• An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on a single row in a table.
  

For example, SELECT ... LOCK IN SHARE MODE sets an IS lock, and SELECT ... FOR UPDATE sets an IX lock.

The agreement of the intention lock is as follows:

Before a transaction can obtain a shared lock on a row in a table, it must first obtain an IS lock or stronger lock on the table.
Before a transaction obtains an exclusive lock on a row in a table, it must first obtain an IX lock on the table.
The compatibility of table-level lock types is as follows:

A lock is granted to the requesting transaction if it is compatible with an existing lock, but is not granted if it conflicts with an existing lock. The transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

Intention locks do not block anything except full-table requests (such as LOCK TABLES ... WRITE). The main purpose of an intent lock is to indicate that someone is locking or wants to lock a row in a table.

3. Record Locks

Record Locks

A record lock is a lock on an index record.

A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

Record locks always lock index records, even if a table has no indexes defined. If the table has no indexes, InnoDB creates a hidden clustered index and uses that index for record locking.

4. Gap Locks

Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

A gap lock is a lock on the gap between index records, or a lock on the gap before the first index record or after the last index record.

For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting the value 15 into the t.c1 column, regardless of whether there is already such a value in the column, because the gaps between all existing values ​​in the range are locked.

A gap may span a single index value, multiple index values, or even be empty.

Gap locks are part of the trade-off between performance and concurrency and are used in some transaction isolation levels but not in others.

For statements that use a unique index to lock rows to search for a unique row, gap locking is not required.

For example, if the id column has a unique index, the following statement takes an index-record lock only for the row with an id value of 100, regardless of whether other sessions insert rows in the preceding gap:

SELECT * FROM child WHERE id = 100;

If the id column has no index or has a nonunique index, the statement locks the leading gap.

It is also worth noting here that different transactions can hold conflicting locks on a gap.

For example, transaction A can hold a shared gap lock (gap S-lock) on a gap, while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason for allowing conflicting gap locks is that if a record is purged from an index, then gap locks on the record held by different transactions must be merged.

The only purpose of gap locks in InnoDB is to prevent other transactions from inserting into the gap. Gap locks can coexist. A gap lock acquired by one transaction does not prevent another transaction from acquiring a gap lock on the same gap. There is no distinction between shared and exclusive interval locks. They do not conflict with each other and perform the same function.

5. Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

A next-key lock is a combination of a record lock on the index record and a gap lock before the index record.

The way InnoDB performs row-level locking is this: when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Therefore, 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, the next-key lock is the index record lock plus the gap lock before the index record. If a session has a shared or exclusive lock on a record R in an index, another session cannot insert a new index record in the gap before R in the index order.

Suppose an index contains the values ​​10, 11, 13, and 20. The possible next-key locks for this index cover the following ranges:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

By default, InnoDB uses the REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans to prevent phantom rows.

6. Insert Intention Locks

Insert Intention Locks

An insert intention lock is a gap lock that is set by an INSERT operation before a row is inserted. This lock means that if multiple transactions insert into the same index gap, they do not need to wait for each other if they are not inserting into the same position in the gap. Assume that there are index records with values ​​4 and 7. Separate transactions attempt to insert values ​​5 and 6. Each transaction locks the gap between 4 and 7 with an insert intention lock before obtaining an exclusive lock on the inserted row, but do not block each other because the rows are non-conflicting.

7. AUTO-INC Locks

An AUTO-INC lock is a special table-level lock acquired by transactions inserting into a table with an AUTO_INCREMENT column. In the simplest case, if one transaction is inserting values ​​into a table, any other transactions must wait for their own inserts into that table so that the rows inserted by the first transaction receive consecutive primary key values.

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

The above is the detailed summary of MySQL InnoDB lock. For more information about MySQL InnoDB lock, please pay attention to other related articles on 123WORDPRESS.COM!