mysql 之事物详解 一

mysql详解

一. 事务的概念

事务：一条或多条sql组成的一个基本操作数据库的逻辑单元，是一个不可分割的工作单位，要么全部执行成功，要么全部执行失败。
注释：：MYSQL的myisam和innodb引擎中，仅innodb引擎支持事务，myisam不支持事务。

二.事务的四特性（ACID）

A.原子性（Atomicity）

作为逻辑工作单元，一个事务里的所有操作的执行，要么全部成功，要么全部失败。

B.一致性（Consistency）

一致性是对数据可见性的约束，保证在一个事务中的多次操作的数据中间状态对其他事务不可见的(可通过隔离级别实现)。因为这些中间状态，是一个过渡状态，与事务的开始状态和事务的结束状态是不一致的.

I. 隔离性（Isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

D：持久性（Durability）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。必须落盘（存储到服务器二进制文件中），即使服务器宕机了，重启后已经完成的事务还是有效的。

三.事务的隔离级别

事物隔离级别	脏写	脏读	不可重复读	幻读
读未提交(read uncommited)		可能	可能	可能
读已提交 (read commited)			可能	可能
可重复读(repeatable read)				可能(Innodb 已经解决了幻读情况)
可串行化(serializable)

注释：
1.未提交读（read-uncommitted）：在一个事务中，可以读取到其他事务未提交的数据变化，这种读取其他会话还没提交的事务，叫做脏读现象，在生产环境中切勿使用。
2.已提交读（read-committed）：在一个事务中，可以读取到其他事务已经提交的数据变化，这种读取也就叫做不可重复读，因为两次同样的查询可能会得到不一样的结果。
3.可重复读（repetable-read）:MySQL默认隔离级别，在一个事务中，直到事务结束前，都可以反复读取到事务刚开始时看到的数据，并一直不会发生变化，避免了脏读、不可重复读现象，但是它还是无法解决幻读问题(mysql innodb 在该级别已经解决了幻读的情况)。
4.可串行化（serializable)：这是最高的隔离级别，它强制事务串行执行，避免了前面说的幻读现象，简单来说，它会在读取的每一行数据上都加锁，所以可能会导致大量的超时和锁争用问题。
串行隔离级别：每个select 语句为默认加上共享锁。

4.可串行化（serializable）

1.脏写

第一种情况（回滚覆盖，导致丢失更新）：

事物A回滚数据，导致事物B的修改被回滚数据覆盖(丢失更新)
如下一张表 table01

id	compangy	money
1	上海xx公司	100

事物A	事物B
start transaction	start transaction
read(money) = 100
	read(money) = 100
	update(money)=money+60
update(money)=money-20	commit
rollback

事物A 和事物B都开启事物（并非同时）且事物有交叉，事物A读moeny为100,事物B读取moeny为100 并且加60提交后，事物A 减20 ，并且因某种原因事物回滚，回滚到事物A开始阶段 money = 100,事物A和B结束后，moeny为100，事物B的操作无效。

注释：所有的加减操作，必须在sql update中运行非应用程序中运算。
事物B：
update table01 set moeny= moeny+100 where id = 1
事物A：
update table01 set moeny= moeny-20 where id = 1

第二种情况（提交覆盖，导致丢失更新）：

如下一张表 table02

id	compangy	money
1	上海xx公司	100

事物A	事物B
start transaction	start transaction
read(money) = 100
	read(money) = 100
	update(money)=money+60
update(money)=money-20	commit
commit

事物A 和事物B都开启事物（并非同时）且事物有交叉，事物A读moeny为100,事物B读取moeny为100 并且加60提交后，事物A 减20 ，并且提交事物。由于事物A在事物B后提交，所以事物A 100-20 = 80 ，提交后数据moeny 变为80.导致事物B的修改丢失。(结果应该为money=140)

注释：所有的加减操作，必须在sql update中运行非应用程序中运算。
事物B：
update table02 set moeny= moeny+100 where id = 1
事物A：
update table02 set moeny= moeny-20 where id = 1

第三种情况(程序中计算，导致事物更新丢失)：

如下一张表 table03

id	compangy	money
1	上海xx公司	100

事物A	事物B
start transaction	start transaction
read(money) = 100
	read(money) = 100
	update(money)=160（程序中计算：money+60）
read(money)=80 (程序中计算：moeny-20)	commit
commit

事物A 和事物B都开启事物（并非同时）且事物有交叉，事物A读moeny为100 存在变量a中,事物B读取moeny为100 存在变量b中 并且b+60,执行update table03 set moeny=160 where id = 1 提交后，事物A a-20，执行update table03 set money = 80 ，并且提交事物。由于事物A在事物B后提交，所以事物A 100-20 = 80 ，提交后数据moeny 变为80.导致事物B的修改丢失。

