Detailed description and comparison of the four transaction isolation levels of MySQL, and detailed description of mysql

Detailed description and comparison of the four transaction isolation levels of MySQL, and detailed description of mysql

Detailed description and comparison of the four transaction isolation levels of MySQL

At the SQL: 1992 transaction isolation level, InnoDB is REPEATABLE READ by default ). MySQL/InnoDB provides all four transaction isolation levels described in the SQL standard. You can use the -- transaction-isolation option on the command line, or set the default isolation level for all connections in the option file.
For example, you can set this option in the [mysqld] section of the my. inf file as follows:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

You can use the set transaction statement to change the isolation level of a single session or all new connections. Its syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Note: The default action (without session or global) is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections (except non-existing connections) created from that point. You need the SUPER permission to do this. Use the SESSION keyword to set the default transaction level for future transactions executed on the current connection. Any client can freely change the session isolation level (even in the middle of the transaction), or set the isolation level for the next transaction.

You can use the following statements to query the global and session transaction isolation levels:

SELECT @@global.tx_isolation;SELECT @@session.tx_isolation;SELECT @@tx_isolation;

---- Theoretical knowledge in the above manual;
========================================================== ========================================================== ==============
Isolation level Dirty Read (Dirty Read) Non-repeated Read (NonRepeatable Read) Phantom Read (Phantom Read)
========================================================== ========================================================== ==============

Uncommitted Read (Read uncommitted) may be

Read committed is impossible.

Repeatable read is impossible

Serializable cannot be impossible

========================================================== ========================================================== ==============

· Uncommitted Read (Read Uncommitted): Dirty Read is allowed, that is, data that may be Read from other sessions that have not committed transaction modifications

· Read Committed: only submitted data can be Read. Most databases such as Oracle are at this level by default (no repeated read)

· Repeated Read: Repeated Read. The queries in the same transaction are consistent at the start time of the transaction, and the InnoDB default level. In the SQL standard, this isolation level eliminates non-repeated reads, but Phantom reads still exist.

· Serializable: Fully Serializable read. Each read requires a table-level shared lock, which blocks read/write operations.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~

Use examples to describe the situation at various levels:

① Dirty read:Dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used.

Session 1: mysql> select @ global. tx_isolation; + --------------------- + | @ global. tx_isolation | + ----------------------- + | REPEATABLE-READ | + ----------------------- + 1 row in set (0.00 sec) mysql> select @ session. tx_isolation; + --------------------- + | @ session. tx_isolation | + --------------------- + | REPEATABLE-READ | + ----------------------- + 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into ttd values (1); Query OK, 1 row affected (0.05 sec) mysql> select * from ttd; + ------ + | id | + ------ + | 1 | + ------ + 1 row in set (0.00 sec) session 2: mysql> select @ session. tx_isolation; + ------------------------ + | @ session. tx_isolation | + ---------------------- + | REPEATABLE-READ | + ------------------------ + 1 row in set (0.00 sec) mysql> select @ global. tx_isolation; + --------------------- + | @ global. tx_isolation | + --------------------- + | REPEATABLE-READ | -------- under this isolation level (except read uncommitted) + --------------------- + 1 row in set (0.00 sec) mysql> select * from ttd; empty set (0.00 sec) -------- no dirty read 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 | -------- under this isolation level + ------------------------ + 1 row in set (0.00 sec) mysql> select * from ttd; + ------ + | id | + ------ + | 1 | -------- REPEATABLE-READ-level dirty read + ------ + 1 row in set (0.00 sec)

Conclusion: Session 2 reads UNCOMMITTED transaction modification data in session 1 under READ-UNCOMMITTED.

② Repeatable reading: Refers to reading the same data multiple times in a transaction. When the transaction is not completed, another transaction also accesses the same data. Therefore, the data read twice in the first transaction may be different because of the modification of the second transaction. In this way, the data read twice in a transaction is different, so it is called non-repeated read.

Session 1: mysql> select @ session. tx_isolation; + ------------------------ + | @ session. tx_isolation | + ---------------------- + | READ-COMMITTED | + ---------------------- + 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from ttd; + ------ + | id | + ------ + | 1 | + ------ + 1 row in set (0.00 sec) session 2: mysql> select @ session. tx_isolation; + ------------------------ + | @ session. tx_isolation | + ------------------------ + | REPEATABLE-READ | + ---------------------- + 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from ttd; + ------ + | id | + ------ + | 1 | + ------ + 1 row in set (0.00 sec) mysql> insert into ttd values (2 ); /You can also update the data Query OK, 1 row affected (0.00 sec) mysql> select * from ttd; + ------ + | id | + ------ + | 1 | 2 | + ------ + 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.02 sec) after session 2 is submitted, view the result of session 1. session 1: mysql> select * from ttd; + ------ + | id | + ------ + | 1 | -------- is different from the first result, READ-COMMITTED-level non-repeated READ | 2 | + ------ + 2 rows in set (0.00 sec)

③ Repeatable read:

