MYSQL数据库的事务

一. 事务引入

最经典的银行转账业务(A给B转100元)：
A账户减去100元：
update bank_acount set balance = balance - 100 where id = 1
B账户增加100元：
update bank_acount set balance = balance + 100 where id = 2
假如第一条语句执行之后，发生了异常情况，没有执行第二条语句，就会导致银行账户的不平衡，100块钱丢了，为了解决这个问题，我们引入了事务。

二. 事务定义

1. 官方定义

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

2. 通俗的讲

为了完成某项业务，需要执行多条sql语句，将这些sql语句绑在一起，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

三. 事务的四大特性

1. 原子性（Atomicity）

原子是最小单元，不能再分割。事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

2. 一致性（Consistency）

事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号金额之和应该是不变的。

3. 持久性（Durability）

一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

4. 隔离性（Isolation）

隔离性是指在并发操作中，不同事务之间应该隔离开，使每个并发中的事务不会相互扰。

四. 事务并发引起的问题

在事务并发时，如果没有采取必要的隔离措施，可能会导致各种并发问题，破坏数据的完整性等。这些问题中，其中有三类是读问题，分别是：脏读、不可重复读、幻读。

1. 脏读（dirty read）

读到另一个事务的未提交更新数据，即读取到了脏数据；

例如：A给B转账100元但未提交事务，在B查询后，A做了回滚操作，那么B查询到了A未提交的数据，就称之为脏读。

2. 不可重复读（unrepeatable read）

读到另一个事务对同一记录的已提交更新数据。对同一记录的两次读取不一致，因为另一事务对该记录做了修改（是针对修改操作）。解决方法：对数据多读几次，得到最新数据结果。

例如：在事务1中，前后两次查询A账户的金额，在两次查询之间，另一事物2对A账户的金额做了修改，此种情况可能会导致事务1中，前后两次查询的结果不一致。这就是不可重复度。

3. 幻读（虚读）（phantom read）

读到另一个事务对同一记录的已提交更新数据。对同一张表的两次查询不一致，因为另一事务插入了一条记录(是针对插入或删除操作)；

五. 事务隔离级别

事务隔离级别分四个等级，在相同数据环境下，对数据执行相同的操作，设置不同的隔离级别，可能导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力也是不同的。

1. READ UNCOMMITTED（读未提交数据）

安全级别最低, 可能出现任何事务并发问题(比如脏读、不可以重复读、幻读等)
性能最好（不使用!!）

2. READ COMMITTED（读已提交数据）

防止脏读，没有处理不可重复读，也没有处理幻读；
性能比REPEATABLE READ好

3. REPEATABLE READ（可重复读）

防止脏读和不可重复读，不能处理幻读问题；
性能比SERIALIZABLE好

4. SERIALIZABLE（串行化）

不会出现任何并发问题，因为它是对同一数据的访问是串行的，非并发访问的；
性能最差

MySQL的默认隔离级别为REPEATABLE READ，即可以防止脏读和不可重复读。
查询事务的隔离级别：select @@tx_isolation;

六. 解决方案

1. LBCC(Lock Based Concurrency Control)：基于锁的并发控制

在读取数据前，对其加锁，阻止其他事务对数据进行修改。

(1) 锁的分类

a. 共享锁：又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对同一个数据可以共享一把锁，都能访问到数据，但是只能读，不能修改。

加锁方式(加锁成功的标志是有查询结果)：

begin;
select * from bank_acount where id = 1 lock in share mode;
commit/rollback;

b. 排它锁：又称为写锁，简称X锁，排它锁不能与其他锁并存，如果一个事务获取了一个数据行的排它锁，其他事务就不能再获取该行的锁（共享锁，排它锁），只有获取了排它锁的事务可以对数据行进行读和写操作。

加锁方式：

自动加：delete/update/insert 默认加上X锁
手动加：select * from bank_acount where id = 1 for update;

举例：
事务1：张三这行数据默认是排它锁：

事务2：给张三这行数据加共享锁失败

事务3：给张三这行数据手动加排它锁失败

事务4：给张三这行数据加默认排它锁失败

c. 意向共享锁(Intention Shared Lock, 简称IS锁)：给一行数据加共享锁前，数据库会自动(数据库自己维护)在这张表上面加一个意向共享锁(可以理解为一种标记)。反过来说，如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。

d. 意向排它锁(Intention Exclusive Lock, 简称IX锁):给一行数据加上排他锁之前，数据库会自动(数据库自己维护)在这张表上面加一个意向排他锁。反过来说，如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

意向锁用途：如果说没有意向锁的话，当我们准备给一张表加上表锁的时候，是不是必须先要去判断有没其他的事务锁定了其中了某些行？？如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率是不是很低？如果有意向锁，只需要判断一下就可以了。

(2) 锁的算法

假如在bank_acount表中插入四条数据，主键id分别是1，4，7, 10

a. 记录锁：锁记录

第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。
注意：只命中了记录

b. 间隙锁：锁范围

