|< Day Day Up >|
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:
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:
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.
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.
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 >|