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
» 
Comment and Contribute