第二种情况，无论是用等值查询还是范围查询的时候，只命中了区间，没有命中任何记录，它使用的都是间隙锁。落在哪个间隙上，哪个间隙就被锁住。
注意：只命中了范围，没有命中记录
例如:
select * from bank_acount where id = 2, 那么间隙(1, 4)会被锁住
select* from bank_acount where id > 20, 那么间隙(10, 正无穷)被锁住
注意：间隙锁主要是阻塞插入insert，为了防止幻读

c. 临键锁：锁定范围+记录

当我们用范围查询，不仅命中了区间同时还命中了记录，在这种情况下我们使用的就是临键锁，它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。
唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁。
没有匹配到任何记录的时候，退化成间隙锁。
注意：命中了记录+命中了范围
例如：
select * from bank_acount where id>5 and id<=7 锁住(4,7]和(7,10]

2. MVCC(Multi Version Concurrency Control)：多版本的并发控制

生成一个数据请求时间点的一致性数据快照(Snapshot)，并用这个快照来提供一定级别(语句级或者事务级)的一致性读取。

InnoDB为每行记录都实现了两个隐藏字段

DB_TRX_ID，6字节：插入或更新行的最后一个事务的事务ID，事务编号是自动递
增的（我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事
务ID）。
DB_ROLL_PTR，7字节：回滚指针（我们把它理解为删除版本号，数据被删除或记
录为旧数据的时候，记录当前事务ID）。

现在启动多个事务并发的对银行账户表做如下操作

通过MVCC分析上述操作过程

第一步

T1时间段，事务1开启事务，并给bank_acount表中插入张三和李四两条数据，并提交事务(假设当前事务的id为1)。

begin;
INSERT INTO `bank`.`bank_acount` (`id`, `name`, `balance`) VALUES ('1', '张三', '900');
INSERT INTO `bank`.`bank_acount` (`id`, `name`, `balance`) VALUES ('2', '李四', '50');
commit;

表中数据的隐藏列表（插入数据，创建版本号就是当前的事务id）：

第二步

T2时间段，事务2开启事务，并执行第一次查询bank_acount表中的数据，未提交事务(假设当前事务的id为2)。

begin;
#在事务内部进行第一次查询
select * from bank_acount;

查询结果必须满足的版本条件：

1.隐藏的创建版本号必须小于当前的版本号（说明是在当前事务之前插入的）
2.隐藏的删除版本号必须大于当前版本号（说明是在当前事务之后删除的，之前是存在的）

根据查询条件，张三和李四符合：

第三步

T3时间段，事务3开启事务，并给bank_acount表中插入李四一条数据，并提交事务(假设当前事务的id为3)。

begin;
INSERT INTO `bank`.`bank_acount` (`id`, `name`, `balance`) VALUES ('3', '王五', '200');
commit;

表中数据的隐藏列表（插入数据，创建版本号就是当前的事务id）：

第四步

T4时间段，事务2进行第二次查询bank_acount表中的数据，未提交事务(当前事务的id依然为2)。

begin;
#在事务内部进行第一次查询
select * from bank_acount;
#在事务内部进行第二次查询
select * from bank_acount;

查询结果必须满足的版本条件：

1.隐藏的创建版本号必须小于当前的版本号（说明是在当前事务之前插入的）
2.隐藏的删除版本号必须大于当前版本号（说明是在当前事务之后删除的，之前是存在的）

根据查询条件，张三和李四符合，王五不符合：

第五步

T5时间段，事务4开启事务，删除李四，并提交事务(假设当前事务的id为4)。

begin;
delete from bank_acount where id = 2;
commit;

表中数据的隐藏列表（删除数据，删除版本号就是当前的事务id）

第六步

T6时间段，事务2进行第三次查询bank_acount表中的数据，未提交事务(当前事务的id依然为2)。

begin;
#在事务内部进行第一次查询
select * from bank_acount;
#在事务内部进行第二次查询
select * from bank_acount;
#在事务内部进行第三次查询
select * from bank_acount;

查询结果必须满足的版本条件：

1.隐藏的创建版本号必须小于当前的版本号（说明是在当前事务之前插入的）
2.隐藏的删除版本号必须大于当前版本号（说明是在当前事务之后删除的，之前是存在的）

根据查询条件，张三和李四符合，王五不符合：

第七步

T7时间段，事务5开启事务，更新张三的名字，并提交事务(假设当前事务的id为5)。

begin;
update bank_acount set name = "张三三" where id = 1;
commit;

表中数据的隐藏列表（更新：删除之前的，插入新的）

第八步

T8时间段，事务2进行第四次查询bank_acount表中的数据，并提交事务(当前事务的id依然为2)。

begin;
#在事务内部进行第一次查询
select * from bank_acount;
#在事务内部进行第二次查询
select * from bank_acount;
#在事务内部进行第三次查询
select * from bank_acount;
#在事务内部进行第四次查询
select * from bank_acount;
commit;

查询结果必须满足的版本条件：

1.隐藏的创建版本号必须小于当前的版本号（说明是在当前事务之前插入的）
2.隐藏的删除版本号必须大于当前版本号（说明是在当前事务之后删除的，之前是存在的）

根据查询条件，张三和李四符合，王五，张三三不符合：