[TOC] ## 事务的概念 事务：把所有的操作当中一个整体，要么全部成功，要么全部失败 tn2> 一旦开启了事务所有的操作都是临时的，你可以选择提交或者回滚 提交事务：全部成功 回滚事务：全部失败 ##### 事务四大特性（ACID） tn2> 事务具有原子性，一致性，隔离性，持久性 Atomicity,Consistency,Isolation,Duration A 原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。 C 一致性：事务把数据库从一个一致状态带入到另一个一致状态，事务结束的时候，所有的内部数据都是正确的。 I 隔离性：并发多个事务时，一个事务的执行不受其他事务的影响。 D 持久性：事务提交之后，数据是永久性的，不可再回滚，不受关机等事件的影响。 ##### 事务能解决什么问题 tn4>有些操作并不是一步就能搞定的。但是它又不允许这些步骤中一些成功一些失败。需要保证他们，要么全部成功，要么全部撤销。比如转账，转账失败不可怕，重新转账一次即可，比如转账超时，账户冻结等，可怕的是数据不一致，A账户减去了1000，而B账户又没有加上的情况。如果一些操作能够允许成功一部分，失败一部分，那么不使用事务也没有关系，效率还快一点。 比如转账的操作简化成3步：我们能保障他要么全部执行成功要么全部执行失败，保证数据的完整性和一致性等。让这些分开的步骤当中一个统一的不开分隔的操作去执行。 ```sql /* 转账操作简化步骤 */ ## 1：查询余额是否转账的金额 ## 2：A账户减去1000 ## 3：B账户增加1000 ## ....... ``` 又或者网上购物下单也是一个整体。我们都需要保证这些数据的一致性。使用的情况非常多，所以事务也是会使用比较频繁的。 ```sql /* 网上购物下单也是一个整体，如果一些成功一些失败就会产生bug,造成数据不一致 */ ## 购买一个商品 ## 对方订单增加一条 ## 库存要减去对应的购买数量 ## ........ ``` 开启了事务，相当于我们多了一个控制它的机会，可以选择或者是回滚还是提交！ ## 模拟转账操作 测试表： ``` create Table Account ( Id int primary key auto_increment, MyMoney int, AccountNumber varchar(64) ) select *from Account insert Account values(default,2000,'小红'); insert Account values(default,2000,'小芳'); -- 恢复数据 update Account set MyMoney =2000 ``` 一般转账的步骤是： tn2> 1：检查账户A的余额是否充足，也就是余额是否大于等于转账的金额 2：账户A减少转账的金额 3：账户B增加转账添加 #### 模拟转账操作 小红给小芳转账1000 #### 正常的情况 小红减少1000，小芳增加1000 ``` sql /* 模拟转账操作 小红给小芳转账1000 */ -- 1：查询小红的余额 select * from Account where accountNumber = '小红'; -- 2: 将小红余额减去1000 update Account set mymoney = mymoney-1000 where accountNumber = '小红'; -- 3：将小芳余额加上1000 update Account set mymoney = mymoney+1000 where accountNumber = '小芳'; ``` #### 测试非正常情况 在2，3步骤之间随便弄一点错误出来即可就会出现，小红减去了钱，而小芳还没有添加上的情况。 ```sql /* 模拟转账操作 小红给小芳转账1000 */ -- 1：查询小红的余额 select * from Account where accountNumber = '小红'; -- 2: 将小红余额减去1000 update Account set mymoney = mymoney-1000 where accountNumber = '小红'; -- 随便让一句报错的 int i = 10/0 -- 3：将小芳余额加上1000 update Account set mymoney = mymoney+1000 where accountNumber = '小芳'; ``` 结果如下：可以看到明显数据不正常  **所以我们应该把这些操作都放到一个事务当中去执行** 其实上面的操作本身是有用事务的，mysql是默认有事务的，默认是一条语句一个事务，执行完成后自动提交，不需要手动提交。oracle默认事务是需要手动添加的，不然一边的数据添加了没有添加，另外一边是不会看到变化的，当然这些默认情况是和版本有关系的。 ## 简单事务的操作 ### 通常的使用步骤 涉及到的步骤就是，开启事务，提交事务，或者回滚事务 tn3> 1：开启事务 begin 或者start transaction [事务名称] 2：提交事务 commit [transaction 事务名称] 3：回滚事务 rollback [transaction 事务名称] sqlserver中事务语法也类似可以简写成trans，比如 tn3> 1：开启事务 begin trans/transaction 事务名称 2：提交事务 commit trans/transaction 事务名称 3：回滚事务 rollback trans/transaction 事务名称 ### 事务的控制方式又分为自动与手动 其实上面的转账操作本身是有用事务的，mysql是默认有事务的，默认是一条语句一个事务，执行完成后自动提交，不需要手动提交。oracle默认事务是需要手动添加的，不然一边的数据添加了没有添加，另外一边是不会看到变化的，当然这些默认情况是和版本有关系的。 #### 查询事务的提交方式： ``` select @@autocommit; ``` tn3> 结果有两种，如果为1就是自动提交，如果为0就是手动提交。这里查询出来是1默认就是自动提交。 #### 设置事务的提交方式： ``` set @@autocommit = 0; -- 设置成手动提交 set @@autocommit = 1; -- 设置成自动提交 ``` 这里说一下，上面的方式其实是一种临时的方式，换一个连接窗口就恢复了，如果要所有设置都生效也就是全局设置，可以使用如下的方式： ``` SET autocommit = 0|1|ON|OFF; ``` 配合的查询是： ``` SHOW VARIABLES LIKE 'autocommit'; ``` 其实也就是全局变量(global)，会话变量（session）的区别。 #### 使用手动提交的方式进行事务控制 先设置成手动提交 ``` set @@autocommit = 0; -- 设置成手动提交 ``` **如果设置成手动提交，我们的单条sql语句操作也可以进行回滚** 因为没有执行commit之前，操作都是临时的我们随时可以进行数据的回滚。 比如我们执行删除账户表：来模拟不小心删除数据忘记接条件了 ``` delete from Account ``` 我们只需要执行一下rollbak数据就会回来了。而且我们执行删除后在其他连接下看到的数据是没有被删除的，除非我们调用commit数据才会被真正的提交。测试的时候可以多开几个连接来测试，比如两个navicat下的查询窗口加一个控制台下的查询。 **然后我们就可以把上面模拟转账的来进行事务控制** 模拟正常情况下，我们直接commit提交即可，转账会成功： ``` /* 模拟转账操作 小红给小芳转账1000 */ -- 1：查询小红的余额 select * from Account where accountNumber = '小红'; -- 2: 将小红余额减去1000 update Account set mymoney = mymoney-1000 where accountNumber = '小红'; -- 3：将小芳余额加上1000 update Account set mymoney = mymoney+1000 where accountNumber = '小芳'; commit; ``` 模拟异常情况下，我们直接rollbak回滚即可(这里我们需要手动执行rollback进行测试)，转账会失败： ``` /* 模拟转账操作 小红给小芳转账1000 */ -- 1：查询小红的余额 select * from Account where accountNumber = '小红'; -- 2: 将小红余额减去1000 update Account set mymoney = mymoney-1000 where accountNumber = '小红'; -- 随便让一句报错的 int i = 10/0 -- 3：将小芳余额加上1000 update Account set mymoney = mymoney+1000 where accountNumber = '小芳'; rollback; ``` tn4> 正常情况下提交事务，异常情况下回滚事务，这样我们就可以保证数据的正确性和完整性了。当然这里只是模拟演示手动提交模式下的事务控制方法，真实情况下我们需要配合存储过程，函数等去把各种情况的验证写完整。 #### 使用自动提交的方式进行事务控制 设置成自动提交(本身就是事务的默认行为) ``` set @@autocommit = 1; -- 设置成自动提交(本身就是事务的默认行为) ``` tn4> 这种方式下，单条sql语句执行完成后会自动提交事务，所以我们需要使用 begin/start transaction手动开始事务 转账的简单示例：使用start transaction这些也非常简单，自己控制事务即可 ``` /* 模拟转账操作 小红给小芳转账1000 */ -- 开始事务 start transaction; -- 1：查询小红的余额 select * from Account where accountNumber = '小红'; -- 2: 将小红余额减去1000 update Account set mymoney = mymoney-1000 where accountNumber = '小红'; -- 随便让一句报错的 int i = 10/0 -- 3：将小芳余额加上1000 update Account set mymoney = mymoney+1000 where accountNumber = '小芳'; -- 回滚事务 rollback; -- 提交事务 commit; ``` ## 事务的并发问题 事务的常见并发问题分为：脏读，不可重复读，幻读。这小节只是面试这些问题，下个小节有详细的步骤演示与解决办法。  ### 脏读 脏读模拟： tn2>第1步：事务A随便查询一下表先看看当前的数据 第2步：事务B去修改一条数据，并且不提交数据 第3步：事务A在去查询一下数据，会发现数据已经被修改了。读取到另外一个事务没有提交的数据这个就是脏读。 脏读的模拟其实很简单，一个事务去查询另外一个没有提交的事务就行了。 ### 不可重复读 不可重复读的意思就是在一个事务中，读取多次会出现数据不一致的情况。 比如事务在第1步读取了数据，然后在第三步又读取一次，数据就变化了。因为这个期间有其他事务更改了这个数据并提交了。 如果要解决这个问题就要把他变成"可重复读"，也就是一个事务中读取n次数据都要一样。虽然有时候即使一个事务中出现多次读取结果不一样也无所谓。 ### 幻读 幻读模拟： tn2>第1步：事务A查询id=1的数据提示不存在 第2步：事务B去添加一条id=1的数据 第3步：因为第1步查询没有id=1的数据所以尝试添加id=1的数据，结果添加不了提示重复id 第4步：在查询一次以确定是不是数据真的不存在于是在查询id=1的，结果还是没有。这就是幻读！明明有但是查询不出来，添加又添加不了的尴尬情况。就像问一个女生有没有男朋友她说没有，但是她又已经有男朋友了。 幻读就是数据插入的时候无法插入，提示已经存在了。但读取又无法读取的情况是一种错觉。明明插入时主键冲突，却又读不出来，就造成的幻觉。 幻读是在解决了不可重复读后引发出来的问题，所以A事务的第1步和第3步查询结果要一致，不然问题还留在不可重复读这个层面上。 **哈哈，来贴一下画得比较粗糙的图**  ## 数据库隔离级别数据  tn3>Read Uncommitted（读取未提交内容）：基本没啥用 什么都不能限制 Read Committed(读取提交内容):大部分数据库的默认级别，只能看到已提交的改变（避免脏读） Repeatable Read(可重复读)：MySql的默认级别，同一事务并发时 保证读取数据行一样（避免脏读与重复读） Serializable(串行化)：最高级别 但是性能差，锁竞争（避免脏读、重复读、幻读）。就像玩游戏一样，一个怪只能同时和一队玩家战斗，其他玩家的队伍都得等待，比如梦幻西游的五行斗法，所以会经常堵车 隔离级别越高，效率越慢。 所以在选择事务隔离级别的时候要权限数据安全性和效率。如果业务逻辑允许脏读，不可重复的这些，我们完全可以把数据库隔离级别设置得很低，让效率更高。 tn3>其实通过这些单词也好理解意思的。比如Read Uncommitted就是能够读取未提交的嘛，Read Committed是能够读取提交的嘛，反正就是可以读取不管是否是提交的，就会造成事务的两种并发情况。 ### 查询事务的隔离级别 ```sql select @@transaction_isolation -- 其他方法 show VARIABLES like 'trans%' select @@session.transaction_isolation select @@global.transaction_isolation ``` ### 设置事务的隔离级别 ```sql set session transaction isolation level read uncommitted set session transaction isolation level repeatable read ``` ### 演示脏读Read Uncommitted 也就是可以读取另外一个事务没有提交过的数据。复现步骤其实上面也说过。 如下图： 第1步先设置一下，事务的隔离级别为允许脏读。此时会出现脏读，不可重复读，幻读。 ``` /* 设置事务的隔离级别为read uncommitted。此时会出现脏读，不可重复读，幻读 set session transaction isolation level read uncommitted */ ```  ### 解决脏读，演示不可重复读 设置事务的隔离级别为read committed。 此时不会出现脏读，但是会出现不可重复读与幻读。 ``` /* 设置事务的隔离级别为read commited。这种情况效率次之，不会出现脏读，但是会出现不可重复读与幻读 */ set session transaction isolation level read committed ```  ### 解决不可重复读 设置事务隔离级别为REPEATABLE-READ 。 此时事务不会出现脏读，也不会出现不可重复读，但是会出现幻读。 ``` /* 设置事务隔离级别为REPEATABLE-READ 。 此时事务不会出现脏读，也不会出现不可重复读，但是会出现幻读 */ set session transaction isolation level REPEATABLE READ ```  ### 演示幻读  ### 解决幻读 设置事务隔离级别为SERIALIZABLE 。 此时事务不会出现脏读,不可重复读，幻读 ``` /* 设置事务隔离级别为SERIALIZABLE 。 此时事务不会出现脏读,不可重复读，幻读*/ set session transaction isolation level SERIALIZABLE ```