Session 1: mysql> select @ session. tx_isolation; + ------------------------ + | @ session. tx_isolation | + ------------------------ + | REPEATABLE-READ | + ---------------------- + 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from ttd; + ------ + | id | + ------ + | 1 | 2 | + ------ + 2 rows in set (0.00 sec) session 2: mysql> select @ session. tx_isolation; + ------------------------ + | @ session. tx_isolation | + ------------------------ + | REPEATABLE-READ | + ---------------------- + 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into ttd values (3); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.03 sec) session 2 after submission, view the results of session 1; session 1: mysql> select * from ttd; + ------ + | id | + ------ + | 1 | -------- is the same as the first result, repeated read at the REPEATABLE-READ level | 2 | + ------ + 2 rows in set (0.00 sec) (after the commit session 1, select * from ttd to see the data inserted by session 2 3)

④ Phantom read: The first transaction modifies the data in a table, which involves all data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion.

Mysql> create table 't_ bitfly '('id' bigint (20) not null default '0', 'value' varchar (32) default NULL, primary key ('id') ENGINE = InnoDBmysql> select @ global. tx_isolation, @ tx_isolation; + ----------------------- + ----------------- + | @ global. tx_isolation | @ tx_isolation | + ------------------------- + ----------------- + | REPEATABLE-READ | + ------------------------- + ----------------- + Experiment 1: t S Ession A Session B | START TRANSACTION; | SELECT * FROM t_bitfly; | empty set | INSERT INTO t_bitfly | VALUES (1, 'A '); | SELECT * FROM t_bitfly; | empty set | COMMIT; | SELECT * FROM t_bitfly; | empty set | insert into t_bitfly VALUES (1, 'A '); | ERROR 1062 (23000): | Duplicate entry '1' for key 1 v, I thought there was no data in the table. In fact, the data already exists. After a silly commit, I found the data conflict. Tutorial 2: t Session A Session B | START TRANSACTION; | SELECT * FROM t_bitfly; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | a | + ------ + ------- + | insert into t_bitfly | VALUES (2, 'B'); | SELECT * FROM t_bitfly; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | a | + ------ + ------- + | COMMIT; | SELECT * FROM t_bitfly; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | a | + ------ + ------- + | UPDATE t_bitfly SET value = 'Z'; | Rows matched: 2 Changed: 2 Warnings: 0 | (how to add one more row) | SELECT * FROM t_bitfly; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | z | 2 | z | + ------ + ------- +

This is the first time that a row is read and retrieved. After an update is performed, the data committed in another transaction appears. It can also be seen as a phantom read.

When the isolation level is repeatable and innodb_locks_unsafe_for_binlog is disabled, the next-key locks used for searching and scanning indexes can avoid phantom read.

In another experiment, note that the id in the t_bitfly table is the primary key field.

Tutorial 3: t Session A Session B | START TRANSACTION; | SELECT * FROM t_bitfly | WHERE id <= 1 | FOR UPDATE; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | a | + ------ + ------- + | insert into t_bitfly | VALUES (2, 'B'); | Query OK, 1 row affected | SELECT * FROM t_bitfly; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | a | + ------ + ------- + | insert into t_bitfly | VALUES (0, '0'); | (waiting for lock... then timeout) | ERROR 1205 (HY000): | Lock wait timeout exceeded; | try restarting transaction | SELECT * FROM t_bitfly; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | a | + ------ + ------- + | COMMIT; | SELECT * FROM t_bitfly; | + ------ + ------- + | id | value | + ------ + ------- + | 1 | a | + ------ + ------- +

We can see that the lock with id <= 1 only locks the range of id <= 1 and records with id 2 can be successfully added, when a record whose id is 0 is added, it will wait for the lock to be released.

Experiment 4: consistent read and commit read t Session A Session B | START TRANSACTION; | SELECT * FROM t_bitfly; | + ---- + ------- + | id | value | + ---- + ------- + | 1 | a | + ---- + ------- + | insert into t_bitfly | VALUES (2, 'B'); | COMMIT; | SELECT * FROM t_bitfly; | + ---- + ------- + | id | value | + ---- + ------- + | 1 | a | + ---- + ------- + | SELECT * FROM t_bitfly LOCK IN SHARE MODE; | + ---- + ------- + | id | value | + ---- + ------- + | 1 | a | 2 | B | + ---- + ------- + | SELECT * FROM t_bitfly for update; | + ---- + ------- + | id | value | + ---- + ------- + | 1 | a | 2 | B | + ---- + ------- + | SELECT * FROM t_bitfly; | + ---- + ------- + | id | value | + ---- + ------- + | 1 | a | + ---- + ------- +

If normal reads are used, consistent results will be obtained. If locked reads are used, the read results of the "latest" and "committed" will be read.

In itself, repeatable reads and committed reads are in conflict. In the same transaction, if repeatable reads are ensured, the commit of other transactions will not be visible, violating the committed read; if committed read is ensured, this will cause inconsistency between the two read results, which violates the Repeatable read.

In this case, InnoDB provides such a mechanism. By default, the locks can be used to query the latest data (committed for read) at the isolation level of Repeatable read ).
The Repeatable read of MySQL InnoDB does not guarantee the avoidance of phantom read. It must be ensured by applying the lock read. The locking mechanism is next-key locks.

Summary:

The four levels are gradually enhanced, and each level solves one problem. The higher the transaction level, the worse the performance. read committed can be used in most environments. Remember the features of the four isolation levels (in the above example );

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!