15.2 InnoDB Transaction Support

The InnoDB storage engine provides transactional capabilities. A transaction is a logical grouping of statements that is handled by the database server as a single unit. Either all the statements execute successfully to completion or all modifications made by the statements are discarded if an error occurs. Transactional systems often are described as being ACID compliant, where "ACID" stands for the following properties:

Atomic. All the statements execute successfully or are canceled as a unit.
Consistent. A database that is in a consistent state when a transaction begins is left in a consistent state by the transaction.
Isolated. One transaction does not affect another.
Durable. All the changes made by a transaction that completes successfully are recorded properly in the database. Changes are not lost.

InnoDB satisfies the conditions for ACID compliance.

15.2.1 Performing Transactions

Multiple clients may execute transactions concurrently, but any given client performs transactions serially, one after the other. The client determines when each of its transactions begins and ends by controlling its autocommit mode. MySQL initializes each client to begin with autocommit mode enabled. This causes each statement to be committed immediately. In transactional terms, this means that each statement is a separate transaction. To group together multiple statements as a single transaction so that they succeed or fail as a unit, autocommit mode must be disabled. There are two ways to do this:

The first method is to disable autocommit mode explicitly:
```
SET AUTOCOMMIT = 0;
```
With autocommit disabled, any following statements become part of the current transaction until you end it by issuing a COMMIT statement to accept the transaction and commit its effects to the database, or a ROLLBACK statement to discard the transaction's effects.
When you disable autocommit explicitly, it remains disabled until you enable it again as follows:
```
SET AUTOCOMMIT = 1;
```
The second method is to suspend the current autocommit mode by beginning a transaction explicitly. Any of the following statements begins a transaction:
```
BEGIN;

BEGIN WORK;

START TRANSACTION;
```
After beginning a transaction with one of those three statements, autocommit remains disabled until you end the transaction by committing it or by rolling it back. The autocommit mode then reverts to the value it had prior to the start of the transaction.

If you disable autocommit explicitly, you perform transactions like this:


SET AUTOCOMMIT = 0;

... statements for transaction 1 ...

COMMIT;

... statements for transaction 2 ...

COMMIT;

...

If you suspend autocommit by using BEGIN, you perform transactions like this:


BEGIN;

... statements for transaction 1 ...

COMMIT;

BEGIN;

... statements for transaction 2 ...

COMMIT;

...

While autocommit mode is enabled, attempts to perform multiple-statement transactions are ineffective. Each statement is committed immediately, so COMMIT is superfluous and ROLLBACK has no effect.

Under some circumstances, the current transaction may end implicitly:

If you issue any of the following statements, InnoDB implicitly commits the preceding uncommitted statements of the current transaction and begins a new transaction:
```
ALTER TABLE

BEGIN

CREATE INDEX

DROP DATABASE

DROP INDEX

DROP TABLE

RENAME TABLE

TRUNCATE TABLE

LOCK TABLES

UNLOCK TABLES

SET AUTOCOMMIT = 1

START TRANSACTION
```
Before MySQL 4.0.13, CREATE TABLE also causes an implicit commit if the binary update log is enabled.
If a client connection closes while the client has a transaction pending, InnoDB rolls back the transaction implicitly. This occurs regardless of whether the connection closes normally or abnormally.

Currently, the server always initializes each client connection to begin with autocommit enabled. Modifications to the autocommit mode made by a client to its connection persist only to the end of the connection. If a client disconnects and reconnects, the second connection begins with autocommit enabled, regardless of its setting at the end of the first connection.

15.2.2 Transaction Isolation Levels

As mentioned earlier, multiple transactions may be executing concurrently within the server, one transaction per client. This has the potential to cause problems: If one client's transaction changes data, should transactions for other clients see those changes or should they be isolated from them? The transaction isolation level determines level of visibility between transactions—that is, the ways in which simultaneous transactions interact when accessing the same data. This section discusses the problems that can occur and how InnoDB implements isolation levels. Note that isolation level definitions vary among database servers, so the levels as implemented by InnoDB might not correspond exactly to levels as implemented in other database systems.

When multiple clients run transactions concurrently, three problems that may result are dirty reads, nonrepeatable reads, and phantoms. These occur under the following circumstances:

