Introduction to the lock classification of mysql and innodb
- 2020-05-14 05:06:42
- OfStack
1. innodb row lock classification
record lock: record lock, which locks only a single row
gap lock: interval lock, which locks only one interval (note that the intervals here are all open intervals, that is, do not include boundary values).
next-key lock: record lock+gap lock, so next-key lock is also half open and half closed, with the lower bound open and the upper bound closed. www. ofstack. com
next-key lock range :(minus infinity, min 1st record],(between records],(Max record, plus infinity)
2. Statement locking analysis
SELECT... FROM... FOR UPDATE sets an exclusive next-key lock on all index records encountered by the read.
INSERT INTO... VALUES (...). Set an exclusive lock on the inserted row. Note that this is not an next-key lock and does not prevent other users from inserting rows in the gap before they have been inserted. If a duplicate key error occurs, a Shared lock is set for duplicate index records.
· when the previously 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 automatic growth counter, InnoDB USES the dedicated table locking mode AUTO-INC, where the locking lasts only until the end of the current SQL statement, not to the end of the entire transaction. InnoDB retrieves the value of the previously initialized AUTO_INCREMENT column without setting any locking.
INSERT INTO T SELECT... FROM S WHERE... Set an exclusive (not next-key) lock for each row inserted into T. It treats search as a continuous read on S, but if MySQL2 logging is enabled, it sets a Shared next-key lock on S
. In the latter case, InnoDB has to be locked: in a rollforward recovery from a backup, each SQL statement has to be executed in exactly the same way it was originally executed.
・ CREATE TABLE... SELECT... Execute SELECT as a continuous read, or with Shared locking, as described in the previous entry.
· if there is no conflict with only 1 key, REPLACE is done as one insert 1. Also, set an exclusive nextkey lock on the rows that must be updated.
・ UPDATE... WHERE... Set an exclusive next-key lock for each record encountered in the search.
・ DELETE FROM... WHERE... Set an exclusive next-key lock for each record encountered in the search.
· if an FOREIGN KEY constraint is defined on a table, set Shared row-level locking for any record that needs to check the insertion of the constraint, update or remove the record that looks at the check constraint on it. InnoDB also sets these locks in case the constraint fails.
record lock: record lock, which locks only a single row
gap lock: interval lock, which locks only one interval (note that the intervals here are all open intervals, that is, do not include boundary values).
next-key lock: record lock+gap lock, so next-key lock is also half open and half closed, with the lower bound open and the upper bound closed. www. ofstack. com
next-key lock range :(minus infinity, min 1st record],(between records],(Max record, plus infinity)
2. Statement locking analysis
SELECT... FROM... FOR UPDATE sets an exclusive next-key lock on all index records encountered by the read.
INSERT INTO... VALUES (...). Set an exclusive lock on the inserted row. Note that this is not an next-key lock and does not prevent other users from inserting rows in the gap before they have been inserted. If a duplicate key error occurs, a Shared lock is set for duplicate index records.
· when the previously 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 automatic growth counter, InnoDB USES the dedicated table locking mode AUTO-INC, where the locking lasts only until the end of the current SQL statement, not to the end of the entire transaction. InnoDB retrieves the value of the previously initialized AUTO_INCREMENT column without setting any locking.
INSERT INTO T SELECT... FROM S WHERE... Set an exclusive (not next-key) lock for each row inserted into T. It treats search as a continuous read on S, but if MySQL2 logging is enabled, it sets a Shared next-key lock on S
. In the latter case, InnoDB has to be locked: in a rollforward recovery from a backup, each SQL statement has to be executed in exactly the same way it was originally executed.
・ CREATE TABLE... SELECT... Execute SELECT as a continuous read, or with Shared locking, as described in the previous entry.
· if there is no conflict with only 1 key, REPLACE is done as one insert 1. Also, set an exclusive nextkey lock on the rows that must be updated.
・ UPDATE... WHERE... Set an exclusive next-key lock for each record encountered in the search.
・ DELETE FROM... WHERE... Set an exclusive next-key lock for each record encountered in the search.
· if an FOREIGN KEY constraint is defined on a table, set Shared row-level locking for any record that needs to check the insertion of the constraint, update or remove the record that looks at the check constraint on it. InnoDB also sets these locks in case the constraint fails.