MySQL Transactions, Part II – Transaction Isolation Levels

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.

  • SQL SERVER –
    READ COMMITTED

  • Oracle – READ
    COMMITTED (supports only READ COMMITTED, SERIALIZABLE and the non-standard READ
    ONLY)

  • DB2 –
    REPEATABLE READ (supports REPEATABLE READ, UNCOMMITTED READ and 2 non-standard
    levels)

  • PostgreSQL –
    REPEATABLE READ (only supports REPEATABLE READ and SERIALIZABLE)

For those who have
skipped Part 1, we are using the following table to test with:


mysql> DESC t;
+——-+———+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———+——+—–+———+——-+
| f | int(11) | YES | | NULL | |
+——-+———+——+—–+———+——-+
1 row in set (0.05 sec)

You can
create and populate it as follows:


mysql> CREATE TABLE t (f INT) TYPE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t values (1),(2),(3),(4),(55);
Query OK, 5 rows affected (0.00 sec)

If you have not made a
specific change to the transaction isolation level, it will be a repeatable
read. You can check this as follows:


mysql> SELECT @@tx_isolation;
+—————–+
| @@tx_isolation |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)

Repeatable Reads

In this exercise, we
begin a transaction, and see if a committed insert from another transaction is
visible in the midst of the transaction.

Connection 1


mysql> BEGIN;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM t;
+——+
| f |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
| 55 |
+——+
5 rows in set (0.00 sec)

Connection 2


mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES(6);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;
+——+
| f |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
| 55 |
| 6 |
+——+
6 rows in set (0.00 sec)

Remember that it does not
matter to the second connection that the SELECT statement was run after the
COMMIT. Within the transaction, the new record is immediately visible.

Connection 1:


mysql> SELECT * FROM t;
+——+
| f |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
| 55 |
+——+
5 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t;
+——+
| f |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
| 55 |
| 6 |
+——+
6 rows in set (0.00 sec)

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.

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles