Shulou(Shulou.com)06/01 Report--

About the isolation level of SQL

The SQL standard defines four types of isolation levels, as follows:

1. Read Uncommitted (read unsubmitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Read uncommitted data, also known as Dirty Read

2. Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

3. Repeatable Read (repeatable)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

4. Serializable (serializable)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

In Mariadb, the possible problems caused by these four isolation levels are shown in the following figure:

Related experiment

The following tests are conducted for different isolation states:

The prepared environment is as follows:

On the Mariadb server side, create a database named hldb, create an INNODB engine data table named test, 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 unsubmitted)

First, set the isolation level for both clients 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 side of id=6 (hereafter referred to as id6). The second step is to open start transaction on both clients. Third, after both clients have opened start transaction, insert a piece of data on top of the client of id=5 (hereafter referred to as id5). The fourth step is to query with select on top of id5's own session. The fifth step is to query with select on the id6 session.

The results you see on 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 +-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 you see on id6 are as follows:

MariaDB [hldb] > select * from test;+----+-+ | id | nm | +-+-+ | 1 | a | 21 | +-+-- + 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), if the transaction of id5 is not committed, the transaction of id6 can query its uncommitted transaction. This phenomenon of being able to read uncommitted transactions is called dirty reading

2. Read Committed (read submission)

Set the transaction isolation level of both clients to Read Committed, and query the contents of the test data 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 start transaction on both clients. The second step is to add a record to id5 and query it 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 commit the transaction on id5 and query it on id5 and id6.

The result of the operation on the id5 client is as follows:

First step: MariaDB [hldb] > start transaction;Query OK, 0 rows affected (0.00 sec) second step: MariaDB [hldb] > insert into test (nm) values ('d'); Query OK, 1 row affected (0.00 sec) MariaDB [hldb] > select * from test +-step 3: MariaDB [hldb] > delete from test where nm='b';Query OK, 1 row affected (0.00 sec) MariaDB [hldb] > select * from test +-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 action on the id6 client is as follows:

First step: MariaDB [hldb] > start transaction;Query OK, 0 rows affected (0.00 sec) second step: 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.01sec)

Conclusion:

Under the isolation level of Read Committed (read submitted content), dirty reading is solved, but it brings another phenomenon: non-repeatable reading. Before and after the commit of the id5 transaction, id6 is in the same transaction, and the content of the query is inconsistent.

3. Repeatable Read (repeatable)

First, set the transaction isolation level of both clients to Repeatable Read, and query the contents of the test data table:

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 start transaction on both clients. The second step is to add a record to id5 and query it 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 commit the transaction on id5 and query it on id5 and id6. The fifth step is to commit the transaction on id6 and query on id6.

The result of the operation on the id5 client is as follows:

First step: MariaDB [hldb] > start transaction; Query OK, 0 rows affected (0.00 sec) second step: 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) third step: 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 result of the operation on id6 is as follows:

First step: MariaDB [hldb] > start transaction;Query OK, 0 rows affected (0.00 sec) second step: MariaDB [hldb] > select * from test;+----+-+ | id | nm | +-- +-+ | 1 | a | +-+-+ 1 row in set (0.00 sec) third step: MariaDB [hldb] > select * from test +-+-- +-+ | id | nm | +-+-+ | 1 | a | +-+-+ 1 row in set (0.01sec) 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:

As can be seen from the above experiments, the difference between transaction isolation level Repeatable read (repeatable read) and Read Committed (committed read) is that in the same transaction environment, the contents of the two reads are consistent, regardless of whether other transactions commit or not.

Note:

Under the condition of Repeatable read (repeatable readability), Phantom Read (phantom reading) phenomenon may occur. This phenomenon can be achieved by simulating update:

The first step is to turn on start transaction on both sides of the client. The second step is to add a field to 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 above result of id6 is 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 operation on id5 is 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: 0MariaDB [hldb] > select * from test +-+ | id | nm | +-+-+ | 1 | c | | 2 | c | +-+-+ 2 rows in set (0.00 sec)

As you can see, through the update command, the "new data" is also read in the unfinished transaction, which is a kind of Phantom Read (illusion reading).

4. Serializable (serializable)

First, set the transaction isolation level of both clients to Serializable, and query the contents of the test data table:

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 start transaction on both clients. The second step is to add a record to id5 and query it on id5 and id6.

The result of the query on id5 is 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 result of the query on id6 is 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, even select query operations are not allowed in other transactions before id5 commits the transaction, and other transactions can only update or modify after waiting for id5 to commit the transaction.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.