mysql试验分析事务隔离级别_mysql验证事务隔离级别-CSDN博客

本文链接：https://blog.csdn.net/u010476739/article/details/97641066

参考：http://www.zsythink.net/archives/1233
记住自己的事物的隔离级别是什么就可能引发什么问题，和别人的隔离级别没关系

一、概念引入：

1.1 为什么会有事务，什么是事务？

解释：

以银行转账为例：A要转账给B有两步：

从A账户减去转账数目，
向B账户增加转账数目
那么为了防止程序执行中只执行了一步，所以要在数据库层次保证这两步要么一起执行，要么都不执行，即把它们看成一个整体也就是一个事务。

1.2 什么是事务的隔离级别，为什么会有隔离级别？

解释：

其实这是为了解决并发问题产生的。如果每次都只有一个人操作数据库，就不会有什么隔离级别了，但这是不可能的。
那么在并发的情况下如何保证互不影响的，这就是隔离级别了。
mysql中的四个隔离级别从低到高以此为：读未提交<读已提交<可重读<串行化
1.读未提交：并发度很高，但是数据隔离的非常不好，会出现脏读数据（个人认为不应该有这种隔离级别，oracle中没这种，但是mysql和sqlserver中都有）。
2.读已提交：并发度还行，数据隔离的也还行，但就是我开启事务后别人对数据的操作还会影响到我（个人认为应当如此，sqlserver的默认级别也是这个）。
3.可重读：并发度还行，数据隔离的还行，但就是会出现幻觉（个人感觉这个幻觉要不得，但是这个是mysql的默认隔离级别）。
4.串行化：并发度很差，数据隔离的非常好（实际中不会用，并发度太低了，事务中一条查询语句都可以将这个表锁死）。

1.3 事务特性是什么

解释：

四大特性：
1.原子性即要把一个事务中的sql语句看成是一个整体，要么一起执行要么都不执行
2.一致性类似原子性，也是为了说明事务是一个整体
3.隔离性就是上面说的事务隔离级别
4.持久性其实这和一般的sql语句是一样的
可以看到最终要的特性就是原子性

二、mysql事务操作命令

准备的数据库：test
数据库引擎：innodb
表：t

create table t(
    id int primary key,
    name varchar(50)
);
insert into t values(1,45);

2.1 显示全局事务是否默认提交

mysql> show global variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

2.2 显示当前会话事务是否默认提交

mysql> show session variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

2.3 显示当前的事务隔离级别

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

2.4 关闭当前会话的默认事务提交

mysql> set @@session.autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

2.5 查看准备的表数据

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
+----+------+
1 row in set (0.00 sec)

2.6 开启一个事务

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

2.7 插入数据

mysql> insert into t values(2,53);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  2 | 53   |
+----+------+
2 rows in set (0.00 sec)

2.8 回滚

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
+----+------+
1 row in set (0.00 sec)

2.9 插入数据

mysql> insert into t values(3,33);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
+----+------+
2 rows in set (0.00 sec)

2.10 创建事务节点a

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

2.11 插入数据

mysql> insert into t values(4,44);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

2.12 创建事务节点b

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

2.13 插入数据

mysql> insert t values(5,55);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
|  4 | 44   |
|  5 | 55   |
+----+------+
4 rows in set (0.00 sec)

2.14 回滚到事务节点a

mysql> rollback to a;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
+----+------+
2 rows in set (0.00 sec)

2.15 创建事务节点a

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

2.16 释放事务节点

mysql> release savepoint a;
Query OK, 0 rows affected (0.00 sec)

三、mysql事物隔离操作

数据库：test
表：t
表结构和数据：

create table t(
    id int primary key,
    name varchar(50)
);
insert into t values(1,11);
insert into t values(2,22);
insert into t values(3,33);

3.1 打开两个mysql客户端(A和B) 切换到test数据库的操作环境

3.2 查看默认的事务隔离级别（应为：REPEATABLE-READ[可重复读]）

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

或

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

3.3 试验隔离级别：READ-UNCOMMITTED[读未提交] （出现脏读，脏读数据都有了就不要再提不可重复读和幻读了）

3.3.1 将A端设置为读未提交（READ-UNCOMMITTED）

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

3.3.2 A端先查看下数据

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.3.3 B端开启事务并增删改数据但不提交

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

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

mysql> insert into t values(4,44);
Query OK, 1 row affected (0.01 sec)

