GNU Info

(mysql.info)InnoDB Locks set

---

Next: InnoDB Deadlock detection Prev: InnoDB Next-key locking Up: InnoDB transaction model

---

Enter node , (file) or (file)node

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.

   * `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 next section below. See also section 13
     'InnoDB restrictions' about the following: 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 integerity into danger.