(mysql.info) innodb-locks-set

Info Catalog

(mysql.info) innodb-consistent-read-example

(mysql.info) innodb-transaction-model

(mysql.info) innodb-implicit-command-or-rollback

 
 14.2.10.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.
 
 `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.
 
    * `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.
 
    * 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  innodb-and-autocommit.
 
      `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'. `InnoDB' sets
      shared next-key locks locks on `S', unless
      `innodb_locks_unsafe_for_binlog' is enabled, in which case it does
      the search on `S' as a consistent read.  `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.
 
    * `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.
 
    * `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.
 
    * 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. `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 
      innodb-deadlock-detection.  See also  innodb-restrictions.
 

Info Catalog

(mysql.info) innodb-consistent-read-example

(mysql.info) innodb-transaction-model

(mysql.info) innodb-implicit-command-or-rollback