(mysql.info) innodb-lock-modes

(mysql.info) innodb-transaction-model
(mysql.info) innodb-and-autocommit
 
 14.2.10.1 `InnoDB' Lock Modes
 .............................
 
 `InnoDB' implements standard row-level locking where there are two
 types of locks:
 
    * A shared (S) lock allows a transaction to read a row (tuple).
 
    * An exclusive (X) lock allows a transaction to update or delete a
      row.
 
 If transaction `T1' holds a shared (S) lock on tuple `t', then
 
    * A request from some distinct transaction `T2' for an S lock on `t'
      can be granted immediately. As a result, both `T1' and `T2' hold
      an S lock on `t'.
 
    * A request from some distinct transaction `T2' for an X lock on `t'
      cannot be granted immediately.
 
 If a transaction `T1' holds an exclusive (X) lock on tuple `t', then a
 request from some distinct transaction `T2' for a lock of either type on
 `t' cannot be granted immediately. Instead, transaction `T2' has to
 wait for transaction `T1' to release its lock on tuple `t'.
 
 Additionally, `InnoDB' supports _multiple granularity locking_ which
 allows coexistence of record locks and locks on entire tables. To make
 locking at multiple granularity levels practical, additional types of
 locks called _intention locks_ are used. Intention locks are table
 locks in `InnoDB'. The idea behind intention locks is for a transaction
 to indicate which type of lock (shared or exclusive) it will require
 later for a row in that table. There are two types of intention locks
 used in `InnoDB' (assume that transaction `T' has requested a lock of
 the indicated type on table `R'):
 
    * Intention shared (IS): Transaction `T' intends to set S locks on
      individual rows in table `R'.
 
    * Intention exclusive (IX): Transaction `T' intends to set X locks
      on those rows.
 
 The intention locking protocol is as follows:
 
    * Before a given transaction can acquire an S lock on a given row,
      it must first acquire an IS or stronger lock on the table
      containing that row.
 
    * Before a given transaction can acquire an X lock on a given row,
      it must first acquire an IX lock on the table containing that row.
 
 These rules can be conveniently summarized by means of a _lock type
 compatibility matrix_:
 
                X              IX             S              IS
 X              Conflict       Conflict       Conflict       Conflict
 IX             Conflict       Compatible     Conflict       Compatible
 S              Conflict       Conflict       Compatible     Compatible
 IS             Conflict       Compatible     Compatible     Compatible
 
 A lock is granted to a requesting transaction if it is compatible with
 existing locks. A lock is not granted to a requesting transaction if it
 conflicts with existing locks. A transaction waits until the
 conflicting existing lock is released. If a lock request conflicts with
 an existing lock and cannot be granted because it would cause deadlock,
 an error occurs.
 
 Thus, intention locks do not block anything except full table requests
 (for example, `LOCK TABLES ...  WRITE'). The main purpose of IX and IS
 locks is to show that someone is locking a row, or going to lock a row
 in the table.
 
 The following example illustrates how an error can occur when a lock
 request would cause a deadlock. The example involves two clients, A and
 B.
 
 First, client A creates a table containing one row, and then begins a
 transaction. Within the transaction, A obtains an S lock on the row by
 selecting it in share mode:
 
      mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
      Query OK, 0 rows affected (1.07 sec)
 
      mysql> INSERT INTO t (i) VALUES(1);
      Query OK, 1 row affected (0.09 sec)
 
      mysql> START TRANSACTION;
      Query OK, 0 rows affected (0.00 sec)
 
      mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.10 sec)
 
 Next, client B begins a transaction and attempts to delete the row from
 the table:
 
      mysql> START TRANSACTION;
      Query OK, 0 rows affected (0.00 sec)
 
      mysql> DELETE FROM t WHERE i = 1;
 
 The delete operation requires an X lock. The lock cannot be granted
 because it is incompatible with the S lock that client A holds, so the
 request goes on the queue of lock requests for the row and client B
 blocks.
 
 Finally, client A also attempts to delete the row from the table:
 
      mysql> DELETE FROM t WHERE i = 1;
      ERROR 1213 (40001): Deadlock found when trying to get lock;
      try restarting transaction
 
 Deadlock occurs here because client A needs an X lock to delete the
 row. However, that lock request cannot be granted because client B is
 already has a request for an X lock and is waiting for client A to
 release its S lock. Nor can the S lock held by A be upgraded to an X
 lock because of the prior request by B for an X lock. As a result,
 `InnoDB' generates an error for client A and releases its locks. At
 that point, the lock request for client B can be granted and B deletes
 the row from the table.
Info Catalog
(mysql.info) innodb-transaction-model
(mysql.info) innodb-and-autocommit
automatically generated byinfo2html