事务的隔离级别

事务的隔离级别

事务的四大特性ACID

事务特性	含义
原子性（Atomicity）	每个事务都不能再拆分，如果事务中有多条语句，它们应该是一个整体。要么全部成功，要么全部失败。
一致性（Consistency）	事务执行前与事务执行后，数据库的状态应该是一致的。如：转账操作，转账前前与转账后两个人的总金额应该是一样的。
隔离性（Isolation）	如果同时有多个事务在数据库中执行，事务与事务之间应该互不影响。
持久性（Durability）	事务一旦提交，对数据库的影响是持久的，就算关闭服务器。数据也是持久**存在。**

事务的隔离级别

事务在操作时的理想状态：事务与事务之间不会有任何影响

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题

MySQL数据库有四种隔离级别

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

MySQL事务隔离级别相关的命令

查询全局事务隔离级别
​
查询隔离级别 
-- select @@tx_isolation;
​
设置事务隔离级别，需要退出MySQL再重新登录才能看到隔离级别的变化
​
设置隔离级别 
-- set global transaction isolation level 级别字符串;  

转账的操作

-- 创建数据表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('Jack', 1000), ('Rose', 1000);

脏读的演示

将数据进行恢复：UPDATE account SET balance = 1000;
​
打开A窗口登录MySQL，设置全局的隔离级别为最低
​
mysql -uroot -proot
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
​
打开B窗口,AB窗口都开启事务
USE db;
START TRANSACTION;
​
A窗口更新2个人的账户数据，未提交
UPDATE account SET balance=balance-500 WHERE id=1;
UPDATE account SET balance=balance+500 WHERE id=2;
​
​
B窗口查询账户
SELECT * FROM account;
​
​
A窗口回滚
ROLLBACK;
​
​
B窗口查询账户，钱没了 
SELECT * FROM account;
​
脏读非常危险的，比如Jack向Rose购买商品，Jack开启事务，向Rose账号转入500块，
然后打电话给Rose说钱已经转了。
Rose一查询钱到账了，发货给Jack。
Jack收到货后回滚事务，Rose的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升

将数据进行恢复：
UPDATE account SET balance = 1000;
​
在A窗口设置全局的隔离级别为*READ COMMITTED*
​
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
​
​
B窗口退出MySQL，B窗口再进入MySQL 
​
​
AB窗口同时开启事务 !
​
​
A更新2个人的账户，未提交
UPDATE account SET balance=balance-500 WHERE id=1;
UPDATE account SET balance=balance+500 WHERE id=2;
​
​
B窗口查询账户
​
​
A窗口commit提交事务
COMMIT;
 
B窗口查看账户 
​
​
​
结论：read committed的方式可以避免脏读的发生却有不可重复读的问题

不可重复读的演示

将数据进行恢复：
UPDATE account SET balance = 1000;
​
开启A窗口
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
​
​
​
开启B窗口，在B窗口开启事务
​
START TRANSACTION;
SELECT * FROM account;
​
​
在A窗口开启事务，并更新数据
​
START TRANSACTION;
UPDATE account SET balance=balance+500 WHERE id=1;
COMMIT;
​
B窗口查询
​
SELECT * FROM account;
​

两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 
很多人认为这种情况就对了，无须困惑，当然是后面的为准。
我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，
结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，
银行工作人员就不知道以哪个为准了。

解决不可重复读的问题：

将全局的隔离级别进行提升为：repeatable READ 
​
将数据进行恢复：
​
UPDATE account SET balance = 1000;
​
A窗口设置隔离级别为：*REPEATABLE READ*
​
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
​
​
​
B窗口退出MySQL，B窗口再进入MySQL
​
START TRANSACTION;
SELECT * FROM account;
​
​
​
A窗口更新数据
​
START TRANSACTION;
UPDATE account SET balance=balance+500 WHERE id=1;
COMMIT;
​
​
B窗口查询
​
SELECT * FROM account;
​
​
​
结论：同一个事务中为了保证多次查询数据一致，必须使用*REPEATABLE READ*隔离级别 

幻读的演示

我们可以将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：
​
UPDATE account SET balance = 1000;
​
开启A窗口
​
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置隔离级别为最高
​
​
​
A窗口退出MySQL，A窗口重新登录MySQL
​
START TRANSACTION;
SELECT COUNT(*) FROM account;
​
​
再开启B窗口，登录MySQL
​
在B窗口中开启事务，添加一条记录
​
START TRANSACTION; -- 开启事务
INSERT INTO account (NAME,balance) VALUES ('LaoWang', 500);
​
​
在A窗口中commit提交事务，B窗口中insert语句会在A窗口事务提交后立马运行 
​
在A窗口中接着查询，发现数据不变
​
SELECT COUNT(*) FROM account;
​
​
B窗口中commit提交当前事务
​
A窗口就能看到最新的数据
​
 
