The InnoDB Transaction Model

Transactional database management systems are generally striving for the same goals, using differing approaches. To isolate transactions, InnoDB uses a fine-grained, row-level locking mechanism. This means that different transactions can run on the same table at the same time as long as they are all only reading or do not use the same rows if they are writing.

Uncommitted changes lock other threads out of only affected rows, not a whole table. This is one of the features that gives InnoDB high performance while delivering the kinds of features you expect from a modern RDBMS. One of these features, or sets of features, is ACID compliance.

ACID Compliance

An important database term that we have not yet defined is the acronym ACID. ACID stands for Atomicity, Consistency, Isolation, and Durability. Much used to be made of the fact that MySQL using MyISAM tables did not pass the "ACID Test." Using InnoDB tables, MySQL is ACID compliant.

Atomicity means that transactions are atomic and indivisible. Either all of a transaction's changes are stored in the database, or none of them are stored. In the event of an external error, it is obviously ideal if the recovery process can complete any transactions that were in progress at the time; however, it is also acceptable for those transactions to be completely rolled back.

Consistency means that operations transform the database from one valid state to another. There are no intermediate stages where the data is inconsistent. The database should also disallow operations that violate consistency constraints. If you are storing bank accounts that relate to bank customers, it should not be possible to create an account for a customer who does not exist, and it should not be possible to delete a customer from the customers table if there are still accounts referring to them in the accounts table.

Isolation means that transactions do not affect each other while they are running. Each transaction should be able to view the world as though it is the only one reading and altering things. In practice this is not usually the case, but locks are used to achieve the illusion. Depending on the database and option settings, you will have different levels of isolation in practice. (See the "Transaction Isolation" section in this chapter for more detail.)

Durability means that after a transaction has been committed to the database, its effects are permanent. This would be a fairly simple requirement to satisfy in a simple program, but in a complex RDBMS that uses locking and multiversioning to allow concurrent multiuser access and caching to improve performance, it is a minefield. In addition, durability implies that we should be able to recover the current state of the database in the event of a failure. If a power failure, hard-disk crash, or other catastrophe occurs between a client sending a transaction to the database and that transaction being recorded on disk, then we should be able to combine a backup and a log to bring the database back to its precrash state and perhaps process transactions that had been logged but not yet executed or committed.

If you are using InnoDB tables (or BerkeleyDB tables), MySQL is ACID compliant. Using the transaction syntax gives you atomicity. Transactions and foreign key constraints give you consistency. You can choose the level of isolation that transactions have from one another. The binary log and repair tools provide durability. (Using replication, you can have a highly durable system without any single point of failure.)

Transaction Isolation

InnoDB tables can run in four different transaction isolation levels. In order from strongest to weakest, they are

• Serializable

• Repeatable read

• Read committed

• Read uncommitted

As with many options, you have a trade-off between robustness and performance.

Serializable isolation is the ideal from a purity and robustness angle. With serializable isolation, reads and writes on the database should appear to be happening in a sequence, with changes from a write being completely recorded before the next read starts. Transactions will not always have to be performed in a noninterleaved sequence to achieve this appearance because many do not interfere with each other, but in cases in which there are clashes , they will. This locking and waiting, combined with the overhead of predicting which combinations of transactions will interfere, makes serializable isolation the slowest isolation mode. If you want to use this mode, this is the command to run:

 set transaction isolation level serializable; 

The default level for InnoDB is repeatable read . In this isolation mode, each transaction gets to work in an isolated version of the table where each row remains as it was when the transaction started. Reading a row is guaranteed to be repeatable. If you call

 select * from account where number=1; 

at the start of the transaction and perform the same query later in the transaction, you will get the same results both times. You can, however, get what are called phantom reads . It is possible that another transaction which commits before yours is adding new rows to the table. If you perform the same query with a condition twice, such as

 select * from account where balance>1000; 

it is possible that you will get new rows ”phantom rows ”the second time.

In practice you should very rarely see phantom reads from MySQL. InnoDB uses an algorithm called next key locking to solve the problem, as long as the column that your condition applies to is indexed. You probably already know that InnoDB has row-level locking. When a transaction uses a row, it locks that row so that the transaction can be isolated from others. As well as locking the rows used, next key locking also locks the gaps between rows found in the index. Because phantom reads are addressed in this way, few systems really need to be put in serialized isolation mode.

If you set the server to read committed , your transactions are no longer very isolated. If you perform a query and repeat it later in the same transaction, you will get different results the second time if another transaction has modified the data in the meantime and committed. Should you want to do this, the command is

 set transaction isolation level read committed; 

At the weakest isolation level, read uncommitted , it is distinctly arguable not only that your transactions are no longer isolated, consistent, and therefore ACID compliant, but that you no longer really have transactions. In this mode, it is possible for transactions to read changes that other transactions have made before the changes have been committed. This is called a dirty read . You would tolerate this only in fairly unusual circumstances, such as at a time when you know all active threads will be reading or writing, but not both. To enable read uncommitted mode, use this:

 set transaction isolation level read uncommitted; 

Table 10.1 summarizes the characteristics of each mode.

Table 10.1. Transaction Isolation Level Characteristics

	Dirty Read	Nonrepeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Not possible	Possible	Possible
Repeatable Read	Not possible	Not possible	Possible (but unlikely )
Serializable	Not possible	Not possible	Not possible