(mysql.info.gz) InnoDB transaction isolation

Info Catalog

(mysql.info.gz) InnoDB and AUTOCOMMIT

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB consistent read

 
 15.11.2 `InnoDB' and `TRANSACTION ISOLATION LEVEL'
 --------------------------------------------------
 
 In terms of the SQL:1992 transaction isolation levels, the `InnoDB'
 default is `REPEATABLE READ'.  Starting from MySQL 4.0.5, `InnoDB'
 offers all four different transaction isolation levels described by the
 SQL standard.  You can set the default isolation level for all
 connections by using the `--transaction-isolation' option on the
 command line or in option files.  For example, you can set the option
 in the `[mysqld]' section of `my.cnf' like this:
 
      [mysqld]
      transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                               | REPEATABLE-READ | SERIALIZABLE}
 
 A user can change the isolation level of a single session or all new
 incoming connections with the `SET TRANSACTION' statement. Its syntax
 is as follows:
 
      SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                             {READ UNCOMMITTED | READ COMMITTED
                              | REPEATABLE READ | SERIALIZABLE}
 
 Note that there are hyphens in the level names for the
 `--transaction-isolation' option, but not for the `SET TRANSACTION'
 statement.
 
 The default behavior is to set the isolation level for the next (not
 started) transaction. If you use the `GLOBAL' keyword, the statement
 sets the default transaction level globally for all new connections
 created from that point on (but not existing connections).  You need
 the `SUPER' privilege to do this.  Using the `SESSION' keyword sets the
 default transaction level for all future transactions performed on the
 current connection.
 
 Any client is free to change the session isolation level (even in the
 middle of a transaction), or the isolation level for the next
 transaction.
 
 Before MySQL 3.23.50, `SET TRANSACTION' had no effect on `InnoDB'
 tables. Before 4.0.5, only `REPEATABLE READ' and `SERIALIZABLE' were
 available.
 
 You can query the global and session transaction isolation levels with
 these statements:
 
      SELECT @@global.tx_isolation;
      SELECT @@tx_isolation;
 
 In row-level locking, `InnoDB' uses so-called "next-key locking."  That
 means that besides index records, `InnoDB' can also lock the "gap"
 before an index record to block insertions by other users immediately
 before the index record. A next-key lock refers to a lock that locks an
 index record and the gap before it.  A gap lock refers to a lock that
 only locks a gap before some index record.
 
 A detailed description of each isolation level in `InnoDB':
 
    * `READ UNCOMMITTED'
 
      `SELECT' statements are performed in a non-locking fashion, but a
      possible earlier version of a record might be used. Thus, using
      this isolation level, such reads are not "consistent." This is
      also called "dirty read."  Other than that, this isolation level
      works like `READ COMMITTED'.
 
    * `READ COMMITTED'
 
      A somewhat Oracle-like isolation level.  All `SELECT ... FOR
      UPDATE' and `SELECT ... LOCK IN SHARE MODE' statements lock only
      the index records, not the gaps before them, and thus allow free
      inserting of new records next to locked records.  `UPDATE' and
      `DELETE' statements that use a unique index with a unique search
      condition lock only the index record found, not the gap before it.
      In range-type `UPDATE' and `DELETE' statements, `InnoDB' must set
      next-key or gap locks and block insertions by other users to the
      gaps covered by the range. This is necessary because "phantom
      rows" must be blocked for MySQL replication and recovery to work.
 
      Consistent reads behave as in Oracle: Each consistent read, even
      within the same transaction, sets and reads its own fresh snapshot.
       InnoDB consistent read.
 
    * `REPEATABLE READ'
 
      This is the default isolation level of `InnoDB'.  `SELECT ... FOR
      UPDATE', `SELECT ... LOCK IN SHARE MODE', `UPDATE', and `DELETE'
      statements that use a unique index with a unique search condition
      lock only the index record found, not the gap before it.  With
      other search conditions, these operations employ next-key locking,
      locking the index range scanned with next-key or gap locks, and
      block new insertions by other users.
 
      In consistent reads, there is an important difference from the
      previous isolation level: In this level, all consistent reads
      within the same transaction read the same snapshot established by
      the first read. This convention means that if you issue several
      plain `SELECT' statements within the same transaction, these
      `SELECT' statements are consistent also with respect to each other.
       InnoDB consistent read.
 
    * `SERIALIZABLE'
 
      This level is like `REPEATABLE READ', but all plain `SELECT'
      statements are implicitly converted to `SELECT ... LOCK IN SHARE
      MODE'.
 
 

Info Catalog

(mysql.info.gz) InnoDB and AUTOCOMMIT

(mysql.info.gz) InnoDB transaction model

(mysql.info.gz) InnoDB consistent read