MySQL Transactions, Part II - Transaction Isolation Levels

August 17, 2004

MySQL Transactions, Part II - Transaction Isolation Levels

By Ian Gilfillan

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.

Uncommitted Reads

Let's see what happens with the READ UNCOMMITTED transaction isolation level. To change this, you will need to have the SUPER privilege.

mysql> SET GLOBAL TRANSACTION ISOLATION 
  LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

Use two new connections for the exercise below, as the new transaction isolation level takes effect only for new connections made after the command is run.

Connection 1:

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

Connection 2:

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

mysql> INSERT INTO t VALUES (7),(8);
Query OK, 1 row affected (0.06 sec)

Connection 1:

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

This is known as a dirty read - the new records have not even been committed by the second transaction, yet they are still visible to the first transaction.

Connection 2:

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

Connection 1:

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

There are dangers with this level of isolation and it bends the rules of transactions. You would only want to use this where you really do not care about the consistency of your results, but do care about potential locks impacting performance.

Committed Reads

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

Connection 1:

mysql>BEGIN;
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)

Connection 2:

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

mysql> INSERT INTO t VALUES (7),(8);
Query OK, 1 row affected (0.05 sec)

Connection 1:

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

Connection 2:

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

Connection 1:

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

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

The important difference is that an uncommitted INSERT did not have any impact on the transaction in Connection 1. Once the second connection's transaction was committed, the result was visible in the first transaction. It is also important to distinguish the difference between this and the default repeatable read transaction isolation level we saw at the beginning. With READ COMMITTED, changes are visible when other transactions commit them. With REPEATABLE READ, changes are only visible when both other transactions commit them, and only in a new transaction. Understanding this important point brings you to the essence of the difference between the two states.

Serializable

Serializable takes locking a step further than even REPEATABLE READ. In this state, all plain SELECT queries are treated as if they had a LOCK IN SHARE MODE appended.

Connection 1:

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

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
|    6 |
|    7 |
|    8 |
+------+
8 rows in set (0.06 sec)

Connection 2:

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

mysql> UPDATE t SET f=88 WHERE f=8;

Because of the SELECT statement from the first connection, the UPDATE is locked, just as with an ordinary LOCK IN SHARE MODE. Only once the first transaction is committed does the UPDATE take place.

Connection 1:

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

Connection 2:

Query OK, 1 rows affected (4.23 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

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

Conclusion

Repeatable reads makes sense as a default transaction isolation level, and in most cases, you are unlikely to want to change this. Locking issues can lead to endless hours of fun if you are not careful and do not take note of the subtleties. Next month we will continue our look at MySQL transactions, focusing on other table types.

转自：MySQL Transactions, Part II - Transaction Isolation Levels