Mysql-innodb lock classification

Recordlock: Record lock, that is, only a single row of gap lock is locked. Only one interval is locked. (note that the interval here is an open interval, that is, the boundary value is not included. Next-keylock: recordlock + gaplock, so next-keylock is half-open and half-closed, and the lower bound is open and the upper bound is closed.

I. innodb row lock classification
Record lock: record lock, that is, only a single row is locked.
Gap lock: interval lock. Only one interval is locked. (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.
Lock range of next-key: (negative infinity, minimum first record], (between records], (maximum record, positive infinity)

II. statement lock analysis

SELECT... 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.
Yes. 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.