注释：该情况所有的计算操作都在程序中运算，只对运算结果，对数据库执行简单的update 操作。

脏写总结：
第一种情况和第二种情况，mysql数据库已经帮我们解决，无论哪种隔离级别，都不会出现上述因事物交叉执行某事物回滚或提交而影响到另外一个事物的执行结果。

第三种情况由于是在程序中执行运算，出现数据共享或者是事物并发的时候，需要其他手段来控制，非数据库层面解决的问题。

针对第三种情况解决方案：
1.悲观锁

事物A和事物B 在读取id=100的时候，加上排他锁 select * from table03 where id =1 for update ,
谁先强占id=1的排他锁，谁先开始执行业务代码，等提交后释放排它锁才能执行另外一个事物。也就避免事物A和事物B读到的数据都是100情况。

2.乐观锁
a(旧值比较):

事物A 执行：update table03 set moeny = 80 where money = 100 and id = 1
事物B 执行：update table03 set moeny = 160 where moeny = 100 and id=1
结果：很明显事物先执行并提交，当事物A在执行更新语句的时候，返回 影响条数为0，也就是更新无效，并为影响事物B的执行。

b(版本号)

数据库中冗余一个字段version 版本号。 初始值为0
事物A执行时，先读取版本号
select version from table03 where id =1
同理事物B也先读取版本号
事物A 执行：update table03 set moeny = 80 ，version=version+1 where id =1 and 0 =version
事物B 执行：update table03 set moeny = 160 ，version=version+1 where id =1 and 0 =version
很明显事物先执行并提交，当事物A在执行更新语句的时候，返回 影响条数为0，也就是更新无效，并为影响事物B的执行。

注释:在电商中，防止商品超卖的情况，可通过，简单处理【update & select 合并】（乐观锁b）来解决update s_store set amount = amount - #{num} where amount>=#{num} and goodId= 12345
或者 【先select 后 update】（乐观锁a）update s_store set amount = amount - #{num}，version = version+1 where version=#{version} and goodId= 12345’ ，a锁用在这里虽然解决了超卖会导致大量扣库存失败。一般电商秒杀，不会放在数据库中解决。

2.脏读

第一种情况(读到未提交且回滚掉的数据，导致脏读):

如下一张表 table01

id	compangy	money
1	上海xx公司	100

事物A	事物B
start transaction	start transaction
update(money) = money+20
	read(money) = 120
	拿到moeny 120 做其他操作（此时读到120 是不一定准确）
rollback(money) = 100
	commit

注释：注意存在并发操作的数据（共享数据），是不能够放在程序中计算的，应该放在数据库中update 中做操作，因为数据库中有行锁控制

第二种情况非事务并发引起的脏读(多个账号操作共享数据导致的):

如下一张表 table02

id	compangy	money
1	小张	9000

业务场景：老板说，小张工资为9000，有点低要求财务人员给小张，涨薪水，由于财务人员都可以操作改张欣并且各自都有自己的账号。

财务人员A	财务人员B
点击查看，小张工资为9000,
当要修改加1000的时候，正好有事离开
	点击查看，小张工资为9000，还为加薪水
	给小张加薪水1000元，小张工资目前为10000
办事回来后，继续操作，给小张涨薪1000
小张看到工资涨薪2000，很开心，非常感谢老板，感觉老板很看重自己

注释：第一种情况，是事务并发的时候导致的，可通过事务隔离级别解决，第二种情况事务并没有发生交叉执行，而是以前以后提交的，这种情况可对数据增加一个version 版本号控制，查询的时候，查询训下版本，版本号相同才能修改，也就是乐观锁。

3.不可重复读

如下一张表 table01

id	compangy	money
1	上海xx公司	100

事物A	事物B
start transaction	start transaction
	read(money) = 100
update(money)=moeny+100
commit
	read(money) = 200
	commit

注释：事务B 两次读取同一条数据，结果不一样，这种情况为不可重复读。

4.幻读

如下一张表 table01

id	compangy	money
1	上海xx公司	100

事物A	事物B
start transaction	start transaction
	read(compangy) = 上海xx公司 查询一条数据
insert(compangy)=上海xx公司
commit
	read(compangy) = 上海xx公司 查询两条数据
	commit

注释：事务B 两次读取条数不一样，好像发生了幻觉。

不可重复读和幻读到底有什么区别呢？
（1）
可重复读针对的是：读一条数据，

（2）

重点：https://www.jianshu.com/p/47e6b959a66e