Mysql-innodb Lock Classification

Mysql innodb lock classification I. innodb row lock classification record lock: record lock, that is, only one row of gap lock is locked: interval lock, lock only one interval (note that the interval here is an open interval, that is, the boundary value is not included. Next-key lock: record lock + gap lock. Therefore, next-key lock is half-open and semi-closed, and the lower bound and upper bound are closed. The lock range of www.2cto.com next-key is: (negative infinity, minimum first record], (between records], (maximum record, positive infinity) II. Analysis of SELECT statement lock... FROM... for update sets an exclusive next-key lock FOR all index records encountered during read. Insert into... VALUES (...) sets an exclusive lock for the inserted row. Note that this is not a next-key lock and does not prevent other users from inserting the gap before the inserted rows. If a duplicate key error occurs, set the share lock for duplicate index records. · When you specify the AUTO_INCREMENT column before initialization for a table, InnoDB sets an exclusive lock at the end of the Index associated with the AUTO_INCREMENT column. In the Access AUTO-increment counter, InnoDB uses the dedicated table lock mode AUTO-INC, where the lock only continues until the end of the current SQL statement, rather than the end of the entire transaction. InnoDB retrieves the value of the previously initialized AUTO_INCREMENT column without setting any lock. Insert into t select... from s where... sets an exclusive (non-next-key) lock for each row inserted to T. It regards search as a continuous read on S, but if MySQL binary log function is enabled, it sets a shared next-key lock on S. InnoDB has to be locked in the latter case: In the Rollback Recovery from a backup, each SQL statement has to be executed in exactly the same way as it was initially executed. · Create table... SELECT... execute the SELECT statement as a continuous reading statement, or execute the statement with a share lock, as described in the previous entry. · If the unique key does not conflict, REPLACE is performed as an insert. In addition, set an exclusive nextkey lock for the rows that must be updated. · UPDATE... WHERE... sets an exclusive next-key lock for each record encountered in the search. · Delete from... WHERE... sets an exclusive next-key lock for each record encountered in the search. · If you define a foreign key constraint for a table, any insert, update, or delete record that needs to check the constraints sets a shared row-level lock for the record that looks at the check constraints. InnoDB also sets these locks when the constraints fail.