Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the level of mysql transaction isolation". In daily operation, I believe many people have doubts about the level of mysql transaction isolation. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "the level of mysql transaction isolation". Next, please follow the editor to study!

The ANSI SQL standard defines four isolation levels:

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

ANSI isolation level

Isolation level dirty reading can not be repeated phantom reading

READ UNCOMMITTED allow

READ COMMITTED does not allow permission

Whether REPEATABLE READ does not allow permission

SERIALIZABLE does not allow

The following is an example of a test.

1.READ UNCOMMITTED

Session 1:

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into t values (2)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

2 rows in set (0.00 sec)

Session 2:

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Mysql > set tx_isolation = 'READ-UNCOMMITTED'

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

| | 2 |

+-+

2 rows in set (0.00 sec)

When the isolation level is set to READ-UNCOMMITTED, you can see the uncommitted data.

2 、 READ-COMMITTED

Session 1

Mysql > SET tx_isolation = 'READ-COMMITTED'

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Session 2:

Mysql > SET tx_isolation = 'READ-COMMITTED'

Query OK, 0 rows affected (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Session 1:

Mysql > insert into t values (2)

Query OK, 1 row affected (0.02 sec)

Mysql > update t set id=3 where id=1

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from t

+-+

| | id |

+-+

| | 3 |

| | 2 |

+-+

2 rows in set (0.00 sec)

Session2:

Mysql > select * from t

+-+

| | id |

+-+

| | 3 |

| | 2 |

+-+

2 rows in set (0.00 sec)

After the data of session1 is committed, the latest data can be read in the transaction of session2.

3 、 REPEATABLE READ

Session1:

Mysql > SET tx_isolation = 'REPEATABLE-READ'

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 3 |

| | 2 |

+-+

2 rows in set (0.00 sec)

Session2:

Mysql > SET tx_isolation = 'REPEATABLE-READ'

Query OK, 0 rows affected (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 3 |

| | 2 |

+-+

2 rows in set (0.00 sec)

Session 1:

Mysql > update t set id=1 where id=2

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > delete from t where id=3

Query OK, 1 row affected (0.01sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Session 2:

Mysql > select * from t

+-+

| | id |

+-+

| | 3 |

| | 2 |

+-+

2 rows in set (0.00 sec)

The data found here is still the data at the beginning of the transaction, not the latest data.

The phenomenon of phantom reading is solved here because the InnoDb engine prevents phantom reading through a gap lock (nex-key locking) strategy.

This is a bit different from the standard REPEATABLE READ isolation.

4 、 SERIALIZABLE

Session 1:

Mysql > SET tx_isolation = 'SERIALIZABLE'

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Session 2:

Mysql > SET tx_isolation = 'SERIALIZABLE'

Query OK, 0 rows affected (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

= = > the records in table t will be locked (that is, locked reads), as can be seen by the following query:

Mysql > SELECT trx_id, trx_state, trx_rows_locked, trx_rows_modified FROM information_schema.INNODB_TRX

+-+

| | trx_id | trx_state | trx_rows_locked | trx_rows_modified | |

+-+

| | 9598 | RUNNING | 3 | 0 | |

+-+

Session 1:

Mysql > update t set id=2 where id= 1

Query OK, 1 row affected (27.05sec)

Rows matched: 1 Changed: 1 Warnings: 0

The time here takes 27.05 sec because the lock cannot be obtained until session 1 issues the commit or rollback command while waiting for the lock to be released by session2.

Summary:

1. REPEATABLE READ is the default isolation level of mysql, and repeated and phantom reads are not allowed.

2. The InnoDb engine prevents misreading through the gap lock (nex-key locking) strategy.

3. Lock reading will be added in SERIALIZABLE isolation mode, similar to select. For update, while other isolation levels do not.

At this point, the study on "the level of mysql transaction isolation" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.