Skip navigation links

Additional languages
13.2.11.9 Implicit Transaction Commit and Rollback »
Section Navigation      [Toggle]

13.2.11.8. Locks Set by Different SQL Statements in InnoDB

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter if there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The record locks are normally next-key locks that also block inserts to the “gap” immediately before the record.

If the locks to be set are exclusive, InnoDB always retrieves also the clustered index record and sets a lock on it.

If you do not have indexes suitable for your statement and MySQL has to scan the whole table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily need to scan many rows.

For SELECT ... FOR UPDATE or SELECT ... IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION, scanned (and locked) rows from a table might be inserted into a temporary table before evaluation whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.

InnoDB sets specific types of locks as follows:

  • SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, this sets shared next-key locks on the index records it encounters.

  • 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.

  • UPDATE ... 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.

  • 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, a shared lock on the duplicate index record is set.

  • 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 that has to be updated.

  • While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDB uses a specific table lock mode AUTO-INC where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Note that other clients cannot insert into the table while the AUTO-INC table lock is held; see Section 13.2.11.2, “InnoDB and AUTOCOMMIT”.

    Before MySQL 3.23.50, SHOW TABLE STATUS applied to a table with an AUTO_INCREMENT column sets an exclusive row-level lock to the high end of the AUTO_INCREMENT index. This means also that SHOW TABLE STATUS could cause a deadlock of transactions, something that may surprise users. Starting from MySQL 3.23.50, InnoDB fetches the value of a previously initialized AUTO_INCREMENT column without setting any locks.

  • INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive (non-next-key) lock on each row inserted into T. It does the search on S as a consistent read, but sets shared next-key locks on S if MySQL binary logging is turned on. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement has to be executed in exactly the same way it was done originally.

  • CREATE TABLE ... SELECT ... performs the SELECT as a consistent read or with shared locks, as in the previous item.

  • If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

  • LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks. Beginning with MySQL 4.0.20 and 4.1.2, InnoDB is aware of table locks if innodb_table_locks=1 (the default) and AUTOCOMMIT=0, and the MySQL layer above InnoDB knows about row-level locks. Otherwise, InnoDB's automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another user currently has row-level locks. However, this does not endanger transaction integrity, as discussed in Section 13.2.11.10, “Deadlock Detection and Rollback”. See also Section 13.2.17, “Restrictions on InnoDB Tables”.