MySQL事务 Krains 2020-08-09
事务是逻辑上的一组操作，要么都执行，要么全都不执行。
若没有事务的支持，会导致数据不一致的问题，比如转账操作将会面临问题：小明给小红各有1000元，小明要给小红转账100元，首先先从小明账户里扣除100元，在给小红账户增加100元。如果系统在给小明扣除100元之后，系统出现了故障，此时这100元钱就不翼而飞了。要想解决该问题就需要引入事务的支持了。
# 事务的四大特性（ACID）
原子性（Atomicity）：事务是最小的执行单位，不能被分割。事务的原子性确保动作要么全部执行，要么全不执行；
一致性（Consistency）：事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。拿上述转账操作来说，小明和小红的金钱总和为2000，不管他们如何转账，他们的金钱总和是不变的，这就是事务的一致性。
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，多个并发事务之间要相互隔离。
持久性（Durability）：一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
# 并发事务产生的问题
脏读：一个事务读取到另一个事务没有提交的数据，这个事务读到了脏数据，对这个数据的后续操作可能会带来错误。
不可重复读：一个事务两次读取同一行数据，结果得到不同状态的结果，中间正好另一个事务更新了该数据，导致两次结果不同。
幻读：一个事务执行了两次查询，第二次结果集中包含了第一次中没有或某些行已经被删除的数据，造成两次查询结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。
丢失更新：是不可重复读的特殊情况。如果两个事务都读取同一行，两个事务都进行写操作，并提交，第一个事务所做的改变就会丢失。
不可重复读和脏读的区别：脏读是某一个事务读取到了另一个事务没有提交的数据，不可重复读是某个事务读取了另一个事务已经提交的数据。
不可重复读和幻读的区别：都是读取了另一条已经提交的事务，不同的是不可重复读查询的是同一个数据项，幻读查询的是一批数据整体。
# 解决方法
设置事务的隔离级别，事务的隔离级别越高，产生的问题越少，同时运行的效率会降低。
SQL 标准定义了四个隔离级别：
- READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。允许两个事务并发读操作，不允许并发读，一个事务对记录的更新操作，会进行阻塞，只有等待另一个事务提交才能继续往下执行，该级别可以防止脏读、不可重复读以及幻读。
隔离级别 | 脏读 | 不可重复读 | 幻影读 |
|---|---|---|---|
READ-UNCOMMITTED | √ | √ | √ |
READ-COMMITTED | × | √ | √ |
REPEATABLE-READ | × | × | √ |
SERIALIZABLE | × | × | × |
# MVCC
MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。
在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。
这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。
# 版本链
对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列(row_id并不是必要的，我们创建的表中有主键或者非NULL唯一键时都不会包含row_id列)：
- trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
- roll_pointer：每次对某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该记录修改前的信息。
版本链存储在undolog日志文件中。
# ReadView
对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。
ReadView中主要包含4个比较重要的内容:
- m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
- min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小 值。
- max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
- creator_trx_id：表示生成该ReadView的事务的事务id。
注意max_trx_id并不是m_ids中的最大值,事务id是递增分配的。比方说现在有id为1,2,3这三个事务,之 后id为3的事务提交了。那么一个新的读事务在生成ReadView时,m_ids就包括1和2,min_trx_id的值就是1, max_trx_id的值就是4。
有了这个ReadView,这样在访问某条记录时,只需要按照下边的步骤判断记录的某个版本是否可见:
- 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自 己修改过的记录，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事 务生成ReadView前已经提交，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事 务生成ReadView后才开启，所以该版本不可以被当前事务访问。
- 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下 trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃 的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版 本可以被访问。
# 读已提交的实现
读的时候如何实现？
两个事务A(300)，B(200)， B修改了数据就在版本链上加一行记录，trx_id为200，roll_pointer指向上一条记录，当它要查询数据的时候，也就是使用select时会生成ReadView，假设ReadView中m_ids中有[81,82,200,300]，m_ids保存的是当前活跃的事务，遍历版本链，找到trx_id与自己的事务id相等，就知道是自己修改的，于是能够取出自己修改的数据。
而对于A来说，它的m_ids为[81, 82, 200, 300]，它遍历版本链，由于事务200,82,81都处于活跃状态，所以不能够读取trx_id为活跃状态的数据，对于trx_id为80的来说，它不存在m_ids中，所以它是在A事务开启前就已经提交了的，所以A读出来的数据就是trx_id为80的这行数据。
假如事务81提交了，它需要把trx_id为81的这行数据提到链表的头部，查询生成的ReadView中事务81不存在于m_ids中，这样其他的事务就能够读取到它所修改的内容。
写的时候如何实现？
事务a对数据进行update、delete、insert时会对数据加X锁，其他事务对这些数据加锁时就会进入阻塞。但是当事务a提交的时候，会将修改提交，其他事务会看到这些数据的变化，会造成不可重复读和幻读。
# 可重复读实现
读的时候如何实现？
在第一次读取数据时生成一个ReadView，以后查询都用这个。
写的时候如何实现？用到了间隙锁
+----+------+------+------+------+
| a | b | c | d | e |
+----+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | b |
| 3 | 3 | 2 | 2 | c |
| 4 | 3 | 1 | 1 | d |
| 5 | 1 | 3 | 5 | e |
| 6 | 6 | 4 | 4 | f |
| 8 | 8 | 8 | 8 | h |
| 10 | 1 | 1 | 1 | i |
| 11 | 1 | 1 | 1 | i |
| 13 | 3 | 3 | 3 | i |
| 14 | 7 | 4 | 4 | b |
+----+------+------+------+------+
a为主键，bcd是联合索引
查询时用到了索引，如使用主键进行查询时，会对8-10之间的间隙以及a>9后的记录以及记录之间的间隙加锁
mysql> select * from t1 where a > 9 for update;
+----+------+------+------+------+
| a | b | c | d | e |
+----+------+------+------+------+
| 10 | 1 | 1 | 1 | i |
| 11 | 1 | 1 | 1 | i |
| 13 | 3 | 3 | 3 | i |
| 14 | 7 | 4 | 4 | b |
+----+------+------+------+------+
insert into t1 value(7,1,1,1,'i'); -- 不会阻塞，7不属于8-10之间的间隙
insert into t1 value(9,1,1,1,'i'); -- 会阻塞
insert into t1 value(12,1,1,1,'i'); -- 会阻塞
insert into t1 value(16,1,1,1,'i'); -- 会阻塞查询的时候没有用到索引的时
为了防止幻读的发生，会对表中所有记录和间隙进行加锁。
# 锁
读锁和写锁
- 读锁：共享锁、shared locks、s锁
- 写锁：排他锁、exclusive locks、x锁
锁冲突：
- 两个事务可以分别对同一条记录加读锁，两个读锁之间没有冲突。
- 其中一个事务加了读锁，另外一个事务就不能加写锁
- 其中一个事务加了写锁，另外一个事务也不能加读锁
两个读锁之间没有冲突，两个事务不能对同一条记录同时加读锁和写锁，读锁和写锁有冲突，如果一个事务a对一条记录加了写锁，另一个事务b此时对这条记录加读锁，此时这个事务b会进入阻塞，只有当另外一个事务a提交之后把写锁释放了，事务b才能够继续往下走。
对于普通的select语句，InnoDB不会加任何锁，所以不管记录有没有读或写锁select语句都不阻塞。
对于delete、insert、update，InnoDB会先给记录加X锁，在进行下一步操作。
如何显式给select加锁？
select .... lock in share mode
将查到的数据加上S锁，允许其他事务加S锁，但是不能加X锁（会阻塞，只有当当前事务提交commit或者rollback之后S锁才会释放）
select ... for update
将查到的数据加上X锁，其他事务不能对这些数据加X或者S锁。
# MySql锁总结
MySql不同存储引擎各自实现了自己的锁机制
- InnoDB引擎支持行级锁、表级锁
- MyISAM支持表级锁
表级锁：对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，
行级锁：只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
InnoDB实现了两种类型的行锁
- 共享锁：允许一个事务去读一行
- 排它锁：允许获得排它锁的事务更新数据
共享锁和共享锁不会冲突，排它锁与这两个锁都会冲突。
InnoDB实现的两种意向表级锁
- 意向共享锁：事务打算给数据行加行共享锁，需要取得该表的意向共享锁
- 意向排他锁：事务打算给数据行加行排它锁，需要取得该表的意向排他锁
IS、IX锁是表级锁,它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁,以 避免用遍历的方式来查看表中有没有上锁的记录。
排它锁会和所有锁冲突，意向排它锁会和排它锁和共享锁冲突。
间隙锁
为了防止幻读，InnoDB提出了间隙锁，使用索引时候会用到间隙锁，而没有使用索引则要对整张表进行加锁。
乐观锁：假设不会发生并发冲突，只是在提交数据的时候检查是否数据被修改过。
乐观锁，顾名思义就是很乐观，每次去拿数据的时候都以为别人不会修改，所以不会上锁，在更新的时候可以要判断在此期间有没有人去更新这个数据，它的实现可以使用版本号等机制。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
每次去拿数据的时候就会上锁。