7.5.9 InnoDB Transaction Model

In the InnoDB transaction model the goal has been to combine the best properties of a multi-versioning database to traditional two-phase locking. InnoDB does locking on row level and runs queries by default as non-locking consistent reads, in the style of Oracle. The lock table in InnoDB is stored so space-efficiently that lock escalation is not needed: typically several users are allowed to lock every row in the database, or any random subset of the rows, without InnoDB running out of memory.

In InnoDB all user activity happens inside transactions. If the autocommit mode is used in MySQL, then each SQL statement forms a single transaction. MySQL always starts a new connection with the autocommit mode switched on.

If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can think that a user always has a transaction open. If he issues the SQL COMMIT or ROLLBACK statement, it ends the current transaction, and a new one starts. Both statements will release all InnoDB locks that were set during the current transaction. A COMMIT means that the changes made in the current transaction are made permanent and become visible to other users. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction.

If the connection has AUTOCOMMIT = 1, then the user can still perform a multi-statement transaction by starting it with START TRANSACTION or BEGIN and ending it with COMMIT or ROLLBACK.

In terms of the SQL-92 transaction isolation levels, the InnoDB default is REPEATABLE READ. Starting from version 4.0.5, InnoDB offers all 4 different transaction isolation levels described by the SQL-92 standard. You can set the default isolation level for all connections in the [mysqld] section of `my.cnf':

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 [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

SQL statement. Note that there are no hyphens in level names in the SQL syntax. If you specify the keyword GLOBAL in the above statement, it will determine the initial isolation level of new incoming connections, but will not change the isolation level of old connections. Any user is free to change the isolation level of his session, even in the middle of a transaction. In versions earlier than 3.23.50, SET TRANSACTION had no effect on InnoDB tables. In versions < 4.0.5 only REPEATABLE READ and SERIALIZABLE were available.

You can query the global and session transaction isolation levels with:

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 means a lock which locks an index record and the gap before it. A gap lock means a lock which only locks a gap before some index record.

A detailed description of each isolation level in InnoDB:

• READ UNCOMMITTED This is also called ``dirty read'': non-locking SELECTs are performed so that we do not look at a possible earlier version of a record; thus they are not 'consistent' reads under this isolation level; otherwise this level works like READ COMMITTED.
• READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, not the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. But still in range type UPDATE and DELETE, InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary since ``phantom rows'' have to 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.
• REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. Otherwise 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 SELECTs within the same transaction, these SELECTs are consistent also with respect to each other.
• SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE.

Subsections

• 7.5.9.1 Consistent Read
• 7.5.9.2 Locking Reads
• 7.5.9.3 Next-key Locking: Avoiding the Phantom Problem
• 7.5.9.4 Locks Set by Different SQL Statements in InnoDB
• 7.5.9.5 Deadlock Detection and Rollback
• 7.5.9.6 An Example of How the Consistent Read Works in InnoDB
• 7.5.9.7 How to cope with deadlocks?
• 7.5.9.8 Performance Tuning Tips
• 7.5.9.9 The InnoDB Monitor

User Comments

Add your own comment.