MySQL Four transaction isolation level detailed and contrast _mysql

MySQL Four transaction isolation level detailed and comparison

According to the sql:1992 transaction isolation level, InnoDB is repeatable by default (Repeatable Read). MYSQL/INNODB provides all four transaction isolation levels described by the SQL standard. You can set the default isolation level for all connections at the command line with the--transaction-isolation option, or in the options 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}

The user can use the SET TRANSACTION statement to change the isolation level of a single session or all new incoming connections. Its syntax is as follows:

SET [Session | GLOBAL] TRANSACTION Isolation Level {READ UNCOMMITTED | READ Committed | Repeatable READ | SERIALIZABLE}

Note: The default behavior (with no session and 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 created from that point (except for nonexistent connections). You need super privileges to do this. Use the session keyword to set the default transaction level for future transactions that are performed on the current connection. Any client can freely change the session isolation level (even in the middle of a transaction), or set the isolation level for the next transaction.

You can query the global and session transaction isolation levels with the following statement:

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

----The theoretical knowledge in the above manuals;
===========================================================================================
Isolation level Dirty Read (Dirty Read) non-repeatable read (Nonrepeatable Read) Phantom Read (Phantom Read)
===========================================================================================

Uncommitted reads (READ UNCOMMITTED) may be possible

Committed read (Read Committed) could not possibly

Repeatable reads (REPEATABLE Read) cannot possibly be

Serializable (Serializable) impossible impossible impossible

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

• Uncommitted read (READ UNCOMMITTED): allow dirty reads, that is, data that may be read to uncommitted transaction modifications in other sessions

• Submit read (Read Committed): only data that has been submitted can be read. Most databases, such as Oracle, default to this level (no repetition)

• Repeatable Read (repeated read): repeatable read. Queries within the same transaction are always at the beginning of the transaction, InnoDB the default level. In the SQL standard, the isolation level eliminates the need for repeatable reads, but there are also phantom reads

• Serial Read (Serializable): fully serialized read, each read need to obtain table-level shared lock, read and write each other will block

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

Use examples to illustrate each level of the situation:

① dirty reads: dirty reading means that when a transaction is accessing data and the data has been modified, and the modification has not been committed to the database, another transaction accesses the data and then uses the data.

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 | --------The isolation level (In addition to read UNCOMMITTED) +-----------------------+ 1 row in Set (0.00 sec) mysql> select * from TTD;
Empty Set (0.00 sec)--------does not appear 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 |
--------the isolation level +------------------------+ 1 row in Set (0.00 sec) mysql> select * from TTD; +------+
|
ID |  +------+
|                    1 |

 Dirty Read +------+ 1 row in Set (0.00 sec)--------Repeatable-read level

Conclusion : Session 2 reads the data of uncommitted transaction modifications in Session 1 under read-uncommitted.

② : Refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction and the modification of the second transaction, the data read by the first transaction two times may be different. This makes it happen that the data read two times within a transaction is not the same, so it is called a non repeatable 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> inserts into TTD values (2);
/can also update 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) Session 2 submit, view the results of Session 1;
Session 1:mysql> SELECT * from TTD; +------+
|
ID |  +------+
|               1 |  --------and the first results are not the same, the read-committed level appeared not repeat |
2 |

 +------+ 2 rows in Set (0.00 sec)

③ can be read again:

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 submitting, view the results of Session 1;
Session 1:mysql> SELECT * from TTD; +------+
|
ID |  +------+
|                   1 |  --------the same as the first result, the Repeatable-read level appears repeated reads |
2 | +------+ 2 rows in Set (0.00 sec) (CommIt session 1 and then select * from TTD can see session 2 Insert Data 3)

 

④ : The first transaction modifies the data in a table that involves all the rows of data in the table. At the same time, the second transaction modifies the data in this table, which is inserting a row of new data into the table. So, it's going to happen later. The user of the first transaction discovers that there are no modified data rows in the table, as if there were hallucinations.

Mysql>create TABLE ' t_bitfly ' (' id ' bigint ' NOT null default ' 0 ', ' value ' varchar ') default NULL, PRIMARY KEY ('
Id ') engine=innodb mysql> select @ @global. tx_isolation, @ @tx_isolation; +-----------------------+-----------------+
| @ @global. tx_isolation |
@ @tx_isolation | +-----------------------+-----------------+
| Repeatable-read |
Repeatable-read | +-----------------------+-----------------+ Experiment One: T session a session B | |     START TRANSACTION; 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 (shit, just tell me that I did not have this record) so there is the illusion of reading, that the table has no data, in fact, the data already exist, silly submission, only to find data conflicts. Experiment two: T session A session B | |     START TRANSACTION; 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 come out a line) | | SELECT * from T_bitfly; | +------+-------+
| | ID | Value | |  +------+-------+
| | 1 |  Z | | | 2 | Z | |

 +------+-------+

The first time a row is read in this transaction, and once an update is made, the data submitted in the other transaction appears. It can also be seen as a kind of phantom reading.

When the isolation level is repeatable and disables Innodb_locks_unsafe_for_binlog, the Next-key locks used when searching and scanning index can avoid phantom reading.

To look at an experiment, note that the ID in the table t_bitfly is the primary key field.

 experiment three: T session A session B | |    START TRANSACTION; 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 | | +------+-------+

You can see that with id<=1 locks, only the id<=1 range is locked, you can successfully add a record with ID 2, and you will wait for the lock to be released when you add a record with ID 0.

Experiment four: Consistency read and submit read
T session A session           B
|
| START TRANSACTION;       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 you use normal reading, you get consistent results, and if you use a locked read, you'll read the results of the latest submit read.

itself, repeatable reading and submission reading are contradictory. In the same transaction, if repeatable reading is ensured, the submission of other transactions is not seen and the submission is violated, and if the submission is guaranteed, the results are inconsistent with the two readings, which violates the repeatable reading.

As you can say, InnoDB provides such a mechanism that in the default repeatable read isolation level, you can use the lock read to query the latest data (Submit read).
The repeatable reading of MySQL InnoDB does not guarantee the avoidance of phantom reading and requires the use of a lock read to ensure that it is used. And this lock degree is used to the mechanism is Next-key locks.

Summarize:

Four levels are gradually enhanced, and each level solves a problem. The higher the transaction level, the worse the performance, most environments Read Committed can be used. Remember the characteristics of 4 isolation levels (examples above);

Thank you for reading, I hope to help you, thank you for your support for this site!