A dirty read is a read by one transaction of uncommitted changes made by another. Suppose that transaction T1 modifies a row. If transaction T2 reads the row and sees the modification even though T1 has not committed it, that is a dirty read. One reason this is a problem is that if T1 rolls back, the change is undone but T2 does not know that.
A nonrepeatable read occurs when a transaction performs the same retrieval twice but gets a different result each time. Suppose that T1 reads some rows and that T2 then changes some of those rows and commits the changes. If T1 sees the changes when it reads the rows again, it gets a different result; the initial read is nonrepeatable. This is a problem because T1 does not get a consistent result from the same query.
A phantom is a row that appears where it was not visible before. Suppose that T1 and T2 begin, and T1 reads some rows. If T2 inserts a new row and T1 sees that row when it reads again, the row is a phantom.

InnoDB implements four isolation levels that control the visibility of changes made by one transaction to other concurrently executing transactions:

READ UNCOMMITTED allows a transaction to see uncommitted changes made by other transactions. This isolation level allows dirty reads, nonrepeatable reads, and phantoms to occur.
READ COMMITTED allows a transaction to see changes made by other transactions only if they've been committed. Uncommitted changes remain invisible. This isolation level allows nonrepeatable reads and phantoms to occur.
REPEATABLE READ ensures that if a transaction issues the same SELECT twice, it gets the same result both times, regardless of committed or uncommitted changes made by other transactions. In other words, it gets a consistent result from different executions of the same query. In some database systems, REPEATABLE READ isolation level allows phantoms, such that if another transaction inserts new rows in the interval between the SELECT statements, the second SELECT will see them. This is not true for InnoDB; phantoms do not occur for the REPEATABLE READ level.
SERIALIZABLE completely isolates the effects of one transaction from others. It is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes.

The essential difference between REPEATABLE READ and SERIALIZABLE is that with REPEATABLE READ, one transaction cannot modify rows another has modified, whereas with SERIALIZABLE, one transaction cannot modify rows if another has merely even read them.

Isolation levels are relevant only within the context of simultaneously executing transactions. After a given transaction has committed, its changes become visible to any transaction that begins after that.

InnoDB operates by default in REPEATABLE READ mode: Each transaction sees a view of the database that consists of all changes that have been committed by the time the transaction issues its first consistent read (such as a SELECT statement), plus any changes that it makes itself. It does not see any uncommitted changes, or committed changes made by transactions that begin later than itself.

InnoDB makes transaction isolation possible by multi-versioning. As transactions modify rows, InnoDB maintains isolation between them by maintaining multiple versions of the rows, and makes available to each transaction the appropriate version of the rows that it should see. Multiple versions of a row that has been changed can be derived from the current version of the row, plus the undo logs.

With multi-versioning, each transaction sees a view of the contents of the database that is appropriate for its isolation level. For example, with a level of REPEATABLE READ, the snapshot of the database that a transaction sees is the state of the database at its first read. One property of this isolation level is that it provides consistent reads: A given SELECT yields the same results when issued at different times during a transaction. The only changes the transaction sees are those it makes itself, not those made by other transactions. For READ COMMITTED, on the other hand, the behavior is slightly different. The view of the database that the transaction sees is updated at each read to take account of commits that have been made by other transactions since the previous read.

15.2.3 Setting the Isolation Level

To set the server's default transaction isolation level at startup time, use the --transaction-isolation option. The option value should be READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to put the server in READ COMMITTED mode by default, put these lines in an option file:


[mysqld]

transaction-isolation = READ-COMMITTED

The isolation level may also be set dynamically for a running server with the SET TRANSACTION ISOLATION LEVEL statement. The statement has three forms:


SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level;

SET SESSION TRANSACTION ISOLATION LEVEL isolation_level;

SET TRANSACTION ISOLATION LEVEL isolation_level;

The value of isolation_level should be READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, or SERIALIZABLE. The first form of the statement sets the server's global isolation level. It applies to all new client connections established from that point on. Existing connections are unaffected. The second form sets the isolation level for the current client connection only and applies to transactions the client performs from that point on. The third form sets the isolation level only for the current client's next transaction.

Only clients that have the SUPER privilege may use the first form of the statement. Any client may use the second and third forms of the statement; they affect only its own transactions, so no special privilege is required.

< Day Day Up >