![[CC++后端开发学习]18 Mysql事务原理,第1张](/aiimages/%5BCC%2B%2B%E5%90%8E%E7%AB%AF%E5%BC%80%E5%8F%91%E5%AD%A6%E4%B9%A0%5D18+Mysql%E4%BA%8B%E5%8A%A1%E5%8E%9F%E7%90%86.png "[CC++后端开发学习]18 Mysql事务原理,第1张")
- 1 事务基本概念
- ACID特性
- 原子性（A）
- 一致性（C）
- 隔离性（I）
- 持久性（D）
- 事务并发异常
- 1）脏读
- 2）不可重复读
- 3）幻读
- 隔离级别
- 1）READ UNCOMMITTED
- 2）READ COMMITTED
- 3）REPEATABLE READ
- 4）SERIALIZABLE
- 不同隔离级别对比
- 事务控制语句
- 2 事务隔离性的实现
- 2.1 锁
- 2.1.1 锁的类型
- 1）共享锁（S锁，读锁）
- 2）排他锁（X锁，写锁）
- 3）意向共享锁（IS锁）
- 4）意向排他锁（IX锁）
- 2.1.2 锁的具体实现
- 1）Record Lock（行锁）
- 2）Gap Lock（间隙锁）
- 3）Next-Key Lock
- 4）Insert Intention Lock（插入意向锁）
- 5）AUTO-INC Lock（自增锁）
- 2.1.3 加锁的对象示例
- 示例1
- 示例2
- 2.2 MVCC
- MVCC的实现 —— undo log
- 3 事务持久性的实现 —— redo log
事务将数据库从一种一致性状态转换为另一种一致性状态。它是访问并更新数据库各种数据项的一个程序执行单元，可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。
简而言之，在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存。
在 MySQL InnoDB 下，每一条语句默认都是事务。可以通过set autocommit = 0;设置当前会话手动提交。
ACID特性 原子性（A）事务 *** 作要么都做（提交），要么都不做（回滚）。
事务的回滚的通过undolog来实现的。undolog记录的是事务每步具体 *** 作，当回滚时，执行事务具体 *** 作的逆运算。
一致性（C）在事务执行前后，数据库的完整性约束没有被破坏。一致性由原子性、隔离性以及持久性共同来维护的。
隔离性（I）例如：一个表的姓名是唯一键，如果一个事务对姓名进行修改，但是在事务提交或事务回滚后，表中的姓名变得不唯一了，这样就破坏了一致性
对于同时执行的多个不同事务，其 *** 作对象互相隔离，也就是说一个事务提交前对其他事务都不可见。这主要是通过 MVCC（多版本并发控制）和 锁来实现的。
锁用来处理并发 DML *** 作。而 MVCC 实现一致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读 *** 作，从而实现高效并发读性能。
持久性（D）事务提交后，事务DML *** 作将会持久化，即使发生宕机等故障，数据库也能将数据恢复。这是通过写入redolog磁盘文件来实现的，大致内容包括 *** 作了数据的哪一个页、页偏移值以及具体数据等。
事务并发异常在我们未对事务做优化前，事务的并发 *** 作可能存在如下问题。
1）脏读事务（A）可以读到另外一个事务（B）中未提交的数据，则称事务A读到了脏数据。
2）不可重复读一个事务还未提交前，两次读同一个数据得到的结果却不一样的情况。出现这种情况说明有另一个事务提交了修改。一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题。
3）幻读往往发生在事务中需要先读数据进行条件判断然后再进行写 *** 作的情况。读数据后，判断条件成立，然后在执行写 *** 作前，数据已被其他事务修改，之前读 *** 作得到的条件已经不满足，导致后续写 *** 作失败或错误。也就是说读了个”寂寞“。
例如：对于一个以name为唯一索引的表，在一个事务中经查询得知不存在name=aaa的行记录，于是正准备执行插入name=aaa的行记录，此时另外一个事务执行了相同的插入 *** 作，这将导致本事务的此次插入 *** 作失败。
幻读也可以通过手动给读 *** 作加锁来解决，但具体还需要看隔离级别。
隔离级别引入隔离级别的目的就是为了解决上面提到的事务并发异常。
1）READ UNCOMMITTEDSQL标准制定了四种事务隔离级别的标准，各数据库厂商在正确性和性能之间做了妥协，并没有严格遵循这些标准。
所谓读未提交，即可以读到其他事务未提交的 *** 作。该级别下读 *** 作不加锁，写 *** 作加排他锁，在事务提交或回滚后释放锁；
2）READ COMMITTED读已提交，即只能读到已提交的数据，但可能出现”不可重复读“。从该级别开始支持 MVCC ，也就是提供一致性非锁定读。此时读取 *** 作实际读取的是历史快照数据的最新版本，如果出现更新的版本，则读到的就是更新的版本了。
很多厂商（如Oracle、SQL Server）默认隔离级别就是READ COMMITTED。
3）REPEATABLE READ可重复读，可以避免”不可重复读“的问题，即事务提交前读同一个数据得到的结果总是相同的。此时执行读 *** 作实际读到的是该事务开始时的快照版本（不是最新版本），因为事务开始时刻的那个快照版本肯定不会变，所以每次读的结果都是一样。该级别下也支持 MVCC，但如果不主动加锁仍可能出现幻读。
4）SERIALIZABLE可串行化，即所有事务都是串行化的执行，因此是最严苛的隔离级别。可以解决上面的所有的并发异常。
不同隔离级别对比
MySQL InnoDB默认支持的隔离级别是REPEATABLE READ。
设置事务隔离级别的命令为：
-- 设置隔离级别 SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;
开始和提交事务等：
-- 显示开启事务 START TRANSACTION -- 提交事务，并使得已对数据库做的所有修改持久化 COMMIT -- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改 ROLLBACK -- 创建一个保存点，一个事务可以有多个保存点 SAVEPOINT identifier -- 删除一个保存点 RELEASE SAVEPOINT identifier -- 事务回滚到保存点 ROLLBACK TO [SAVEPOINT] identifier
示例：
DROp TABLE IF EXISTS `lock_test`; CREATE TABLE `lock_test` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `cid` INT, `num` INT, -- 用于测试修改 KEY(`cid`) ) ENGINE=InnoDB; -- 建表 INSERT INTO `lock_test` (`cid`, `num`) VALUES (3, 1), (5, 2), (9, 3), (11, 4), (15, 5); -- 设置隔离级别并开始事务 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 隔离级别 REPEATABLE READ START TRANSACTION; SELECT * FROM `lock_test` WHERe `cid` > 9; -- 读 *** 作 -- ... COMMIT; -- 提交事务2 事务隔离性的实现 2.1 锁
锁用于实现事务的隔离级别。Mysql的锁丰富而复杂，多个概念互相联系，不易理解。
2.1.1 锁的类型首先Mysql针对事务的锁粒度可分为三种层次：
- 针对表加锁（即对B+树加锁）
- 针对页加锁（即对B+树叶子节点加锁）
- 针对行加锁（即对B+树叶子节点当中的某一记录行加锁）
具体来说，共享锁和排他锁是行级锁，而意向共享锁和意向排他锁都是表级锁。所谓“意向”，大致意思就是说事务有意向对表中的某些行加锁。
1）共享锁（S锁，读锁）针对事务的读 *** 作所加的锁，并且是针对某一行加锁：
- 在SERIALIZABLE隔离级别下，默认帮读 *** 作加共享锁（即自动加锁）；
- 在REPEATABLE READ隔离级别下，若需要解决幻读问题，需手动加共享锁；
- 在READ COMMITTED隔离级别下，可以加共享锁但没必要加，采用的是 MVCC（见2.2）；
- 在READ UNCOMMITTED隔离级别下，既没有加锁也没有使用 MVCC（见2.2）；
其中，REPEATABLE READ和READ COMMITTED都可以加共享锁但二者的具体实现不同，READ COMMITTED采用的是“行锁”，而REPEATABLE READ采用的是“Next-Key锁”，具体见2.1.2。
需要手动加共享锁时，在执行语句末尾加LOCK IN SHARE MODE指令。
2）排他锁（X锁，写锁）针对事务的删除或更新 *** 作加的锁，并且是针对某一行加锁。
在4种隔离级别下，事务写 *** 作都自动添加了排他锁，事务提交或事务回滚后释放锁。
需要手动加排他锁时，在执行语句末尾加FOR UPDATe指令。
3）意向共享锁（IS锁）对一张表中某几行加的S锁。事务要获取表中某些行的S锁，必须先获得表的IS锁。同时，在某个表中插入S锁时，则自动在页和表上添加IS锁。
4）意向排他锁（IX锁）对一张表中某几行加的X锁。事务要获取表中某些行的X锁，必须先获得表的IX锁。同时，在某个表中插入X锁时，则自动在页和表上添加IX锁。
普通select查询语句默认不加锁，而DML *** 作（增、删、改）默认加排他锁。
隔离级别与加行级锁 *** 作的关系汇总：
锁可以锁住单行记录，也可以锁住各个记录的索引之间的一段范围（比如要向某一个索引范围内插入一个新记录时）。
1）Record Lock（行锁）又叫记录锁，不论读写，都是对单行记录加锁，实质是对主键索引加锁。
2）Gap Lock（间隙锁）间隙锁会锁定一个索引范围，但不包含各个已存在的记录的索引本身。也就是说，对于前面第1节中所举的示例，下面的语句（在REPEATABLE READ级别下手动加锁），间隙锁会锁住大于指定值的的所有索引范围，用区间表示为：(9, 11)，(11, 15)，(15, +∞)，都是全开区间。
SELECT * FROM `lock_test` WHERe `cid` > 9 LOCK IN SHARE MODE;
实际上，在READ COMMITTED级别下，还会自动把各个间隙间的索引也加上行锁，于是锁住的索引范围区间变为：(9, 11]，(11, 15]，(15, +∞)，成了左开右闭区间，这实际上就是接下来要说的Next-Key Lock。
REPEATABLE READ及以上的隔离级别才支持间隙锁，这就是为什么在READ COMMITTED隔离级别下即使手动加锁也不能解决幻读问题，因为在READ COMMITTED隔离级别下即使使用范围查询加的也是行锁，只锁住了单个行，没有锁住一个范围。
问1：什么情况下即使在REPEATABLE READ级别也只加行锁而不加间隙锁呢？
在索引连续的情况下。比如前面的例子中我们再插入一个记录的cid为10，则存在连续的索引9、10、11，那么9和10之间就不存在间隙，不会加间隙锁，10和11之间也会不加，因此对于索引10和11只有行锁。
实质是Record Lock + Gap Lock，锁定相邻索引间的范围，同时会锁住范围内各个记录本身。主要目的是阻止多个事务将记录插入到同一个范围内，从而避免幻读。所谓“Next-Key”想表达的意思大概就是说锁的范围包含下一个索引key。
问2：那什么情况下一个间隙锁不会变成 Next-Key Lock 呢？
查询条件为“等于”时，1）查询的索引对应记录不存在，只有间隙锁；2）查询的索引对应记录存在，则前一个区间是 Next-Key Lock，后一个区间是间隙锁。
1）比如执行下面语句，待查询的索引压根不存在表中：
SELECt * FROM `lock_test` WHERe `cid` = 7 LOCK IN SHARE MODE;
此时查询的索引不存在，自然没有对应的行锁，被锁住的索引范围是(5, 9)，此时就是间隙锁。注意cid是非唯一索引，为什么强调这一点后面2.1.3节就知道了。
2）又如下面的语句，待查询的索引存在表中：
SELECt * FROM `lock_test` WHERe `cid` = 9 LOCK IN SHARE MODE;
被锁住的索引范围是(5, 9]，(9, 11)，后一个区间是对应间隙锁。
4）Insert Intention Lock（插入意向锁）是特殊的间隙锁，在insert *** 作的时候产生，同时自动加X锁。
在多事务同时插入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，即不会发生锁等待。
假设记录的索引已包含键值4和7，两个不同的事务分别插入索引的键值为5和6的记录，每个事务都会产生一个加在4到7之间的插入意向锁，去获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。
5）AUTO-INC Lock（自增锁）一种特殊的表级锁，发生在AUTO_INCREMENT约束下的插入 *** 作。从MySQL 5.1.22开始提供了一种轻量级互斥量的自增长实现机制，该机制提高了自增长值插入的性能。
对于锁的总结：
已知行级锁是针对表的索引加锁，这里的索引包括聚集索引和辅助索引；而表级锁是针对页或表进行加锁。并且具体的加锁行为是与SQL语句中的where条件也有关的。
上面的各种锁都在何时会被使用呢？我们通过示例来说明与验证。
示例1SELECt * FROM `lock_test` WHERe `id` = 5 LOCK IN SHARE MODE; -- 主动加 S 锁 SELECt * FROM `lock_test` WHERe `id` = 5 FOR UPDATe; -- 主动加 X 锁
对于上面的语句产生的加锁行为有以下这些可能情况。
a）在 Read Committed 隔离级别下:
- id 是主键
对主键 id=5 的行记录加行锁。 - id 是唯一索引
在唯一索引 id=5 的记录上加行锁，同时对其聚集索引上相应的行记录加行锁 - id 是非唯一索引
在非唯一索引上所有 id=5 的记录上加行锁，同时对其聚集索引上相应的所有行记录加行锁 - id 不是索引
在聚集索引上扫描，对经过的所有行记录加行锁，此处有个优化：不满足条件语句的记录在加锁后，判断为不满足即可释放锁
b）在 Repeatable Read 隔离级别下：
- b.1）id 是主键
如果 id=5 的记录存在，则对主键 id=5 的记录上加行锁；如果不存在则对其所在的索引范围加间隙锁。 - b.2）id 是唯一索引
如果 id=5 的记录存在，在唯一索引 id=5 的记录上加行锁，同时对其聚集索引上相应的行记录加行锁；如果不存在，则对其所在的索引范围加间隙锁。 - b.3）id 是非唯一索引
在非唯一索引上查找 id=5 的记录：如果找到了则对其加上行锁和间隙锁（即Next-Key Lock），同时对其聚集索引上相应的所有行记录加行锁；如果没有找到则加间隙锁。
问3：怎么理解对于非唯一索引即使记录存在也要加上间隙锁？非唯一索引下可能存在多个行有相同的索引值，通过加间隙锁可以将拥有这个相同索引值的记录都囊括进来。 - b.4）id 不是索引
在聚集索引上扫描，对经过的所有记录加行锁和间隙锁（即Next-Key Lock）。
验证：
（一） 建表
DROP TABLE IF EXISTS `lock_test`; CREATE TABLE `lock_test` ( `id` INT PRIMARY KEY AUTO_INCREMENT, -- 唯一索引，主键 `cid` INT, -- 非唯一索引 `num` INT, -- 用于测试修改 KEY(`cid`) ) ENGINE=InnoDB; INSERT INTO `lock_test` (`id`, `cid`, `num`) VALUES (NULL, 3, 1), (NULL, 5, 2), (NULL, 9, 3), (NULL, 11, 4), (7, 15, 7);
mysql> select * from lock_test; +----+------+------+ | id | cid | num | +----+------+------+ | 1 | 3 | 1 | | 2 | 5 | 2 | | 3 | 9 | 3 | | 4 | 11 | 4 | | 7 | 15 | 7 | +----+------+------+ 5 rows in set (0.00 sec)
（二） 开启两个mysql终端并都执行下面的语句开始事务
-- 设置隔离级别并开始事务 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION;
（为了方便，下面的每次测试前，都执行COMMIT;提交之前启用的事务，再执行前两步重新建表并重新开始事务）
（三） 测试非唯一索引的情况
- 在终端1执行语句：
SELECt * FROM `lock_test` WHERe `cid` = 9 lock in share mode;
理论上cid被锁区间：(5, 9]，(9, 11)；cid被锁行：9；id被锁行：3。
在终端2执行下表中的各个语句并可以看出执行是否被阻塞（拿不到锁）：
- 在终端1执行语句：
SELECT * FROM `lock_test` WHERe `cid` > 9 lock in share mode;
理论上cid被锁区间：(9, 11]，(11, 15]，(15, +∞)；cid被锁行：11，15；id被锁行：4，7。
在终端2执行下表中的各个语句并可以看出执行是否被阻塞（拿不到锁）：
因此我们验证了 b.3）。
（四） 测试唯一索引和主键的情况
- 在终端1执行语句：
SELECT * FROM `lock_test` WHERe `id` = 4 lock in share mode; -- 这个 id存在
理论上id没有被锁的区间（没有间隙锁）；id被锁行：4。
在终端2执行下表中的各个语句并可以看出执行是否被阻塞（拿不到锁）：
- 在终端1执行语句：
SELECT * FROM `lock_test` WHERe `id` = 6 lock in share mode; -- 这个 id不存在
理论上id被锁区间：(4, 7)；id没有被锁行。
在终端2执行下表中的各个语句并可以看出执行是否被阻塞（拿不到锁）：
- 在终端1执行语句：
SELECT * FROM `lock_test` WHERe `id` > 4 lock in share mode;
理论上id被锁区间：(4, 7]，(7, +∞)；id被锁行：7。
在终端2执行下表中的各个语句并可以看出执行是否被阻塞（拿不到锁）：
因此我们验证了 b.1）和 b.2），其中 b.2）是唯一索引而不是主键的情况此处就不单独验证了。 b.4）中不是索引的情况此处也不进行验证了。
示例2在Repeatable Read 隔离级别下：
- 如下读 *** 作，不加任何锁
select * from `lock_test`;
- 如下读 *** 作，对扫描经过的任何索引记录上加S锁（Next-Key Lock）
select * from `lock_test` LOCK IN SHARE MODE;
- 如下读 *** 作，对扫描经过的任何索引记录上加X锁（Next-Key Lock），并在其聚集索引上加X锁
select * from `lock_test` FOR UPDATe;
- 如下写 *** 作，对扫描经过的任何索引记录上加X锁（Next-Key Lock），并在其聚集索引上加X锁
update `lock_test` set .. where condition; delete from `lock_test` where condition;
- 如下写 *** 作，如果是间隙插入，先添加Insert Intention Lock，后在该行上添加X锁；如果是递增插入，添加Auto-Inc Lock 或者优化的轻量级互斥锁。
insert into `lock_test` ... ;
验证：
- 在终端1执行语句：
SELECt * FROM `lock_test` lock in share mode;
理论上id被锁区间：全部；id被锁行：全部。id全部被锁，cid自然也被锁。
在终端2执行下表中的各个语句并可以看出执行是否被阻塞（拿不到锁）：
前面已经提到过多版本并发控制MVCC用于实现一致性的非锁定读。所谓非锁定读就是指不需要等待欲读记录的X锁释放，也不需要加S锁。其基本实现原理就是去读快照数据而不是最新数据，这就是”多版本“的含义所在。
为什么读取快照数据就不需要加锁呢？因为不存在事务会需要对历史的数据进行修改 *** 作。
前面已经说明过，在READ COMMITTED和REPEATABLE READ隔离级别下InnoDB都会使用MVCC，但二者所读的快照版本是不同的。
MVCC的实现 —— undo logundo 日志存储在共享表空间中，是一个逻辑日志，回滚时根据 undo log 的记录，执行之前 *** 作的逆运算，将数据库恢复到原来的样子。比如事务中有 insert *** 作，那么对应地执行 delete *** 作；对于 update *** 作执行相反的 update *** 作。
同时 undo 日志还记录了行记录的版本信息，用于实现 MVCC 功能。
3 事务持久性的实现 —— redo log当事务提交时，必须先将该事务的所有日志写入到redo日志文件进行持久化。其实现在内存中包含了 redo log buffer，磁盘中包含 redo log file。
redo log采用顺序写，记录的是对每个页的修改（包括页号、页偏移量以及修改的内容）。在数据库运行时不需要对 redo log 的文件进行读取 *** 作；只有发生宕机的时候，才会借助 redo log进行恢复。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）