事务与锁机制

MySQL Transaction

事务（Transaction）：是由一系列对数据库中数据进⾏访问（查询）与更新（增删改）的操作所组成的⼀个程序执行逻辑单元。这些操作，要么都成功，要么都不成功。

-- 事务相关命令：
begin;                -- 开始，还可以使用下列语法：
start transaction; 

commit;               -- 提交：使得当前的修改确认

rollback;             -- 回滚：使得当前的修改被放弃

事务的ACID特性

事务具有四个重要特征：原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）。简称ACID特性

事务的并发问题

在不同的隔离级别下，多个事务并发执行时可能会产生一些问题，如：脏读，不可重复读，幻读

• 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。
• 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。

事务的隔离级别

为了处理并发问题，SQL标准定义了四种隔离级别，每种级别提供了不同程度的隔离性：

MySQL中InnoDB存储引擎默认支持的隔离级别是 REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，通过Next-Key Lock 有效地防止了幻读问题

MVCC机制详解

多版本并发控制（Multi-Version Concurrency Control，简称 MVCC，Copy on Write的思想）通过维护数据的不同版本来实现对同一数据的无锁并发访问，大大提高了数据库的吞吐量及读写性能。

MVCC最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，MVCC是 Read Commited 和 Repeatable Read 两种隔离级的实现基础。

数据行隐藏字段

InnoDB 存储引擎在每个数据行中添加了几个隐藏字段，用于支持 MVCC：

• DB_TRX_ID：记录最近一次对本记录进行插入或修改的事务ID。（占六个字节） 删除操作也会被视为一个update操作，只是有额外的删除标记（Delete Bit）

• DB_ROLL_PTR：指向该行的回滚段（undo log）的指针，用于找到该行的旧版本。(占用七个字节)

• DB_ROW_ID：随着新行插入而单调递增的行ID，数据行没有主键时，为行提供一个唯一的标识 DB_ROW_ID占用六个字节, 若无聚簇索引则作为聚簇索引使用。

UndoLog与版本链

事务在对数据操作前，undolog会记录==可以通过事务回滚到上一个版本数据==的信息

• insert undo log: 事务insert新记录时产生的undolog, 只在事务回滚时需要，并且在事务提交后就可以立即丢弃
• update undo log: 事务进行delete和update操作时产生的undo log 不仅事务回滚需要，快照读也需要，只有当数据库所使用的快照中不涉及 对应的undo log才会被purge线程删除

Read View

readview视图存在如下4个字段

• m_ids：m_ids中维护了一个有序数组（升序），该数组保存了ReadView生成时，活跃的事务id
• m_low_limit_id（max_trx_id）：预分配事务id，等于当前最大事务id + 1（事务是自增的）
• m_up_limit_id（min_trx_id）：最小活跃事务id
• m_creator_trx_id：ReadView创建者的id

• 读已提交（RC）：==每次查询创建新的读视图==，事务内的多次查询可能会看到不同的数据版本。
• 可重复读（RR）：事务开始时创建读视图，事务内的多次查询会看到相同的快照。

因此在MVCC机制中，在可重复读隔离级别下同一个事务内多次查询使用的均是同一个视图，解决了不可重读的问题和部分幻读问题。

参照Blog：https://blog.csdn.net/qq_62835094/article/details/136669636

可见性规则

MVCC机制通过下面的算法，分析版本链中访问版本的 DB_TRX_ID 属性值与Read view数据，确定版本的可见性(能否被当前事务访问)：

示例

快照读与当前读

快照读快照读（Snapshot Read）： 指读取一个事务开始时的数据快照，而不是最新的数据。这意味着它不会看到其他事务在此事务启动后所做的任何更改，即使这些更改已经被提交。

默认情况下，SELECT 是非锁定的一致性读（consistent nonlocking read）。这种类型的读取能够提高并发性能，因为它允许多个事务同时读取而不会相互阻塞。

• REPEATABLE-READ级别: 事务begin后，第一条SELECT语句会创建一个快照，读取满足可见性的数据，之后该事务会一直用这个快照，直到事务结束

• READ-COMMITTED级别: 事务begin后，每条SELECT语句都会重置快照(重新创建快照)， 读取当前满足可见性的数据

当前读（Current Read）：总是读取最新的、已经提交的数据版本。这不仅包括了本事务所做的更改，也包括所有其他已经提交事务的结果。

