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

This article will explain in detail how to test the isolation level of MySQL 5.5. the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, REPEATABLE READ and READ COMMITTED have an important difference: in the same transaction, all consistent reads read data from the snapshot that was first read by the transaction. If multiple identical SELECT statements are executed in the same transaction, the return results of these SELECT query statements are the same. For locked reads (for example, SELECT.. FOR UPDATE or LOCK IN SHARE MODE), UPDATE, DELETE statements, lock dependent statements whether to use unique index scan or range scan. For unique index scans, InnoDB locks only the index records found. For other search criteria, InnoDB locks the records scanned by the range index.

The session ① inserts data into the table and submits it, and the session ② can query the insert data of the session ① only after performing a commit or rollback operation, otherwise you will see the data in the snapshot originally queried when logging in.

Session ①

Mysql > show variables like'% iso%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

1 row in set (0.09 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into dept2 values (10)

Query OK, 1 row affected (0.01sec)

Mysql > commit

Query OK, 0 rows affected (0.12 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

+-+ +

1 row in set (0.00 sec)

Session ②

Mysql > show variables like'% iso%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

1 row in set (0.01 sec)

Mysql > select * from dept2

Empty set (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.16 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

+-+ +

1 row in set (0.00 sec)

Session ①

Mysql > start transaction

->

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into dept2 values (20 million Maintenances)

Query OK, 1 row affected (0.04 sec)

Mysql > commit

Query OK, 0 rows affected (0.05 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

| | 20 | Maintenance |

+-+ +

2 rows in set (0.00 sec)

Session ②

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

+-+ +

1 row in set (0.00 sec)

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

| | 20 | Maintenance |

+-+ +

2 rows in set (0.00 sec)

Interval lock test, in REPEATABLE-READ isolation mode, when MySQL scans the range and condition of the data, it locks the values that may not exist in the range.

Session ①

Mysql > select @ @ global.tx_isolation,@@tx_isolation

+-+ +

| | @ @ global.tx_isolation | @ @ tx_isolation |

+-+ +

| | REPEATABLE-READ | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

| | 20 | Maintenance |

| | 30 | Leader |

+-+ +

3 rows in set (0.00 sec)

Mysql > select * from dept2 where deptno

< 30 lock in share mode; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | +--------+-------------+ 2 rows in set (0.02 sec) 会话② mysql>

SELECT @ @ GLOBAL.tx_isolation, @ @ tx_isolation

+-+ +

| | @ @ GLOBAL.tx_isolation | @ @ tx_isolation |

+-+ +

| | REPEATABLE-READ | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into dept2 values (40 million Market`)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

READ COMMITTED

Transaction isolation level similar to Oracle: each consistent read, in time in the same transaction, reads the latest snapshot of its own transaction. For locked reads (for example, SELECT.. FOR UPDATE or LOCK IN SHARE MODE), UPDATE, DELETE statements, InnoDB locks only indexed records, allowing new records to be inserted into unlocked records.

After the session ① deletes a piece of data and performs the submit operation, the session ② will immediately query the updated operation of the session ①.

Mysql > SET GLOBAL tx_isolation='READ-COMMITTED'

Query OK, 0 rows affected (0.00 sec)

Mysql > exit

Bye

Session ①

Mysql > use fire

Database changed

Mysql > SELECT @ @ GLOBAL.tx_isolation, @ @ tx_isolation

+-+ +

| | @ @ GLOBAL.tx_isolation | @ @ tx_isolation |

+-+ +

| | READ-COMMITTED | READ-COMMITTED |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from dept

+-+

| | DEPTNO | DNAME | LOC | |

+-+

| | 10 | ACCOUNTING | NEW YORK |

| | 20 | RESEARCH | DALLAS |

| | 30 | SALES | CHICAGO |

| | 40 | OPERATIONS | BOSTON |

+-+

4 rows in set (0.08 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

| | 20 | Maintenance |

| | 30 | Leader |

| | 40 | Market |

+-+ +

4 rows in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from dept2 where deptno=40

Query OK, 1 row affected (0.09 sec)

Session ②

Mysql > use fire

Database changed

Mysql > SELECT @ @ GLOBAL.tx_isolation, @ @ tx_isolation

+-+ +

| | @ @ GLOBAL.tx_isolation | @ @ tx_isolation |

+-+ +

| | READ-COMMITTED | READ-COMMITTED |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

| | 20 | Maintenance |

| | 30 | Leader |

| | 40 | Market |

+-+ +

4 rows in set (0.00 sec)

Session ①

Mysql > commit

Query OK, 0 rows affected (0.12 sec)

Session ②

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

| | 20 | Maintenance |

| | 30 | Leader |

+-+ +

3 rows in set (0.00 sec)

Interval lock test, in READ-COMMITTED isolation mode, the session ② is not affected by the session ①.

Session ①

Mysql > select @ @ global.tx_isolation,@@tx_isolation

+-+ +

| | @ @ global.tx_isolation | @ @ tx_isolation |

+-+ +

| | READ-COMMITTED | READ-COMMITTED |

+-+ +

1 row in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from dept2 where deptno

< 30 lock in share mode; +--------+-------------+ | deptno | dname | +--------+-------------+ | 10 | Research | | 20 | Maintenance | +--------+-------------+ 2 rows in set (0.00 sec) 会话② mysql>

SELECT @ @ GLOBAL.tx_isolation, @ @ tx_isolation

+-+ +

| | @ @ GLOBAL.tx_isolation | @ @ tx_isolation |

+-+ +

| | READ-COMMITTED | READ-COMMITTED |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from dept2

+-+ +

| | deptno | dname |

+-+ +

| | 10 | Research |

| | 20 | Maintenance |

| | 30 | Leader |

+-+ +

3 rows in set (0.00 sec)

Mysql > insert into dept2 values (40 million Market`)

Query OK, 1 row affected (0.11 sec)

READ UNCOMMITTED

The SELECT statement allows execution to be unlocked, but only earlier versions of rows can be used. Therefore, with this isolation level, some read operations are not consistent. This isolation level is also known as dirty reading.

SERIALIZABLE

This isolation level is similar to REPEATABLE READ, but if autocommit is not turned on, InnoDB implicitly converts all SELECT statements to SELECT. LOCK IN SHARE MODE .

This is the end of the MySQL 5.5 isolation level test. I hope the above content can be helpful to you and you can learn more. If you think the article is good, you can share it for more people to see.

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.