(mysql.info.gz) InnoDB Locks set

Info Catalog

(mysql.info.gz) InnoDB Consistent read example

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB implicit command or rollback

 
 15.11.7 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
 query. It does not matter if there are `WHERE' conditions in the query
 that would exclude the row from the result set of the query. `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, then `InnoDB' always retrieves
 also the clustered index record and sets a lock on it.
 
 If you do not have indexes suitable for your query and MySQL has to
 scan the whole table to process the query, every row of the table will
 become 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.
 
    * `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, instead of to the end of the whole transaction.  
      `InnoDB' and `AUTOCOMMIT' 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.
 
    * `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 checking of the constraint
      condition sets shared record-level locks on the records 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' and `AUTOCOMMIT=0', and the MySQL layer
      above `InnoDB' knows about row-level locks.  Before that, the
      automatic deadlock detection of `InnoDB' cannot detect deadlocks
      where such table locks are involved.  Also, since 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. But that does not put transaction integrity in danger.
       InnoDB Deadlock detection.   InnoDB restrictions.
 

Info Catalog

(mysql.info.gz) InnoDB Consistent read example

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB implicit command or rollback