为了确保读取到最新数据，当前读可能会加锁以防止其他事务对所读取的数据进行修改。因此，当前读通常是锁定读（locking read），即在读取的同时还会获取适当的锁来保护数据的一致性和完整性。主要包括以下几种情况：

SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR SHARE;
SELECT ... FOR UPDATE;

INSERT ... ;
UPDATE ... ;
DELETE ... ;

MySQL中当前读一般说的就是基于事务的一致性读（Consistent Read） 和 锁定读（Locking Read）

快照读查看的是事务开始时的数据快照，而当前读则总是读取最新的已提交数据。

MySQL锁的分类

在 MySQL 中，锁机制是保证数据一致性和并发控制的重要手段。根据不同的角度，MySQL 中的锁可以分为多种类型。

InnoDB 中的锁机制是确保数据一致性和并发控制的关键部分。根据不同的层次和用途，InnoDB 提供了多种类型的锁。InnoDB Locking

• 共享排他锁（Shared and Exclusive Locks） 用于控制并发访问，确保数据的一致性和隔离性。
• 意向锁（Intention Locks）用于协调不同粒度的锁，避免死锁和锁冲突。
• 记录锁（Record Locks）防止多个事务同时修改同一数据项，确保数据一致性。
• 间隙锁（Gap Locks）用于防止幻读现象，特别是在范围查询中。
• 临键锁（Next-Key Locks）通过锁定具体的索引记录及其之前的间隙，有效防止幻读现象。
• 插入意向锁：用于优化并发插入操作，防止多个事务在同一个间隙内插入相同的记录。
• 自增锁：确保 AUTO_INCREMENT 列生成唯一的值，通过预分配自增值提高并发性能。
• 谓词锁：支持空间索引中的复杂查询，确保对空间数据的操作具有一致性和隔离性。

共享排他锁

共享排他锁（Shared and Exclusive Locks）属于行级锁，分为共享锁和排他锁:

• 共享锁(SLock)：允许持有该锁的事务读取一行数据

• 排他锁(XLock)：允许该锁的事务删除或更新一行数据

• Innodb会自动给 delete,update 语句涉及到的数据集加 排他锁（XLock）

• 普通的 select 语句不会加锁，但可以使用下列方式加锁（参照：locks-set）：

SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR SHARE;             -- MySQL8

insert 语句也会自动加锁，但不是共享锁/排他锁。

意向锁

意向锁（Intention Locks）表示未来某个时刻，事务要加共享锁/排他锁，所以先提前声明==意向==

• 意向共享锁（intention shared lock ，IS）表示事务有意向表中的某些行加共享锁

• 意向排他锁（intention exclusive lock，IX）表示事务有意向表中的某些行加排他锁

意向锁是一个表级锁，==事务在请求S锁/X锁前，需要先获得对应的IS，IX锁==

Table-level lock type compatibility （Conflict：❌，Compatible：✔）：

意向锁最明显的作用在于==简化锁兼容性检查== ，通过使用意向锁，数据库引擎可以在更高层次（如表级别）快速判断是否可能存在冲突，而不需要逐行检查每个锁的状态。

记录锁和索引

记录锁（Record Locks）==锁定的是索引== ，防止其他事务针对同一条记录同时进行增删改操作。

当where条件使用二级索引时，InnoDB 不仅会对二级索引上的记录加锁，还会进行回表操作来锁定聚集索引上的相应记录。

间隙锁

间隙锁（Gap Locks）：锁住索引之间的 ==间隙（键值在条件范围内但不存在的索引记录）==

表的某个索引树通常包括以下间隙：索引记录之间的间隙，第一条索引记录之前的范围，最后一条索引记录之后的范围。例：假设有一个表中包含以下记录（索引有 2， 4，7）：

则存在以下索引间隙： $( -\infty , 2) , (2, 4), (4, 7), (7, \infty)$ ，一个间隙可能覆盖单个索引值、多个索引值，甚至可能是空的。

SELECT ... FOR UPDATE;

临键锁

临键锁（Next-Key Locks）是索引记录上的记录锁与该索引记录前的间隙锁的组合。通过锁定具体的索引记录及其之前的间隙，临键锁可以有效地防止其他事务在此间隙内插入新记录，从而避免幻读问题。

