First, we initialize the account table with some records as follows. I use MySQL to demonstrate all four isolation levels. The main concepts still apply for other RDBMS.
> describe account;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| owner | varchar(50) | NO | PRI | NULL | |
| balance | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
> select * from account;
+-------+---------+
| owner | balance |
+-------+---------+
| Mary | 210 |
| Tom | 200 |
+-------+---------+
2 rows in set (0.00 sec)
In fact, owner should not be chosen as a primary key. However, for demonstration, let's simplify our schema that way.
Read Uncommitted
This isolation level permits the transaction to read even the uncommitted data. Let's see what does it mean. We open two connections on terminals A and B, respectively. Then, apply the following command to set the isolation level to READ UNCOMMITTED.
:: BOTH TERMINALS
> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 row in set (0.00 sec)
Now start the new transaction in both terminals. From now on, let's call the transaction in terminal A transaction A, and similarly for transaction B on terminal B.
:: BOTH TERMINALS
> begin;
Query OK, 0 rows affected (0.00 sec)
Now, in terminal A, add $50 to Tom's balance, and don't commit this transaction yet.
:: TERMINAL A
> update account set balance = balance + 50 where owner = 'Tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Now if we take a look at Tom's balance on transaction B, we see that his balance has also increased to $250.
:: TERMINAL B
> select * from account where owner = 'Tom';
+-------+---------+
| owner | balance |
+-------+---------+
| Tom | 250 |
+-------+---------+
1 row in set (0.00 sec)
This phenomenon is called dirty read.
Why dirty? You might ask. Well, we know that a transaction carries our data from one consistent state to another consistent state (the C in ACID). However, transaction A has not been committed yet. It means that our transaction has not finished, and probably another UPDATE statement is waiting ahead (say, subtract 50$ from Tom's balance again).
That's the reason why Tom's current balance ($250) is not an "official" value yet. And by allowing other transactions to read such data, we might end up with a wrong condition check during transaction B or even worse, inconsistent data after the transactions have been committed.
Let's roll back both transactions and move on to the second isolation level.
:: BOTH TERMINALS
> rollback;
Query OK, 0 rows affected (0.00 sec)
> select * from account where owner = 'Tom';
+-------+---------+
| owner | balance |
+-------+---------+
| Tom | 200 |
+-------+---------+
1 row in set (0.00 sec)
Read Committed
With this isolation level, the data being read in our transaction is always guaranteed to be committed. Let's change the isolation level in both terminals to read committed.
:: BOTH TERMINALS
> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 row in set (0.00 sec)
> begin;
Query OK, 0 rows affected (0.00 sec)
If we perform the same dirty read experiment, we will no longer encounter that phenomenon.
:: TERMINAL A
> update account set balance = balance + 50 where owner = 'Tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
:: TERMINAL B
> select * from account where owner = 'Tom';
+-------+---------+
| owner | balance |
+-------+---------+
| Tom | 200 |
+-------+---------+
1 row in set (0.00 sec)
> select * from account where balance < 220;
+-------+---------+
| owner | balance |
+-------+---------+
| Mary | 210 |
| Tom | 200 |
+-------+---------+
2 rows in set (0.00 sec)
Here, Tom's balance is still $200. Let's commit transaction A and check the result again.
:: TERMINAL A
> commit;
Query OK, 0 rows affected (0.01 sec)
:: TERMINAL B
> select * from account where owner = 'Tom';
+-------+---------+
| owner | balance |
+-------+---------+
| Tom | 250 |
+-------+---------+
1 row in set (0.00 sec)
> select * from account where balance < 220;
+-------+---------+
| owner | balance |
+-------+---------+
| Mary | 210 |
+-------+---------+
1 rows in set (0.00 sec)
Now, we can have a sigh of relief that the data we read is always clean. But READ COMMITTED is not the highest level of isolation yet. Why? Because changes in one transaction can still interfere with other transactions.
Suppose that you run transaction B on a completely separate machine, without any knowledge about transaction A. You first execute those 2 queries and get some results. A few seconds later, you re-execute those 2 queries and get completely different results. It is not very reasonable behavior.
In other words, those read statements are non-repeatable. The issue with the first SELECT statement is called non-repeatable read, and the issue with the second SELECT statement is called phantom read. Non-repeatable read means that you get two different rows, and phantom read means that you get two different sets of rows.
Therefore, we need a higher isolation level to resolve those two issues. And we will mention it in the next post.
Anyway, we still have one interesting scenario left to consider. When transaction A runs in READ UNCOMMITTED and transaction B runs in READ COMMITTED level, can you guess what will happen?
It turns out that nothing really special happened. Each transaction still follows its principles. Changes by transaction A before commit still cannot be read by transaction B, and A can read the result of any UPDATE statements in B immediately. I shall left the task of verifying to you.
Top comments (0)