Mysql-InnoDB Lock Classification

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 the specified auto_increment column is initialized on 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 the MySQL binary log function is enabled, it sets a shared next-key lock for 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 read or with a shared 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 constraint is set to share row-Level Lock. InnoDB also sets these locks when the constraints fail.