MySQL事务及实现与锁

transaction

事务： 逻辑上是一组不可分的操作序列；

特性ACID

1. Atomicity：事务是最小的执行单位，不允许分割;要么执行完，要么失败回滚(undolog)。
2. Consistency： 执行事务前后，数据保持一致即有意义;(如：转账与收账账户一起总额不变-WAL技术：当然，分布式事务包含二阶段提交保证一致性)
3. Isolation：不同事物在提交时呈现是串行化的，对事物来说，它感应的数据库只有它自己在操作；但并发下，真正串行效率低，于是制定出四个隔离级别；
4. Durability：事务提交后，数据是持久化了的，不会因异常与宕机而丢失(redolog)；

并发事务的问题

1. Dirty read：一个事务读取到另一个未提交事务却已修改的数据(脏数据，非脏页)；
2. Lost to modify：一个事务对某数据进行修改，事务未提交时，另一事务随后也对此数据修改(修改的缓存)，造成丢失修改
3. Unrepeatable read：针对其他事务提交前后，事务读取数据本身的对比不同(更新操作)
4. Phantom read：针对其他事务提交前后，事务读取数据条数的对比(删除与插入操作)

隔离级别

1. read uncommitted： RC允许读取尚未提交的数据变更；
2. read committed：允许读取并发事务已经提交的数据，依旧有不可重复读、幻读(更改事务在读事务执行区间中)问题（MVCC）
3. repeatable read：事务多次读取一数据结果逻辑一致(事务自己修改)；但幻读存在；(MVCC+行锁)
4. serializable：事务依次执行，兄弟：没问题；(加锁实现)

MVCC

参考博客与脑图

是什么：

​ 指的是一种提高并发的技术，一种多版本并发控制机制；最早的DBS只有读读可并发，读写、写写都要阻塞；使用MVCC只有写写会阻塞，其余可并发；

解决什么问题：锁机制可以控制并发操作,但开销大；mvcc大部分情况可代替行级锁，降低系统开销；

实现：

1. 版本链(事务ID，回滚指针，自增ID) + undolog + consistent read view(快照读时产生)

snapshot and current read

• 快照读snapshot read：普通select语句(除select ... lock in share mode/for update;外)，读的可能是历史版本数据(一致性视图)；

• 当前读current read：update delete insert与select共享排他锁读的是数据库的最新数据（悲观锁操作）

更新操作

更新时，表记录中，每个记录有此三个字段：先写undolog，更新记录的回滚指针指向undolog记录，然后同时更新当前记录与事务id，还有自增标记id；

1. DB_TRX_ID： 6个字节，表示最近一次修改本记录的事务ID
2. DB_ROLL_PTR ： 7 个字节，回滚指针，指向回滚段中的 undo log record，用于找出这个记录的上个修改版本的数据。
3. DB_ROW_ID： 6 个字节，一个单调递增的 ID(若表有主键，则不包含此字段，无，则依此生成聚集索引)。

另外，每条记录的头信息（record header）里都有一个专门的bit（deleted_flag）来表示当前记录是否已经被删除

MVCC快照读

RC与RR的隔离级别事务开启后，有其它事务对此记录进行更新操作，但本事务依旧能够读取到准确的值(非表最新值：历史版本值)

活跃事务数组：每个事务开启时，会将当前活跃事务的ID放入此数组，同时记录低水位ID与高水位ID，三者组成事务的一致性视图；

• 低水位ID：取当前系统里已提交最大事务ID，再加1；即活跃事务数组里最早的事务ID；
• 高水位ID：当前事务所获取的事务ID(事务ID是严格递增的)；
• MVCC的consistent read view一致性视图：是个纯逻辑概念，定义在事务执行期间所见的版本数据；

事务查询数据时，会拿该记录事务ID与一致性视图递归比较，拿到一个可见的版本数据：

• 该记录事务ID < 低水位：此版本数据在此事务开启前提交的，可见，直接返回数据；
• 该记录事务ID > 高水位：此版本数据在此事务开启后提交的，不可见，从记录中取出回滚指针并获取其记录中的事务ID(此记录上个版本事务ID)，开始下一轮判断；
• 在高水位与低水位之间：两种情况
  • row trx_id在本活跃数组中：表示此版本是由还未提交事务生成的，不可见，还是需要取DB_ROLL_PTR获取上个版本记录的事务ID进行下一轮判断；
  • row trx_id不在本活跃数组中：表此版本是已提交事务生成的，可见；
  • 不在数组中情况解析：活跃事务数组(1,2,4)，3在2之后开启事务，但在当前事务4开启之前提交了；

隔离等级与MVCC

RC情况

​ 事务的每个快照读都会生成最新的consistent read view（纯逻辑概念）；因此，在一个事务执行期间，另一个事务开启并更新提交，会造成不可重复读；

RR情况

​ 同一事物只有第一个快照读才会创建consistent read view，只使用它；但仍有幻读(赠删符合的数据)

RR如何解决幻读

​ 注意：需要区分，所有事物里都可以用当前读(共享锁)=对匹配行数据进行加S锁，其它事务只能读，不能写；(但依旧会出现插入符合的数据，因此有幻读)

​ Next-key lock+MVCC（RR情况下）可解决幻读

锁Lock

​ 锁一般有读写锁，线程A获取读锁S时，需获取写锁才能写；同时，之后线程在A结束前不能获取写锁；

​ 获取写锁：需要等其它 读锁全部释放（或一个写锁释放）

锁分类

• 大致分三类：全局锁(锁数据库)，表级锁，行锁；

全局锁

