MySQL Transactions, Part II - Transaction Isolation Levels - Page 2

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 |
+------+
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.

» See All Articles by Columnist Ian Gilfillan