TX isolation levels (2) READ COMMITTED

Lets continue this series about transaction isolation levels and table a look at READ COMMITTED.

We start with the same data and perform the same steps as we did with READ UNCOMMITTED and take a look at the differences. So prepare the table ‘tt’ that it looks like this:

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Again, open 2 MySQL monitor windows and change the transaction isolation level of the 1st window (which we call CLIENT 1) to READ COMMITTED:

set session transaction isolation level read committed;

So let’s start with the first example. Client 1 starts a transaction and performs a SELECT statement. Then, client 2 updates row 2 to value 4.

CLIENT 1
========

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

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

CLIENT 2
========

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
+------+
3 rows in set (0.00 sec)

As we see, the result is exactly the same as with READ UNCOMMITTED. It’s still possible to see changes of the data applied by another client.

Now, client 2 inserts a row and client 1 performs another SELECT statement:

CLIENT 2
========

mysql> insert into tt values (5);
Query OK, 1 row affected (0.00 sec)

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

Again, there’s no difference to READ UNCOMMITTED. Finally client 2 also starts a transaction and updates and inserts a few rows:

CLIENT 2
========

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)


mysql> insert into tt values (6), (7);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql> update tt set d=8 where d=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

Here’s the difference between READ UNCOMMITTED and READ COMMITTED. While client 2 is in transaction mode, client 1 is not able to see the changes done by client 2.

To sum this up: READ COMMITTED allows non repeatable reads and phantom reads to happen, but in contrast to READ UNCOMMITTED, it does not allow dirty reads.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.