Mysql InnoDB Lock Classification Introduction _mysql

First, InnoDB row lock classification

Record lock: Records locks, which is just a single line locked
Gap Lock: An interval lock that only locks an interval (note that the interval here is open, i.e. excluding boundary values.)
Next-key Lock:record Lock+gap Lock, so the Next-key lock is ajar and half open, and the upper bound is closed. Www.jb51.net
Next-key Locking Range: (negative infinity, minimum first record), (Between Records), (Max record, positive infinity)

II. Analysis of statement locking

SELECT ... From ... For update sets an exclusive Next-key lock on all index records that are encountered by a read.
INSERT into ... VALUES (...) Sets an exclusive lock on 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 row. If a duplicate key error occurs, set the shared lock on the duplicate index record.
· 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-growth counter, InnoDB uses a dedicated table lock mode Auto-inc, where the lock lasts only until the end of the current SQL statement, not the end of the entire transaction. InnoDB retrieves the value of the previously initialized auto_increment column without setting any locks.

INSERT into T SELECT ... From S WHERE ... Sets the exclusive (non-next-key) lock on each row inserted into T. It treats the search as a continuous read on S, but if the MySQL binary log function is turned on, it sets a shared next-key lock on S
Set InnoDB has to set a lock in the latter case: Every SQL statement has to be executed exactly the same way it was originally executed in the rollback recovery from a backup.

· CREATE TABLE ... SELECT ... The select is executed as a continuous read, or with a shared lock, as described in the previous entry.
· If the unique key does not conflict, replace is made like an insert. In addition, set an exclusive Nextkey lock on the rows that must be updated.
· UPDATE ... WHERE ... Set an exclusive Next-key lock on each record that the search encounters.
· DELETE from ... WHERE ... Set an exclusive Next-key lock on each record that the search encounters.
· If you define a FOREIGN key constraint on a table, any inserts that need to check the constraint, update or delete the shared row-level lock on the record that it looks at the check constraint. InnoDB also sets these locks if the constraint fails.