前言
本文仅个人的学习所记随笔，如有不对之处，还有各位大佬斧正！谢谢！
我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题
这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题
1 事务
1 概念
事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚
2 ACID
-
原子性
事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚
回滚可以用日志 (undo log) 来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可
-
一致性
数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的
-
隔离性
一个事务所做的修改在最终提交以前，对其它事务是不可见的
-
持久性
一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
可以通过数据库备份和恢复来实现，在系统发生崩溃时，使用备份的数据库进行数据恢复
事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系:
-
只有满足一致性，事务的执行结果才是正确的
-
在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性
-
在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性
-
事务满足持久化是为了能应对数据库崩溃的情况
3 AUTOCOMMIT
MySQL 默认采用自动提交模式。也就是说，如果不显式使用 start transaction 语句来开始一个事务，那么每个查询都会被当做一个事务自动提交
2 并发一致性问题
-
丢失修改
T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改
-
读脏数据
T1修改一个数据，T2随后读取这个数据。如果T1撤销了这次修改，那么T2读取的数据是无效,不符合一致性要求
-
不可重复读
T2读取一个数据，T1对该数据做了修改。如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同，不符合隔离性
-
幻影读
T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和第一个读取的结果不同，不符合隔离性
脏读、不可重复读、幻读，其实都是数据库读一致性的问题，必须有数据库提供一定的事务隔离机制来解决
-
未提交读(READ UNCOMMITTED) 事务中的修改，即使没有提交，对其它事务也是可见的。
-
提交读(READ COMMITTED) 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
-
可重复读(REPEATABLE READ) 保证在同一个事务中多次读取同样数据的结果是一样的。
-
可串行化(SERIALIZABLE) 强制事务串行执行。
| 隔离级别 | 脏读 | 不可重复读 | 幻影读 |
|---|---|---|---|
| 未提交读 | √ | √ | √ |
| 提交读 | × | √ | √ |
| 可重复读 | × | × | √ |
| 可串行化 | × | × | × |
-
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION; -
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意：事务隔离级别越高，数据越安全，但是性能越低。
3 锁
从对数据库的操作类型分：
-
读锁（共享锁，简写为 S 锁）
针对同一份数据，多个读操作可以同时进行而不会互相影响；
-
写锁（排他锁，简写为 X 锁）
当前写操作未完成前，它会阻断其他写锁和读锁
从对数据操作的粒度上看分：
-
表锁
-
行锁
1 表锁
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低；一般应用于整表数据迁移的场景，应用在MyISAM、 InnoDB、BDB等存储引擎中
语法
-
加锁：lock table 表名称 read(write),表名称2 read(write);
-
释放锁：unlock tables / 客户端断开连接
案列分析（加读锁）
# 对表进行加读锁
lock table sys_role read;
# 释放锁
unlock table;
案列分析（加写锁）
案列结论
-
对表的读操作(加读锁) ，不会阻寒其他进程对同一表的读请求，但会阻赛对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作
-
对表的写操作(加写锁) ，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作
2 行锁
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB存储引擎中
可以通过以下SQL，查看意向锁及行锁的加锁情况：
# mysql 8.0 可用
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;常见的SQL语句，在执行时，所加的行锁如下：
| SQL | 行锁类型 | 说明 |
|---|---|---|
| insert | 排他锁 | 自动加锁 |
| update | 排他锁 | 自动加锁 |
| delete | 排他锁 | 自动加锁 |
| select（正常） | 不加任何锁 | |
| select ...... lock in mode | 共享锁 | 需要手动在 select 之后加 lock in mode |
| select ...... for update | 排他锁 | 需要手动在 select 之后加 for update |
行锁案例
案例小结
简而言之，就是写锁（X锁，排他锁）阻塞写锁和读锁；读锁（S锁，共享锁）会阻塞写，但是不会阻塞读
3 意向锁
使用意向锁（ Intention Locks ）可以更容易的支持多粒度的封锁
在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加上 X 锁，就需要先检测是否有其他事务对表 A 或者表 A 中的任意一行加上了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的
意向锁在原来的 X / S 锁之上引入了 IX / IS，IX / IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁 或 S 锁。有以下两个规定：
-
一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁
-
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁
通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其他事务对表 A 加了 X / IX / S / IS 锁，如果加了就表示有其他事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败
各种锁的兼容关系如下：
| - | X | IX | S | IS |
|---|---|---|---|---|
| X | × | × | × | × |
| IX | × | √ | × | √ |
| S | × | × | √ | √ |
| IS | × | √ | √ | √ |
解释如下：
-
任意 IS / IX 锁之间都是兼容的，因为它们只是想要对表加锁，而不是真正加锁
-
S 锁只与 S 锁和 IS 锁兼容，也就是说事务 T 想要对数据行加 S 锁，其他事务可以获得对表或者表中的行的 S 锁
4 锁协议（锁 & 事务并发）
-
一级封锁协议
事务 T 要修改数据 A 时必须加上 X 锁，直到事务结束才释放锁
可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖
T1 T2 lock - X(A) read A = 20 lock - x（A） wait write A = 19 ... commit ... unlock - X(A) ... obtain read A = 19 write A = 21 commit unlock - X(A) -
二级封锁协议
在一级的基础上，要求读取数据时必须加上 S 锁，读取完马上释放 S 锁
可以解决脏读问题，因为一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据
T1 T2 lock - X(A) read A = 20 write A = 19 lock -S(A) wait rollback ... A = 20 ... unlock - X(A) ... obtain read A = 20 unlock - S(A) commit -
三级封锁协议
在二级的基础上，要求读取数据时必须加 S 锁，直到事务结束了才能释放 S 锁
可以解决不可重复的问题，因为读 A 时，其他事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变
T1 T2 lock - S(A) read A = 20 lock - X(A) wait read A = 20 ... commit ... unlock - S(A) ... obtain read A = 20 write A = 19 commit unlock - X(A) -
两段锁新协议
加锁和解锁分为两个阶段进行
可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同
事务遵循两段锁协议是保证可串行化调度的充分条件；例如以下操作满足两段锁协议，它是可串行化调度
lock-x(A) ... lock-s(B) ... lock-s(C) ... unlock(A) ... unlock(C) ... unlock(B)但不是必要条件；例如以下操作不满足两段锁协议，但是它还是可串行化调度
lock-x(A) ... unlock(A) ... lock-s(B) ... unlock(B) ... lock-s(C) ... unlock(C)
4 多版本并发控制
多版本并发控制(Multi-Version Concurrency Control, MVCC)是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现
1 版本号
-
系统版本号：是一个递增的数字，每开始一个事务，系统版本号就会自动递增
-
事务版本号：事务开始之前的系统版本号
2 隐藏的列
MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：
-
创建版本号：指示创建一个数据行的快照时的系统版本号
-
删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合 undo log，指向上一个版本 |
3 快照读与当前读
-
当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
select ... lock in share mode（共享锁），select ... for update（排他锁）、update、insert、delete 都是一种当前读
-
快照读：简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
-
read committed：每次select，都生成一个快照读
-
repeatable read：开启事务后第一个 select（不加锁） 语句才是快照读的地方，后续的 select（不加锁）都是快照读，保证了可重复读
-
serializable：快照读会退化为当前读
-
4 undo log
回滚日志，在 insert 、update 、delete 的时候产生的便于数据回滚的日志
当 insert 的时候，产生的 undo log 日志只在回滚时需要，在事务提交后，可被立即删除
而 update 、delete 的时候，产生的 undo log 日志不仅在回滚时需要，在快照读时也需要，不会立即被删除
1 版本链
有一条新增的原始数据：
| id | age | name | DB_TRX_ID | DB_ROLL_PTR |
|---|---|---|---|---|
| 30 | 30 | A30 | 1 | null |
假设有4个事务同时在访问这个数据：
-
第一步
| T1 | T2 | T3 | T4 |
|---|---|---|---|
| begin | begin | begin | begin |
| update age = 3 | select id = 30 | ||
| commit | |||
| update name = A3 | |||
| select id = 30 | |||
| commit | |||
| update age = 10 | |||
| select id = 30 | |||
| select id = 30 | |||
| commit | |||
| commit; |
当 T1 执行第一条修改语句时，会记录 undo log 日志，记录数据变更之前的样子；然后更新记录， 并且记录本次操作的事务ID
回滚指针：用来指定如果发生回滚，回滚到哪一个版本
-
第二步
当 T2 执行修改语句时，也会记录 undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID
-
第三步
当 T2 执行修改语句时，也会记录 undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID
同第二步
不同事务或相同事务对同一条记录进行修改，会导致该记录的 undo log 生成一条记录版本链表
链表的头部是最新的旧记录，链表尾部是最早的旧记录
5 ReadView
ReadView 是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id
ReadView中包含了四个核心字段：
| 字段 | 含义 |
|---|---|
| m_ids | 当前正在执行且还没有提交的事务 id |
| min_trx_id | m_ids中最小的事务 id |
| max_trx_id | 下一个要生成的事务 id |
| creator_trx_id | 每开启一个事务都会生成一个 ReadView，而 creator_trx_id 就是这个开启的事务的 id |
而在readview中就规定了版本链数据的访问规则：
-
trx_id 代表当前 undo log 版本链对应事务 ID
| 条件 | 是否可以访问 | 说明 |
|---|---|---|
| trx_id == creator_trx_id | 可以访问 | 说明数据是当前这个事务更改的 |
| trx_id < min_trx_id | 可以访问 | 说明数据已经提交了 |
| trx_id >= max_trx_id | 不可以访问 | 说明该事务是在 ReadView 生成后才开启的 |
| min_trx_id <= trx_id < max_trx_id | 如果 trx_id 不在 m_ids 中，是可以访问的 | 说明数据已经提交 |
此处如果 m_ids 为 [5,6,7,9,10]
若 trx_id 在 m_ids 中，比如是 6：说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问
若 trx_id 不在 m_ids 中，比如是 8：说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问
不同的隔离级别，生成ReadView的时机不同：
-
read committed：在事务中每一次执行快照读时生成 ReadView
-
repeatable read：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView
6 实现流程
| T1 | T2 | T3 | T4 |
|---|---|---|---|
| begin | begin | begin | begin |
| update age = 3 | select id = 30 | ||
| commit | |||
| update name = A3 | |||
| select id = 30 | |||
| commit | |||
| update age = 10 | |||
| select id = 30 | |||
| select id = 30 | |||
| commit | |||
| commit |
1 RR
假设事务的隔离等级为：repeatable read
当 T4 执行第一次的查询语句时，m_ids 中应该包含了 T2、T3 两个正在活跃的事务，m_ids = [ 2, 3 ]；max_trx_id 为下一个开启事务的 Id；当前的事务 Id 为 4；所以 max_trx_id = 5；
当 T4 第一次执行查询语句时，该行当前数据的 undo log 如下图所示：
当 T4 查询时，会拿该行记录的 DB_TRX_ID 去跟 min_trx_id、max_trx_id、m_ids 进行比较
先拿记录中的 DB_TRX_ID 去跟记录中的 min_trx_id 比较，看 4 是否小于 2；不符合就继续判断是否大于等于 max_trx_id；不符合就继续判断是否符合 min_trx_id <= trx_id < max_trx_id；如果还不符合就查找该记录的 undo log 链表中的下一个记录；继续判断
当只要满足一个条件时，判断结束
5 Next-Key Locks
Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现，是记录锁（record lock）与间隙锁（gap lock）的结合
MVCC 不能解决幻读的问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题
假设数据：
| id | name | balance |
|---|---|---|
| 1 | lilei | 450 |
| 2 | hanmei | 10000 |
| 3 | lucy | 2400 |
| 10 | xlp | 1000 |
| 20 | wjz | 2000 |
那么以上数据的间隙就有（3，10）、（10，20）、（20，＋∞）三个区间
如果有个 session 执行以下的语句：
update stu set name = 'aaa' where id > 8 and id < 18那么就会加 Next-Key Locks 锁住 10 U （3，10）U（10，20】：表示对 id = 10 的索引项加锁 + 对 id 在（3，10）加间隙锁 + 对 id 在（10，20】加间隙锁
间隙锁唯一目的是防止其他事务插入间隙
间隙锁可以共存，一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁
间隙锁是在可重复读隔离级别下才会生效
6 补充
在 InnoDB 的 RR 级别中，当快照读和当前读同时使用时，仍然可能会发生不可重复读
1 RR出现不可重复读
假设数据：
| id | name | age |
|---|---|---|
| 1 | xlp | 23 |
假设有以下两个事务：
| T1 | T2 |
|---|---|
| begin | begin |
| select id = 1 | update age = 18 |
| commit | |
| update name = aaa | |
| select id = 1 |
显然 T1 在第二次执行查询语句时出现了不可重复读
1 为什么出现不可重复读
在 T2 执行 update 语句时采用的是当前读，会对这条记录进行加锁、修改 age、修改 trx_id = 2（假设在插入数据时，trx_id = 1）
在 T1 执行 update 语句时也是当前读，所以记录的 trx_id = 1
在 T1 第二次执行查询语句时，采用的是快照读，根据可见性算法，这条记录最新的 trx_id 就是当前活跃的事务，所以是可见的
所以 T1 在第二次查询时读取到了 T2 的修改，这就表示发生了不可重复读
2 如何解决
可以使用行锁解决
在第一次查询时，语句使用 for update，为记录加上行锁
194
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
