62.【数据库】Mysql事务隔离级别测试本文正在参加「技术专题19期漫谈数据库技术」活动 62.【数据库】Mysql

本文正在参加「技术专题19期漫谈数据库技术」活动

书接前几回,对mySQL几种事务隔离级别及问题进行测试

6.面试宝典-数据库事务概述 - 掘金 (juejin.cn)

7.面试宝典-数据库索引概述 - 掘金 (juejin.cn)

8.面试宝典-数据库索引数据结构 - 掘金 (juejin.cn)

9.面试宝典-数据库分布式事务概论 - 掘金 (juejin.cn)

mysql版本：Server version: 5.7.24-log

1.查看默认隔离级别

[anchu@localhost ~]$ mysql  -h 192.168.120.110 -u root  -D test --password
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.04 sec)

mysql>

2.创建表,及测试数据

mysql> CREATE TABLE `tb_user` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(10) DEFAULT NULL,
    ->   `password` varchar(10) DEFAULT NULL,
    ->   `sex` char(1) DEFAULT NULL,
    ->   `age`  int(10) default 0,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10010 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `tb_user`(name,password,sex,age) VALUES ('testname', '111', '2', 27);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   27 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_test      |
+---------------------+
| tb_user             |

2.1 设置读未提交，未提交修改并查看:出现脏读

session 1

mysql> SET SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

mysql> select * from tb_users;
ERROR 1146 (42S02): Table 'test.tb_users' doesn't exist
mysql> select * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   27 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> select * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   29 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)




session2

mysql> SET SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)

mysql> UPDATE tb_user set age=29 where id=10010;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.2 设置读已提交，未提交修改并查看:没有脏读；提交修改数据并查看：出现：不可重复读

未提交修改，没有产生脏读

session1

mysql> SET SESSION TRANSACTION ISOLATION LEVEL read COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   29 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   29 |
+-------+----------+----------+------+------+
1 row in set (0.01 sec)





session2
mysql> SET SESSION TRANSACTION ISOLATION LEVEL read COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tb_user set age=30 where id=10010;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   30 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql>

提交数据并查看：不可重复读

session1


mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   29 |
+-------+----------+----------+------+------+
1 row in set (0.01 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   30 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)


session2


mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   30 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> 
mysql> commit;
Query OK, 0 rows affected (0.02 sec)

未提交新增并查看:没有脏读；提交新增数据，出现幻读

需要退出当前会话重新登陆

session1

mysql> SET SESSION TRANSACTION ISOLATION LEVEL read COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| 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 tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname5 | 555      | 5    |   30 |
| 10015 | testname6 | 666      | 6    |   32 |
| 10016 | testname7 | 777      | 7    |   33 |
+-------+-----------+----------+------+------+
7 rows in set (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname5 | 555      | 5    |   30 |
| 10015 | testname6 | 666      | 6    |   32 |
| 10016 | testname7 | 777      | 7    |   33 |
| 10018 | testname8 | 888      | 8    |   34 |
+-------+-----------+----------+------+------+
8 rows in set (0.00 sec)




session2

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL read COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.02 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname5 | 555      | 5    |   30 |
| 10015 | testname6 | 666      | 6    |   32 |
| 10016 | testname7 | 777      | 7    |   33 |
+-------+-----------+----------+------+------+
7 rows in set (0.00 sec)

mysql> INSERT INTO `tb_user`(name,password,sex,age) VALUES ('testname8', '888', '8', 34);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql>

3.设置可重复读，未提交修改并查看:没有脏读；提交修改数据并查看：没有不可重复读

需要退出当前会话重新登陆

session1

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   30 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   30 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   30 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)





session2

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   30 |
+-------+----------+----------+------+------+
1 row in set (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tb_user set age=31 where id=10010;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   31 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

在session1中，如果在commit之前再继续执行UPDATE tb_user set age=age-1 where id=10010; 的话，此时age不是用30来计算的，而是session2中更新后的31来计算，保证了数据的一致性。使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

4.设置可重复读，提交新增数据，理论上应该出现：幻读；事实上MySQL可重复读已经解决了幻读

文档参考

MySql隔离级别：RU / RC / RR / S + 脏读 / 不可重复读 / 幻读 / 可重复读 - yifanSJ - 博客园 (cnblogs.com)

MySQL 可重复读隔离级别，彻底解决幻读了吗？ - 知乎 (zhihu.com)

INSERT INTO `tb_user`(name,password,sex,age) VALUES ('testname2', '222', '3', 27);

session1

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   31 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   31 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   31 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> SELECT * from tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   31 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)





session2

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| 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 tb_user;
+-------+----------+----------+------+------+
| id    | name     | password | sex  | age  |
+-------+----------+----------+------+------+
| 10010 | testname | 111      | 2    |   31 |
+-------+----------+----------+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO `tb_user`(name,password,sex,age) VALUES ('testname2', '222', '3', 27);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
+-------+-----------+----------+------+------+
2 rows in set (0.00 sec)

设置可重复读，模拟幻读：session2新增；同时session1修改 session2新增的数据；是可以修改成功的,此时就类似于幻读

（insert操作是因为mvcc机制和间隙锁，数量上看到的不一样）因为这种特殊现象的存在，所以我们认为 MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。

session1

 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| 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 tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
+-------+-----------+----------+------+------+
4 rows in set (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
+-------+-----------+----------+------+------+
4 rows in set (0.00 sec)

mysql> update tb_user set name='testname555' where age=30;
ERROR 1406 (22001): Data too long for column 'name' at row 5
mysql> update tb_user set name='testname6' where age=30;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname6 | 555      | 5    |   30 |
+-------+-----------+----------+------+------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname5 | 555      | 5    |   30 |
+-------+-----------+----------+------+------+
5 rows in set (0.00 sec)




session2

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.02 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
+-------+-----------+----------+------+------+
4 rows in set (0.00 sec)

mysql>  INSERT INTO `tb_user`(name,password,sex,age) VALUES ('testname5', '555', '5', 30);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname5 | 555      | 5    |   30 |
+-------+-----------+----------+------+------+
5 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

5.设置可串行化，新增数据，理论上解决：幻读；第一个事务未提交第二个事务阻塞无法执行；（读写都串行化）

session1

mysql> SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname5 | 555      | 5    |   30 |
| 10015 | testname6 | 666      | 6    |   32 |
+-------+-----------+----------+------+------+
6 rows in set (0.01 sec)

mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 


session2


mysql> SET SESSION TRANSACTION ISOLATION LEVEL Serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'tx_isolation';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from tb_user where age>=27;
+-------+-----------+----------+------+------+
| id    | name      | password | sex  | age  |
+-------+-----------+----------+------+------+
| 10010 | testname  | 111      | 2    |   31 |
| 10011 | testname2 | 222      | 3    |   27 |
| 10012 | testname3 | 333      | 1    |   28 |
| 10013 | testname4 | 444      | 4    |   29 |
| 10014 | testname5 | 555      | 5    |   30 |
| 10015 | testname6 | 666      | 6    |   32 |
+-------+-----------+----------+------+------+
6 rows in set (0.00 sec)

mysql> INSERT INTO `tb_user`(name,password,sex,age) VALUES ('testname7', '777', '7', 33);
Query OK, 1 row affected (13.69 sec)

mysql>