当一个事务执行范围查询（如 SELECT ... WHERE ... BETWEEN ... AND ... FOR UPDATE）时，InnoDB 会对查询范围内所有索引记录加临键锁。这不仅包括实际存在的记录，还包括它们之间的间隙。因此，即使在查询范围内没有匹配的记录，InnoDB 也会对相应的间隙加锁，以确保其他事务不能在此范围内插入新记录。

示例：假设有一个表 employees，其中 id 是主键，并且包含以下 id 值：10、11、13、20。考虑以下事务：

-- 事务 A
START TRANSACTION;
SELECT * FROM employees WHERE id BETWEEN 10 AND 20 FOR UPDATE;

-- 事务 B
START TRANSACTION;
INSERT INTO employees (id, first_name, last_name) VALUES (12, 'Charlie', 'Davis');

在这个例子中，事务 A 对 id 在 10 到 20 范围内的所有索引记录加了临键锁，具体包括：

• 记录锁：id = 10、id = 11、id = 13、id = 20
• 间隙锁：(10, 11]、(11, 13]、(13, 20]

因此，当事务 B 尝试插入 id = 12 的记录时，会被阻塞，直到事务 A 提交或回滚。这有效地防止了幻读现象的发生。

• 防止幻读：通过锁定索引记录及其之前的间隙，临键锁有效防止了其他事务在同一范围内插入新记录，从而避免幻读问题。
• 提高并发性：相比于完全锁定整个表，临键锁只锁定必要的记录和间隙，提高了系统的并发性能。

临键锁是 InnoDB 实现高并发性和数据一致性的重要工具，特别是在 REPEATABLE READ 隔离级别下。通过结合记录锁和间隙锁，它可以有效防止幻读现象，确保事务的一致性和隔离性。

插入意向锁

插入意向锁（Insert Intention Locks）是 InnoDB 存储引擎中的一种特殊类型的间隙锁（Gap Lock），用于优化并发插入操作。它表明一个事务打算在某个特定的间隙内插入一条新记录，但并不锁定整个间隙。

-- 事务 A
START TRANSACTION;
INSERT INTO employees (id, first_name, last_name) VALUES (3, 'Charlie', 'Davis');

-- 事务 B
START TRANSACTION;
INSERT INTO employees (id, first_name, last_name) VALUES (4, 'Eve', 'Smith');

事务 A 和事务 B 都会在在索引范围 (2, 5) 上加一个插入意向锁。由于它们插入的位置不同，这两个事务可以并发执行而不会互相阻塞。

自增锁和谓词锁

自增锁（AUTO-INC Locks）用于确保在多线程环境中，AUTO_INCREMENT 列生成唯一的值。InnoDB 使用两种模式来处理自增锁：

• 传统模式（Traditional Mode）：每个插入操作都会获取一个表级的自增锁，这可能会导致较高的争用和较低的并发性。
• 批量模式（Batch Mode）：从 MySQL 5.7 开始，默认使用批量模式。在这种模式下，InnoDB 会预先分配一批自增值，并允许多个事务并发地使用这些值，从而提高了并发性能。

谓词锁（Predicate Locks for Spatial Indexes）：主要用于支持空间索引（Spatial Indexes），如 GIS 数据类型。这种锁机制确保了对空间数据的操作具有一致性和隔离性，特别是在涉及复杂的空间查询时。

• 作用：防止其他事务在相同的空间范围内进行修改操作，从而避免数据不一致或冲突。
• 特点：谓词锁基于查询条件（即谓词）而不是具体的行或记录。这意味着它可以覆盖更大的范围，提供更细粒度的控制。 InnoDB 使用谓词锁来支持复杂的查询，如范围查询、邻近查询等，确保在事务期间数据的一致性。

示例场景：假设有一个包含地理坐标的数据表 locations，其中 geom 列是一个空间索引。考虑以下事务：

-- 事务 A
START TRANSACTION;
SELECT * FROM locations WHERE MBRContains(ST_GeomFromText('Polygon((...))'), geom) FOR UPDATE;

-- 事务 B
START TRANSACTION;
INSERT INTO locations (geom) VALUES (ST_GeomFromText('Point(...)'));

-- 如果事务 B 的插入点位于事务 A 查询的多边形范围内，事务 B 将被阻塞，直到事务 A 提交或回滚。