mysql> delete from t where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> update t set name='22-2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.3.4 A端再次执行查询（出现脏读数据）

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

这里可以看到，由于A端的事务隔离级别较低，所以访问到了别人还没有提交的数据，这种现象称之为脏读。在oracle中直接认为这是一个错误，不提供这种隔离级别，个人认为应当如此。
另外：如果此时在A端执行更新语句update t set name='22-2-2' where id=2;会发现A端将处于等待状态，直到报错：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,这是因为这一行数据已经被B端锁定了(B端还未提交事务)。

3.3.5 将B端事务回滚

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.3.6 A端再执行查询（验证数据是否恢复到最初状态）

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

可以看到数据又恢复到了最初的模样

3.4 试验隔离级别：READ-COMMITTED[读提交] 不可重复读现象

3.4.1 A端将事务隔离级别设置为 READ-COMMITTED

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

3.4.1 A端开启事务并查看当前的数据

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.4.2 B端增删改数据但不提交

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

mysql> insert into t values(4,'44');
Query OK, 1 row affected (0.01 sec)

mysql> delete from t where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> update t set name='22-2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.4.3 A端查看数据（验证不会出现脏读数据）

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

由此可见A端并未查看到B端未提交的数据,即没有脏数据。
另外：如果此时执行update t set name='22-2-2' where id=2;会发现A端一直出现等待直到报错，同上，行数据被B端锁定了。

3.4.4 B端将事务提交

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.4.5 A端查看数据（验证出现不可重复度、幻读）

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

可以看到在A端的一个事务中的先后查到了不同的数据，包含增删改的数据，其中改的数据称之为不可重复读数据，增删的数据称之为幻读数据。

3.4.6 将表t删掉重建并插入数据，为后面做准备

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

mysql> drop table t;
Query OK, 0 rows affected (0.19 sec)

mysql> create table t(
    ->     id int primary key,
    ->     name varchar(50)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> insert into t values(1,11),(2,22),(3,33);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.5 试验隔离级别：REPEATABLE-READ[可重读] （会出现幻读现象）

3.5.1 将A端隔离级别设置为REPETABLE-READ开启事务并查看当下数据

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.5.2 B端开启事务并进行增删改并提交

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

mysql> insert into t values(4,'44');
Query OK, 1 row affected (0.01 sec)

mysql> delete from t where id=1;
Query OK, 1 row affected (0.00 sec)

mysql>  update t set name='22-2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.5.3 A端查看数据

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

到这里为止，数据的隔离看着还是正常的，本事务内的数据果然是可重复读的。但是接着往下看。。。

3.5.4 A端修改数据后再进行查看，提交后再进行查看

mysql> update t set name='88';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 88   |
|  3 | 88   |
|  4 | 88   |
+----+------+
4 rows in set (0.00 sec)

幻觉发生了：这个update针对的所有的数据，为什么第一行还显示的这么怪异，其实这就是幻读。接着提交这个事务：

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 88   |
|  3 | 88   |
|  4 | 88   |
+----+------+
3 rows in set (0.00 sec)

此时再看数据，发现是两个事务更改后的最后结果，但是上一步确实有幻觉，所以叫做幻读现象。

3.5.5 清理数据，恢复到最初状态

mysql> delete from t;
Query OK, 3 rows affected (0.05 sec)

mysql> insert into t values(1,11),(2,22),(3,33);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.6 试验隔离级别：SERIALIZABLE[串行化] （沒有脏读、没有不可重复读、没有幻读，但是数据库并发性能极低）

3.6.1 将A端事务隔离级别设为SERIALIZABLE，开启事务并查看当前数据

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.6.2 B端打开事务并进行数据操作

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

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into t values(4,'44');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到，数据能查就查看的到，但是增删改的操作都不能允许，这是因为A端的事务中用到了表t（虽然只是查询了一下）。
这样的数据更安全，但是由于操作串行，数据库的并发也很低。
试验到此结束，A、B端分别回滚事务即可。

四、总结

图1：
在这里插入图片描述

五、附

另外mysql中不支持嵌套的事务，因为执行start transaction的时候是默认提交上次的事务的，但是通过事务的保存点和回滚到指定的保存点也可以提供相同的功能。参考：https://my.oschina.net/yurun/blog/2247901
sqlserver中是直接支持嵌套事务的，参考：https://blog.csdn.net/qq_40205468/article/details/87785634