MySQL InnoDB transaction related records

One 、 Transaction isolation level

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels

Two 、InnoDB Different from SQL Statement to set the lock

MySQL :: MySQL 8.0 Reference Manual :: 15.7.3 Locks Set by Different SQL Statements in InnoDB

summary ：

1、 SELECT ... FROM Is a consistent read , Read the snapshot of the database without setting the lock , Unless the transaction isolation level is set to SERIALIZABLE. about SERIALIZABLE Level , Search sets the shared next key lock on the index record it encounters . however , For statements that lock rows with unique indexes to search for unique rows , Only one index record lock is needed .

2、 For locked reads （SELECTwithFOR UPDATE or FOR SHARE）、 UPDATE and DELETE sentence , The lock used depends on whether the statement uses a unique index with a unique search condition or a range type search condition .

  2.1 For a unique index with unique search criteria , InnoDB Lock only the index records found , Without locking the gap before it . That is, the line lock .

 2.2 For other search criteria and non unique indexes , InnoDB Lock the index range of the scan , Use clearance lock Or the next key lock  

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

What is clearance lock ？
  A gap lock is a lock on a gap between index records .

The function of clearance lock
  Ensure that the data in a certain gap will not change in case of locking . For example, I mysql Repeatable read at the default isolation level （RR）.

When a statement that uses a unique index to search for a unique row , No gap lock required . As the following statement id Column has a unique index , At this time, only id The value is 10 Use record locks for rows .

select * from t where id = 10 for update;//  Be careful ： If it is a normal query, it is a snapshot read , No need to lock , added for update It's not an ordinary query 

If , In the above statement id When the column is not indexed or non unique , The statement will generate a gap lock .

If , There are multiple query criteria in the search criteria ( Even if each column has a unique index ), There will also be clearance locks .

    It should be noted that , When id When there is no index on the column ,SQL The full table scan of the clustered index will be used for filtering , Because filtering is in MySQL Server At the level of . So every record （ Whether the conditions are met or not ） Will be added X lock . however , For efficiency ,MySQL Optimized , For records that do not meet the conditions , Will put the lock after judgment , In the end , It's the lock on the record that meets the condition . But lock on records that do not meet the conditions / The lock release action will not be omitted . So when there is no index , Data rows that do not meet the conditions will have a time-consuming process of locking and releasing locks .