在这个例子中，事务 A 对满足 MBRContains 条件的空间范围加了谓词锁，因此事务 B 在尝试插入位于该范围内的新点时会被阻塞，直到事务 A 结束。

死锁分析

幻读问题回顾与总结

MySQL的并发控制机制主要就是 MVCC 和 锁 ， 这两者又都是解决幻读问题的关键

-- 事务 A
START TRANSACTION;
SELECT * FROM table_name WHERE id > 100;

-- 事务 B
INSERT INTO table_name (id, value) VALUES (101, 'New Row');

-- 再次读取
SELECT * FROM table_name WHERE id > 100; -- 查询结果不会包含 id = 101 的新行

-- 事务 A
START TRANSACTION;
SELECT * FROM table_name WHERE id > 100 FOR UPDATE;

-- 事务 B
INSERT INTO table_name (id, value) VALUES (101, 'New Row'); 
-- 被阻塞，因为 101 在 A 的锁范围内

-- 事务 A 再次读取
SELECT * FROM table_name WHERE id > 100 FOR UPDATE; -- 查询结果一致，且未发生幻读

在 MySQL 的 REPEATABLE READ (RR) 隔离级别下，有人认为没有彻底解决幻读问题。并提出了以下几种情形：

情形一 前后两次查询混合使用当前读和快照读

-- 事务 A
START TRANSACTION;
SELECT * FROM table_name WHERE id > 100; -- 没有查到id为101的记录

-- 事务 B （插入完自动提交）
INSERT INTO table_name (id, value) VALUES (101, 'New Row');

-- 事务A 再次读取
SELECT * FROM table_name WHERE id > 100 FOR UPDATE; -- 这里又能查到 id = 101 的新行

第二次查询使用了 FOR UPDATE，这是一个锁定读，它会读取最新的数据并加锁，因此可以看到新插入的记录。这是因为混合使用了快照读和当前读！

情形二 两次查询中间本事务无法插入新数据：

-- 事务 A
START TRANSACTION;
SELECT * FROM table_name WHERE id > 100; -- 没有查到id为101的记录

-- 事务 B （插入完自动提交）
INSERT INTO table_name (id, value) VALUES (101, 'New Row');

-- 事务A 再次读取
SELECT * FROM table_name WHERE id > 100; -- 还是查询不到 id = 101 的新行
-- 事务A插入数据失败！
INSERT INTO table_name (id, value) VALUES (101, 'New A');

因为查询不到数据，进行插入时又发现插入不了，但这里本质原因还是没有使用锁定读，根本没有用到锁机制来解决问题，事务中要修改或者插入时，应该先通过select ... for update查询，而不是普通查询

情形三 两次查询中间本事务更新了数据：

-- 事务 A
START TRANSACTION;
SELECT * FROM table_name WHERE id > 100; -- 没有查到id为101的记录

-- 事务 B （插入完自动提交）
INSERT INTO table_name (id, value) VALUES (101, 'New Row');

-- 事务A 再次读取
SELECT * FROM table_name WHERE id > 100; -- 还是查询不到 id = 101 的新行
UPDATE table_name SET name = 'new' WHERE id = 101; -- 但是这里能修改！！
SELECT * FROM table_name WHERE id > 100; -- 这里又能查到 id = 101 的新行

• 第一次查询：这是一个普通的 SELECT，它执行的是快照读（Snapshot Read），因此看不到事务 B 插入的新记录。

• 第二次查询：仍然是快照读，所以仍然看不到新插入的记录。

• 更新操作：这是一个锁定读/当前读（Current Read），它会读取最新的数据并加锁，因此可以看到并修改新插入的记录。 实际上这里根本不属于常规操作，要更新数据，从开始就应该使用当前读。从正常的逻辑也说不通，都没查到数据你去更新什么？谁会这么写代码？

• 第三次查询：由于之前执行了锁定读（更新操作），事务 A 已经看到了最新数据，因此这次查询也能看到新插入的记录。即便这里还要用MVCC的readview也能解释，当前事务修改过了数据，那最新版本中的隐藏字段中的事务ID自然变成了当前事务ID，按照可见性规则也能读取到该数据

这些情形并没有说明 RR 隔离级别没有彻底解决幻读问题。相反，它们展示了 RR 隔离级别下快照读和锁定读的不同行为。

如果希望在整个事务期间保持绝对的一致性视图，可以考虑使用更高的隔离级别 SERIALIZABLE