MySQL four types of transaction isolation level description

Transferred from: http://www.cnblogs.com/zhoujinyi/p/3437475.html

The articles that were written earlier, reviewed and studied, can also be seen in this article.

By sql:1992 transaction ISOLATION level, InnoDB is repeatable read (repeatable read) by default. 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 similar to the following:

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 (without 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 connections that do not exist. You need super privilege to do this. Use the session keyword to set the default transaction level for future transactions 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 of 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

Read Committed may not be possible

REPEATABLE READ (Repeatable Read) could not possibly

Serializable (Serializable) cannot be impossible

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

· Uncommitted read (READ UNCOMMITTED): Allows dirty reads, which may read data that has not been committed in other sessions for transaction modification

· Read Committed: Read only the data that has been committed. Most databases, such as Oracle, are at this level by default (not repeating)

· Repeatable read (repeated read): Repeatable read. Queries within the same transaction are all consistent at the beginning of the transaction, InnoDB the default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but there are also phantom reads

· Serial Read (Serializable): Fully serialized read, each read requires a table-level shared lock, read and write each other will block

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

Use examples to illustrate the situation at each level:

① Dirty read: dirty read refers to when a transaction is accessing the data, and the data is modified, and this modification has not been committed to the database, then another transaction also accesses the data, and then used this 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 | --------This isolation level (except for 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 | --------This isolation level +------------------------+1 row in Set (0.00 sec) mysql> SELECT * FROM ttd;+------+|    ID |+------+|                                       1 | Dirty reads--------repeatable-read level +------+1 row in Set (0.00 sec)

Conclusion:Session 2 reads the data from session 1 that did not commit the transaction modification under read-uncommitted.

② non-repeatable reads: 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, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called 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> 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) Session 2 after submission, view session 1 results; session 1:mysql> SELECT * FROM ttd;+------+|    ID |+------+|                             1 |    --------is not the same as the first result, the read-committed level has not repeated | 2 |+------+2 rows in Set (0.00 sec)

③ Repeatable reads:

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 session 1 results; session 1:mysql> SELECT * FROM ttd;+------+|    ID |+------+|                                      1 |    --------the same as the first result, the Repeatable-read level appears repeated reading | 2 |+------+2 rows in Set (0.00 sec) (Commit session 1 afterSELECT * from TTD can see the data inserted in Session 2 3) 

④: The first transaction modifies the data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.

Mysql>create TABLE ' t_bitfly ' (' ID ' bigint () not NULL default ' 0 ', ' value ' varchar (+) Default Null,primary KEY (' id ') ) engine=innodbmysql> 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 1v (shit, just tell me that there is no record) so there is a phantom reading, that there is no data in the table, in fact, the data already exists, after the silly submission, only to find the data conflict.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 one more line) | | SELECT * FROM t_bitfly;| +------+-------+| | ID | Value | |    +------+-------+| | 1 | Z | |    | 2 | Z | | +------+-------+

The first time a row is read in this transaction, once an update is made, the data submitted in the other transaction appears. can also be seen as a kind of phantom reading.
When the isolation level is repeatable and innodb_locks_unsafe_for_binlog is disabled, the Next-key locks used when searching for and scanning index can avoid phantom reads.

To see an experiment, note that the ID in 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 | | +------+-------+

As you can see, with id<=1 Lock, only the scope of the id<=1 is locked, you can successfully add a record with ID 2 and add a record with id 0 to wait for the lock to be released.

Experiment four: Consistent 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     | | +----+-------+

MySQL four types of transaction isolation level description