There are four isolation levels for database transactions:
Read Uncommitted: Allow dirty reading, that is, it is possible to read data in other sessions that have not submitted transaction modifications.
Read Committed: Only submitted data can be read, which is the default level for most databases such as Oracle.
Repeated Read: Repeated Read. Queries within the same transaction are consistent at the start of the transaction, InnoDB default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but there are still hallucinations.
Serializable: Completely serialized read, each read requires a table-level shared lock, read and write will block each other.
Friends who first come into contact with the concept of transaction isolation as defined in the textbook above may look confused. Here we will explain the four isolation levels through specific examples.
First we create a user table:
CREATE TABLE user ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE `uniq_name` USING BTREE (name) ) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Read uncommitted isolation level
Let's first set the transaction isolation level to read committed:
mysql> set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec) mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | READ-UNCOMMITTED | +------------------------+ 1 row in set (0.00 sec)
In the following two terminals are used to simulate transaction one and transaction two, respectively, p.s: operation one and operation two mean that they are executed in time sequence.
Transaction 1
mysql> start transaction; # Operation 1 Query OK, 0 rows affected (0.00 sec) mysql> insert into user(name) values('ziwenxie'); # Operation 3 Query OK, 1 row affected (0.05 sec)
Transaction 2
mysql> start transaction; # Operation 2 Query OK, 0 rows affected (0.00 sec) mysql> select * from user; # Operation 4 +----+----------+ | id | name | +----+----------+ | 10 | ziwenxie | +----+----------+ 1 row in set (0.00 sec)
As can be seen clearly from the above execution results, under the read uncommitted level, we may read the data without commit in transaction 2 in transaction 1, which is dirty reading.
Read submit isolation level
The above dirty reading problem can be solved by setting the isolation level to committed.
mysql> set session transaction isolation level read committed;
Transaction I
mysql> start transaction; # Operation I Query OK, 0 rows affected (0.00 sec) mysql> select * from user; # Operation 3 +----+----------+ | id | name | +----+----------+ | 10 | ziwenxie | +----+----------+ 1 row in set (0.00 sec) mysql> select * from user; # Operational 5. The modification of Operational 4 did not affect Transaction 1. +----+----------+ | id | name | +----+----------+ | 10 | ziwenxie | +----+----------+ 1 row in set (0.00 sec) mysql> select * from user; # Operation 7 +----+------+ | id | name | +----+------+ | 10 | lisi | +----+------+ 1 row in set (0.00 sec) mysql> commit; # Operation 8 Query OK, 0 rows affected (0.00 sec)
Transaction II
mysql> start transaction; # Operation II Query OK, 0 rows affected (0.00 sec) mysql> update user set name='lisi' where id=10; # Operation IV Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; # Operation 6 Query OK, 0 rows affected (0.08 sec)
Although the problem of dirty reading has been solved, it should be noted that in operation 7 of transaction 1, transaction 2 will cause different data read twice by transaction 1 in the same transaction after operation 6 commit. This is the problem of non-repeatable reading, which can be solved by using the third transaction isolation level repeatable read.
Repeatable read isolation level
The default transaction isolation level of MySQL's Innodb storage engine is the repeatable read isolation level, so we don't need to make any redundant settings.
Transaction I
mysql> start tansactoin; # Operation I mysql> select * from user; # Operation V +----+----------+ | id | name | +----+----------+ | 10 | ziwenxie | +----+----------+ 1 row in set (0.00 sec) mysql> commit; # Operation 6 Query OK, 0 rows affected (0.00 sec) mysql> select * from user; # Operation 7 +----+------+ | id | name | +----+------+ | 10 | lisi | +----+------+ 1 row in set (0.00 sec)
Transaction II
mysql> start tansactoin; # Operation II mysql> update user set name='lisi' where id=10; # Operation 3 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; # Operation IV
In operation 5 of transaction 1, we did not read the update of transaction 2 in operation 3. Only after commit can we read the updated data.
Did Innodb solve hallucination?
In fact, RR level is likely to produce hallucination. InnoDB engine official claims to solve this problem by using MVCC multi-version concurrency control. Now let's verify that Innodb really solved the hallucination problem.
To facilitate the presentation, I modified the user table above:
mysql> alter table user add salary int(11); Query OK, 0 rows affected (0.51 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> delete from user; Query OK, 1 rows affected (0.07 sec) mysql> insert into user(name, salary) value('ziwenxie', 88888888); Query OK, 1 row affected (0.07 sec) mysql> select * from user; +----+----------+----------+ | id | name | salary | +----+----------+----------+ | 10 | ziwenxie | 88888888 | +----+----------+----------+ 1 row in set (0.00 sec)
Transaction I
mysql> start transaction; # Operation I Query OK, 0 rows affected (0.00 sec) mysql> update user set salary='4444'; # Operations 6, even affected the two lines, not to say solve the hallucination? Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from user; # Operation 7, Innodb does not completely solve hallucination +----+----------+--------+ | id | name | salary | +----+----------+--------+ | 10 | ziwenxie | 4444 | | 11 | zhangsan | 4444 | +----+----------+--------+ 2 rows in set (0.00 sec) mysql> commit; # Operation 8 Query OK, 0 rows affected (0.04 sec)
Transaction II
mysql> start transaction; # Operation II Query OK, 0 rows affected (0.00 sec) mysql> insert into user(name, salary) value('zhangsan', '666666'); # Operation IV Query OK, 1 row affected (0.00 sec) mysql> commit; # Operation V Query OK, 0 rows affected (0.04 sec)
As can be seen from the above example, Innodb did not solve the illusion reading as the official said, but it is not very common in the above scenarios to not worry too much.
Serialization isolation level
Serial execution of all transactions, the highest isolation level, will not appear hallucination performance will be very poor, rarely used in actual development.
Contact
GitHub: https://github.com/ziwenxie
Blog: https://www.ziwenxie.site
Email: ziwenxiecat@gmail.com
This article was sent to me at the same time. Personal Blog To reprint, please state the origin of the blog:)