​
结论：使用serializable隔离级别，一个事务没有执行完，其他事务的SQL执行不了，可以挡住幻读

练习

1. 脏读的操作：
 1) 打开命令行Jack，设置全局的隔离级别为最低：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
​
 2) 开启事务
 USE db;
 START TRANSACTION;
​
 3) 更新2个人的账户，未提交
 UPDATE account SET balance=balance-200 WHERE NAME='Jack';
 UPDATE account SET balance=balance+200 WHERE NAME='Rose';
​
 4) 打开另一个命令行Rose，查询账户
 USE db;
 SELECT * FROM account;
 发现钱已经到账，发货
​
 5) 命令行Jack回滚
ROLLBACK;
​
 6) 命令行Rose，查询账户，钱没了。  
​
2. 解决脏读的问题：将全局的隔离级别进行提升
 1) 打开命令行Jack，设置全局的隔离级别为read committed：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 
​
 2) 重复上面的操作：需要重新登录。
会发现命令行Jack在没有提交和回滚之前，命令行Rose看不到账户发生任何变化。
​
 3) 命令行Jack，使用commit提交以后
命令行Rose，可以看到账户发生了变化。
​
 4) 结论：read committed的方式可以避免脏读的发生。
​
3.  出现不可重复读的问题
 1). 将数据进行恢复，并关闭窗口重新登录。
UPDATE account SET balance=500;
​
 2) 开启1个银行账户窗口
SELECT @@global.tx_isolation; -- 确保当前的事务隔离是read committed
​
 3) 在银行窗口中开启一个事务
USE db;
START TRANSACTION;
查询用户Jack的账户，输出到屏幕。
SELECT * FROM account WHERE NAME='Jack';  -- 查到是500块
​
 4) 开启1个Jack账户窗口
-- 开启一个事务：
USE db;
START TRANSACTION
更新账户，加100元
UPDATE account SET balance=balance+100 WHERE NAME='Jack';
提交事务
COMMIT;
​
 5) 银行窗口在同一个事务中再次查询Jack的账户，输出到文件中：
SELECT * FROM account WHERE NAME='Jack';  -- 查到是600块
COMMIT;  -- 事务结束
两次查询输出的结果不同，到底哪次是对的？
​
4. 解决：不可重复读的问题
 1). 将数据进行恢复
UPDATE account SET balance=500;
设置隔离级别为repeatable READ  
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
SELECT @@global.tx_isolation;  -- 查询隔离级别
​
 2) 重新登录
银行窗口登录
USE db;
START TRANSACTION;
SELECT * FROM account WHERE NAME='Jack';-- 查询到500
​
 3) Jack窗口登录
USE db;
START TRANSACTION;
SELECT * FROM account WHERE NAME='Jack';
UPDATE account SET balance=balance+100 WHERE NAME='Jack';
COMMIT;  -- 提交事务
SELECT * FROM account WHERE NAME='Jack'; -- 这时Jack看到的是600
​
 4) 银行窗口再查
SELECT * FROM account WHERE NAME='Jack'; -- 查询还是500，很好的体现了事务的隔离性。
COMMIT;-- 2次查询结果相同
​
 5) 银行提交当前事务
COMMIT;
再开启一个新的事务
START TRANSACTION;
SELECT * FROM account WHERE NAME='Jack';  -- 查询变成了600
COMMIT;
​
 6) 结论：为了保存多次查询数据一致，必须使用repeatable read隔离级别
​
5. 幻读的操作：
1) 开启一个银行窗口
USE db;
UPDATE account SET balance=500;  -- 还原数据
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- 设置隔离级别为最高
SELECT @@global.tx_isolation;  -- 显示事务的隔离级别
​
 2)  关闭银行窗口，重新登录
START TRANSACTION;   -- 开启事务
SELECT COUNT(*) FROM account;  -- 查询一共有多少个账户,可以多次查询
​
 3) 开启另一个New窗口
USE db;
START TRANSACTION; -- 开启事务
INSERT INTO account (NAME,balance) VALUES ('New', 500);  
-- 这时会发现这个操作无法进行，除非银行窗口中的事务结束
​
 4) 在银行窗口中提交事务
COMMIT;
则New窗口中insert语句运行
​
 5)New窗口中要commit或rollback，完成当前事务。
银行窗口就能看到最新的数据。
否则就会出现银行窗口中2次查询总数不同的情况。