On the Isolation Level of SQL
The SQL standard defines four isolation levels, as follows:
1. Read Uncommitted
At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is seldom used in practical applications, because its performance is not much better than other levels. Reading uncommitted data is also called Dirty Read.
2. Read Committed
This is the default isolation level for most database systems (but not MySQL). It satisfies the simple definition of isolation: a transaction can only see changes made by a committed transaction. This isolation level also supports the so-called Nonrepeatable Read, because other instances of the same transaction may have new commit during the processing of the instance, so the same select may return different results.
3. Repeatable Read
This is MySQL's default transaction isolation level, which ensures that multiple instances of the same transaction will see the same data row when reading data concurrently. In theory, however, this leads to another thorny problem: Phantom Read. Simply put, hallucination refers to when a user reads a range of data rows, another transaction inserts new rows in the range, and when the user reads the range of data rows, new "hallucination" rows will be found. InnoDB and Falcon storage engines solve this problem through MVCC (Multiversion Concurrency Control) mechanism.
4. Serializable
This is the highest isolation level, which solves the hallucination problem by forcing transaction sorting to make it impossible to conflict with each other. In short, it adds a shared lock to each read data row. At this level, it may lead to a large number of timeouts and lock competition.
In Mariab, the problems that may arise from these four isolation levels are shown in the following figure:
Relevant experiments
The following tests are conducted for different isolation states:
The preparation environment is as follows:
On the Mariab server side, create a database named hldb, create a table named test for the INNODB engine, and insert two pieces of data in advance.
Prepare two MySQL client threads to connect to the server:
MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+
1. Read uncommitted (read uncommitted)
Firstly, the isolation level of both clients is set to Read uncommitted mode:
MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> set @@session.tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.00 sec) ...... ...... MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> set @@session.tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.00 sec)
The first step is to complete a query on the client with id=6 (hereinafter referred to as id6). The second step is to open the start transaction on both clients at the same time. The third step is to insert a data on the client with id=5 (hereinafter referred to as id5) after both clients have opened the start transaction. The fourth step is to use select to query the session of id5 itself. The fifth step is to use select to query the id6 session.
The results shown above in id5 are as follows:
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 21 | b |
+----+------+
2 rows in set (0.00 sec)
MariaDB [hldb]> insert into test(nm) values('c');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 21 | b |
| 22 | c |
+----+------+
3 rows in set (0.00 sec)The results shown above in id6 are as follows:
MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | +----+------+ 2 rows in set (0.00 sec) MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec)
Conclusion:
As you can see, if the transaction level is set to Read Uncommitted (read uncommitted), the transactions of id6 can be queried for uncommitted transactions when the transactions of id5 are uncommitted. This ability to read uncommitted transactions is called dirty reading.
2. Read Committed
Set the transaction isolation level for both clients to Read Committed and query the contents of the test table
MariaDB [hldb]> set @@session.tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec) ...... ...... MariaDB [hldb]> set @@session.tx_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec)
The first step is to open the start transaction on both clients. The second step is to add a record on id5 and query on id5 and id6. The third step is to delete a record on id5 and query it on id5 and id6. The fourth step is to submit a transaction on id5 and query on id5 and id6.
The operation results on the id5 client are as follows:
Step 1:
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
//Step 2:
MariaDB [hldb]> insert into test(nm) values('d');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 21 | b |
| 22 | c |
| 23 | d |
+----+------+
4 rows in set (0.00 sec)
//Step 3:
MariaDB [hldb]> delete from test where nm='b';
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 22 | c |
| 23 | d |
+----+------+
3 rows in set (0.00 sec)
//Step 4:
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 22 | c |
| 23 | d |
+----+------+
3 rows in set (0.00 sec)The operation above the id6 client is as follows:
Step 1: MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) //Step 2: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec) //Step 3: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 21 | b | | 22 | c | +----+------+ 3 rows in set (0.00 sec) //Step 4: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | | 22 | c | | 23 | d | +----+------+ 3 rows in set (0.01 sec)
Conclusion:
Under the isolation level of Read Committed, dirty reading is solved, but it brings another phenomenon: non-repeatable reading. Before and after the submission of id5 transaction, id6 queries inconsistently in the same transaction.
3. Repeatable Read
First, the transaction isolation level of both clients is set to Repeatable Read, and the contents of the test data table are queried:
MariaDB [hldb]> set @@session.tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) ...... ...... MariaDB [hldb]> set @@session.tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec)
The first step is to open the start transaction on both clients. The second step is to add a record on id5 and query on id5 and id6. The third step is to delete a record on id5 and query it on id5 and id6. The fourth step is to submit a transaction on id5 and query on id5 and id6. The fifth step is to submit a transaction on id6 and query it on id6.
The operation results on the id5 client are as follows:
Step 1:
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
//Step 2:
MariaDB [hldb]> insert into test(nm) values('b');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 25 | b |
+----+------+
2 rows in set (0.00 sec)
//Step 3:
MariaDB [hldb]> delete from test where id=1;
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 25 | b |
+----+------+
1 row in set (0.00 sec)
//Step 4:
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 25 | b |
+----+------+
1 row in set (0.00 sec)The operation results above id6 are as follows:
Step 1: MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) //Step 2: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) //Step 3: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.01 sec) //Step 4: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) //Step 5: MariaDB [hldb]> commit; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 25 | b | +----+------+ 1 row in set (0.00 sec)
Conclusion:
From the above experiments, we can see that the difference between Repeatable read and Read Committed in transaction isolation level is that in the same transaction environment, the contents read before and after are the same, regardless of whether other transactions are committed or not.
Note:
Under the condition of Repeatable Read, Phantom Read may appear. This phenomenon can be achieved by simulating update:
The first step is to start transaction on both sides of the client. The second step is to add a field on id6 and query it on id5 and id6 respectively. The third step is to submit on id6 and query on id5 and id6 respectively. The fourth step is to update the nm fields of all rows on id5 and query them on id5 and id6 respectively.
The results above id6 are as follows:
Step 1:
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
+----+------+
1 row in set (0.00 sec)
//Step 2:
MariaDB [hldb]> insert into test(nm) values('b');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
//Step 3:
MariaDB [hldb]> commit;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)The operations above id5 are as follows:
Step 1: MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) //Step 2: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) //Step 3: MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) //Step 4: MariaDB [hldb]> update test set nm='c'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 1 | c | | 2 | c | +----+------+ 2 rows in set (0.00 sec)
As you can see, by updating the command, you can even read "new data" in an unfinished transaction, which is a Phantom Read.
4. Serializable
First, the transaction isolation level of both clients is set to Serializable, and the contents of the test data table are queried:
MariaDB [hldb]> set @@session.tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 26 | a | | 27 | b | +----+------+ 2 rows in set (0.00 sec) ...... ...... MariaDB [hldb]> set @@session.tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec) MariaDB [hldb]> select * from test; +----+------+ | id | nm | +----+------+ | 26 | a | | 27 | b | +----+------+ 2 rows in set (0.00 sec)
The first step is to open the start transaction on both clients. The second step is to add a record to id5 and query on id5 and id6
The results of the query above id5 are as follows:
MariaDB [hldb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hldb]> insert into test(nm) values('c');
Query OK, 1 row affected (0.00 sec)
MariaDB [hldb]> select * from test;
+----+------+
| id | nm |
+----+------+
| 26 | a |
| 27 | b |
| 28 | c |
+----+------+
3 rows in set (0.00 sec)The results of the query above id6 are as follows:
MariaDB [hldb]> start transaction; Query OK, 0 rows affected (0.00 sec) MariaDB [hldb]> select * from test; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
As can be seen from the above results, before id5 commits a transaction, even select query operation is not allowed to be executed in other transactions. Only after waiting for id5 to commit a transaction, other transactions can be updated or modified.