7.5.9.4 Locks Set by Different SQL Statements in InnoDB

• SELECT ... FROM ... : this is a consistent read, reading a snapshot of the database and setting no locks.
• SELECT ... FROM ... LOCK IN SHARE MODE : sets shared next-key locks on all index records the read encounters.
• SELECT ... FROM ... FOR UPDATE : sets exclusive next-key locks on all index records the read encounters.
• INSERT INTO ... VALUES (...) : sets an exclusive lock on the inserted row; note that this lock is not a next-key lock and does not prevent other users from inserting to the gap before the inserted row. If a duplicate key error occurs, sets a shared lock on the duplicate index record.
• INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive (non-next-key) lock on each row inserted into T. Does the search on S as a consistent read, but sets shared next-key locks on S if the MySQL logging is on. InnoDB has to set locks in the latter case because in roll-forward recovery from a backup every SQL statement has to be executed in exactly the same way as it was done originally.
• CREATE TABLE ... SELECT ... performs the SELECT as a consistent read or with shared locks, like in the previous item.
• REPLACE is done like an insert if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row which has to be updated.
• UPDATE ... SET ... WHERE ... : sets an exclusive next-key lock on every record the search encounters.
• DELETE FROM ... WHERE ... : sets an exclusive next-key lock on every record the search encounters.
• If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete which requires checking of the constraint condition sets shared record level locks on the records it looks at to check the constraint. Also in the case where the constraint fails, InnoDB sets these locks.
• LOCK TABLES ... : sets table locks. In the implementation the MySQL layer of code sets these locks. The automatic deadlock detection of InnoDB cannot detect deadlocks where such table locks are involved: see the following section. Also, since MySQL does know about row level locks, it is possible that you get a table lock on a table where another user currently has row level locks. But that does not put transaction integrity into danger. See section 7.5.14 Restrictions on InnoDB Tables.

User Comments

Add your own comment.