事务的四大特性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次查询总数不同的情况。