https://blog.csdn.net/h294590501/article/details/80386000
事务的隔离级别
事务的四大特性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次查询总数不同的情况。
---------------------
作者:h294590501
来源:CSDN
原文:https://blog.csdn.net/h294590501/article/details/80386000
版权声明:本文为博主原创文章,转载请附上博文链接!