• 命令：flush tables a,b with read lock，不写表名a,b则是整个数据库，写了就锁那几个表，其余线程增删改、数据定义语句(创建与改结构)、更新类事务提交语句 将阻塞；
• 使用场景：全库逻辑备份(mysqldump官方转储程序)；
• 开启时问题：一，在主库备份时，非只读操作将阻塞；二，在从库备份时，不能执行主库同步的binlog(更新操作)，主从延迟；
• mysqldump：RR、RC隔离下，使用参数–single-transaction，便启动一个事务来备份，可获取一致性视图(支持读写)；可正常更新；
• 不建议的只读命令：set global readonly=true
  • 此命令在客户端异常断开情况下，保持只读，阻塞更新等操作；而flush-则会自动释放此全局锁；
  • 在从库备份时，不影响slave同步复制，主从数据仍然一致；普通用户不能增删改等操作，但super用户可以(与flush不同)；

表级锁=表锁-MDL

元数据锁MDL

• MDL=meta data lock，不需要显示使用，访问时自动加上。
• 作用：保证读写的正确性；对数据curd时加读锁，在改变表结构等加写锁（这里读写、写写是阻塞的）
• 面试提问：
  • 数据量不大的热点表更改结构：alter table语句设置等待获取MDL写锁时间，失败则等下一时间段再试（避开高峰期）？
  • 小表加字段：设置获取DML写锁时间，失败，则可能是有长事务(非热点表)，可kill此长事务；

表锁

• 语法：lock tables t1 read,t2 wirte; => unlock tables客户端断开也自动释放；

行锁

​ 引擎层实现：innodb，分为S与X锁，事务结束，主动释放锁；是通过索引项加锁实现的；

两阶段协议：

​ InnoDB中，行锁有加锁、解锁两阶段，且两阶段不相交（执行增删改才加上(需要时)，事务结束后释放锁）

S与X锁

​ S锁：普通select是快照读，无锁；共享锁需自己添加SQL + lock in share mode，事务结束主动释放；

​ X锁：增删改都自动加排他锁，或SQL + for update，事务结束才主动释放；

问题：锁冲突

• 一般而言，都是X锁：事务中把尽可能造成的锁冲突、锁住多行 的sql往后放，以提高并发
• 需注意加锁时是否是行锁，查询不走索引将变为表锁，性能损耗大；

行锁的三种实现

innodb_locks_unsafe_for_binlog静态参数默认0(打开gap-lock)，为1时，关闭gap-lock，此时只有record lock(即同RC)；

静态参数：MySQL实例的生命周期中不可更改；即需要在配置文件中配置，并重启MySQL生效；

注意：即使关闭了gap-lock，于外键和唯一键重复检查方面用到的gap lock依旧有效；

1. Record lock：单条-索引记录上加锁，只能在索引上加；所以，会在聚集索引所以数据上加上锁(就类似表锁-但不是)，但有优化；
2. gap lock：在索引记录之间的间隙中加锁，即一条索引记录之前或之后，但不包含此索引记录本身；
3. next-key lock：默认=Record+gap lock；即除了锁住索引记录本身，也索引之间的间隙；

next-key lock加锁原则

1. 原则一：加锁的基本单位为next-key lock，前开后闭区间-(0,1]；

2. 原则二：查找过程中访问到的对象才加锁；

3. 优化一：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为Record lock;

4. 优化二：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁；

   bug说明：唯一索引上的范围查询会访问到不满足条件的第一个值为止：后闭区间实际上不用到不满足的那一个。（MySQL 8.0.18版本修复了此bug）

原则说明：

for update级别操作：

• 唯一索引
  • 精确等值检索，Next-Key Locks就退化为记录锁；
  • 范围检索，会锁住where条件中相应的范围，范围中的记录以及间隙，换言之就是加上记录锁和gap 锁（至于区间是多大稍后讨论）。
• 非唯一索引
  • 精确等值检索，Next-Key Locks会对间隙加gap锁（至于区间是多大稍后讨论），以及对应检索到的记录加记录锁。
  • 范围检索，会锁住where条件中相应的范围，范围中的记录以及间隙，换言之就是加上记录锁和gap 锁（至于区间是多大稍后讨论）。
• 非索引检索（实际给聚集索引全加锁-因为加的是行锁），全表gap lock、record lock(即next-key lock)，但可以开启semi-consitent read，边释放锁(不符合两阶段锁协议)；

其它说明：

1. 唯一索引=主键索引+唯一辅助索引 有Record Lock；
2. 非唯一索引 有next-key lock；
3. RC只有Record lock，RR在innodb_locks_unsafe_for_binlog=1也只有Record lock

扩展

数据库并发场景

1. 读读：没问题，无并发问题；
2. 读写：线程安全问题，可能有事物隔离问题：脏读、不可重复读、幻读；
3. 写写：线程安全问题，可能存在丢失更新问题；(锁)

一致性非锁定视图

​ 创建时机：一，事务执行第一个快照读语句时创建；二，执行start transaction with consistent snapshot时创建

​ MVCC的一致性视图 就是 一致性非锁定读 也是 快照读方式产生的纯逻辑视图；

死锁条件

• 互斥条件：一个资源每次只能被一个进程使用
• 保存与请求：获得的资源不放，还需请求其它的
• 不剥夺：不强制剥夺他人资源
• 循环等待：多个进程头尾相接请循环等待资源

脏读与脏页(prepare-redolog与更新内存顺序)：

1. 脏数据：事务对缓冲池的修改，未提交事务的缓冲池数据；
2. 脏读：是事务可读取到其它未提交事务的脏数据；
3. 脏页：内存中的数据页与磁盘中的不一致时，内存中的数据页为脏页；