Shared Locks and Exclusive Locks X: Two types of standard row-level Locks

If transaction 1 holds the S lock on row R, then transaction 2 can immediately hold the S lock on row R, but transaction 2 cannot immediately hold the X lock on row R. If transaction 1 holds the X lock on row R, then transaction 2 cannot immediately hold the X lock on row R, The X lock that must wait for transaction 1 to release row R can be interpreted as the X lock colliding with any lock

2. Intention Locks

Innodb supports multi-granularity locking, which allows row and table locks to co-exist. Intentional locks are table-level locks, indicating which locks are required for later rows. There are two types of intentional locks: LOCK IN SHARE MODE (IX) SELECT * from SHARE MODE (IX) SELECT * from SHARE MODE (IX) (1) Before a transaction acquires an S LOCK, it must acquire an IS LOCK or a stronger LOCK. (2) Before a transaction acquires an X LOCK, it must acquire a IX LOCK.If a transaction requires a lock that does not conflict with a lock currently held by another transaction, the lock is granted successfully; otherwise, the conflicting lock is released. The main purpose of intent locks is to show that someone is locking rows or wants to lock rows in a table.

Record Locks

A row lock is a lock on an index record. FOR example, SELECT c1 FROM T WHERE c1=10 FOR UPDATE prevents any transaction FROM insert, UPDATE, or delete on a row WHERE c1=10. InnoDB creates a hidden clustered index and uses this index to lock records.

4. Gap Locks

Interval locking locks the contents of multiple index records, or gaps before or after the first index record, such as SELECT c1 FROM T WHERE c1 BETWEEN 10 and 20 FOR UPDATE, prevents other transactions FROM inserting the value 15 into column T.c1. Whether or not you already have this value. A table with a unique index does not use an interval lock to search for a unique row (although it is possible to have a lock if the search criteria contain multiple unique index fields). SELECT * FROM child WHERE id = 100; If the ID has a unique index, the interval lock is not applied. If the ID does not have a unique index, the interval lock is applied. Conflicting locks can occur in the same interval. For example, within the same interval, transaction 1 can have a shared interval lock and transaction 2 can have an exclusive interval lock. The sole purpose of an interval lock is to prevent other transactions from inserting the interval. There is no difference between a shared interval lock and an exclusive interval lock. They do not conflict with each other and perform the same function. Explicit interval locking gaps can be specified by lowering the isolation level of the database to READ_COMMITTED. In these cases, interval locking is not used for search and index scanning operations, but only for foreign key constraint checking and duplicate key checking. When changing the isolation level to READ_COMMITTED, mysql evaluates WHERE query criteria and releases row locks that do not match rows.

####5, next-key Locks Next-key Locks specifies the interval lock before the row is added. If one transaction has an S lock or an X lock on row R, another transaction does not insert a new record before the index record on row R. Given that the index values contain 10, 11, 13, and 20, a next-key lock range may occur

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)
Copy the code

In REPEATABLE_READ level, innoDB uses next-key Lock in queries and index scans to prevent phantom reads

6. Insert Intention Locks

An insert intent lock is an interval lock that is triggered before the insert. This insert intent lock indicates that multiple transactions can insert the same interval without waiting, unless they insert the same row in the interval

7 the Locks, AUTO – INC

This is a special type of table lock that is triggered when a transaction inserts an increment column into the database.

Note:

This article is translated from InnoDB Locking, MYSQL official website