MySQL Transactions, Part II - Transaction Isolation Levels

August 17, 2004

Last month we started
looking at transactions
in MySQL, in particular with InnoDB tables. This month we look at the four
transaction isolation levels, again with InnoDB tables, and see how they affect
the usual locking transactional behavior.

Transaction Isolation Levels

A transaction isolation
level sets the default transactional behaviour. Our examples last month all
took the default setting. This month, we see how changing the transaction
isolation level leads to different results. As the name suggests, the setting
determines how isolated each transation is, or what kind of locks are
associated with queries inside a transaction. The four levels, in ascending
order of strictness, are:

READ
UNCOMMITTED: Barely transactional, this setting allows for so-called 'dirty
reads', where queries inside one transaction are affected by uncommitted
changes in another transaction.

READ
COMMITTED: Committed updates are visible within another transaction. This means
identical queries within a transaction can return differing results. This is
the default in some DBMS's.

REPEATABLE READ:
The default isolation level for InnoDB tables. Within a transaction, all reads
are consistent.

SERIALIZABLE:
Updates are not permitted in other transactions if a transaction has run an
ordinary SELECT query, i.e. queries are treated as if they had a LOCK IN SHARE
MODE, which we saw in action last month.

InnoDB
tables support all four SQL standard transaction isolation levels. Be careful
when converting code from other DBMS's, as they do not all support all four
levels, nor do they all default to the same level.

This is the essence of
the repeatable read. The SELECT query returns a consistent result within a
transaction, and new records added from another window during the transaction
are not immediately visible. For a result to be visible, both the updating transaction,
and any transactions that are already open, needs to be committed.