Photo by Lukas Hartmann from Pexels
辞职这段时间以来看见了很多工作之外的东西，我认为这是值得的。同时也有时间和机会来好好整理所学所想，准备开启下一段旅途。
事务的定义及特性
数据库事务代表在数据库管理系统（或类似系统）中对数据库进行的一致可靠的工作单元，独立于其他事务的处理。事务通常表示对数据库的任何更改。数据库环境中的事务有两个主要目的：
- 提供可靠的工作单元，以便在系统故障的情况下能够正确恢复并保持数据库的一致性。例如：当执行突然中止（完全或部分）时，许多对数据库的操作未完成，状态不明确。
- 提供并发访问数据库的程序之间的隔离。如果不提供这种隔离性，程序的结果可能是错误的。
在数据库管理系统中，事务是逻辑或工作的单个单位，有时由多个操作组成。在数据库中以一致的方式执行的任何逻辑计算都被称为事务。一个例子是从一个银行账户转账到另一个账户：完整的事务需要从一个账户中减去要转账的金额，并将同样的金额添加到另一个账户中。
根据定义，数据库事务必须具有原子性（要么完全完成，要么没有任何影响）、一致性（必须符合数据库中的现有约束）、隔离性（不能影响其他事务）和持久性（必须写入持久存储介质）。数据库专业人员经常使用ACID这个缩写来提及数据库事务的这些特性。
MySQL中的事务支持
MySQL是一个流行的关系型数据库管理系统，它支持多种数据库引擎，其中InnoDB是最常用的支持事务的引擎。MySQL提供了丰富的事务控制语句和隔离级别，让用户可以灵活地管理事务的开始、提交、回滚、嵌套等行为，并调整事务对并发性和数据一致性的影响。MySQL还提供了事务日志、锁机制、隐式和显式锁定等功能，来保证事务的持久性、并发控制和性能优化。在本文中，我们将详细介绍和分析MySQL中的事务相关的知识和技巧。
事务的ACID属性
原子性（Atomicity）
事务通常由多个语句组成。原子性保证每个事务都被视为一个“单元”，它要么完全成功，要么完全失败：如果构成事务的任何语句未能完成，则整个事务失败，数据库保持不变。原子系统必须保证每种情况下的原子性，包括电源故障、错误和崩溃。[5] 原子性的保证可以防止对数据库的更新仅部分发生，这可能会导致比完全拒绝整个系列更大的问题。因此，另一个数据库客户端无法观察到该事务正在进行中。在某一时刻，它还没有发生，而在下一个时刻，它已经全部发生了（或者如果交易在进行中被取消，则什么也没发生）。
一致性（Consistency）
一致性确保事务只能将数据库从一个一致状态带到另一个一致状态，保持数据库不变性：写入数据库的任何数据都必须根据所有定义的规则有效，包括约束、级联、触发器及其任意组合。这可以防止非法事务损坏数据库。参照完整性保证主键-外键关系。[6]
隔离性（Isolation）
事务通常同时执行（例如，多个事务同时读取和写入表）。隔离可确保事务的并发执行使数据库处于按顺序执行事务时获得的相同状态。隔离是并发控制的主要目标;根据所使用的隔离级别，不完整事务的影响可能对其他事务不可见。[注7]
持久性（Durability）
持久性保证一旦事务提交，即使在系统故障（例如，断电或崩溃）的情况下，事务也会保持提交状态。这通常意味着已完成的交易（或其影响）记录在非易失性存储器中。[需要引用]
事务隔离级别
隔离级别（Isolation Level）是一种用于在数据库中控制事务之间的可见性和并发性的设置，它决定了一个事务可以看到其他事务对数据所做的修改的程度。隔离级别越高，数据的一致性和可重复性就越好，但是并发性能就越低；反之亦然。
SQL标准定义了四种隔离级别(https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html)：
- 读未提交（Read Uncommitted）：最低的隔离级别，一个事务可以看到其他事务未提交的修改，可能导致脏读（Dirty Read）、不可重复读（Non-repeatable Read）和幻读（Phantom Read）等问题
- 读已提交（Read Committed）：一个事务只能看到其他事务已经提交的修改，可以避免脏读，但是可能导致不可重复读和幻读等问题
- 可重复读（Repeatable Read）：一个事务在开始时创建一个数据快照，并且在整个事务期间保持不变，可以避免脏读和不可重复读，但是可能导致幻读等问题
- 可串行化（Serializable）：最高的隔离级别，一个事务在执行期间对其他事务不可见，并且对数据进行加锁，可以避免所有的并发问题，但是并发性能最低
MySQL支持使用SET TRANSACTION语句来设置当前会话或者全局的隔离级别。
例如：
-- 设置当前会话的隔离级别为可重复读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 设置全局的隔离级别为读已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
各个隔离级别下的事务并发问题及解决方案
这里从分析问题和学习的角度出发，简单的列举了各个隔离级别下的事务并发问题，但是其实在实际开发中，基本不用考虑这些，大部分的时候都默认为可重复读级别，因为隔离级别低了数据不安全，高了性能保证不了。
如果想要兼顾安全和性能，那应该从业务架构层面寻找优化点，不到万不得已没必要试图死磕MySQL，所以如果大致理解了隔离级别就可以跳过本节。
读未提交（Read Uncommitted）
读未提交（Read Uncommitted）是最低的隔离级别，一个事务可以看到其他事务未提交的修改，可能导致以下并发问题：
- 脏读（Dirty Read）：一个事务读取到另一个事务未提交的数据，然后另一个事务回滚了，导致前一个事务读取到了无效的数据
- 不可重复读（Non-repeatable Read）：在同一个事务中，两次读取同一条记录得到了不同的结果，因为其他事务在此期间修改或删除了该记录
- 幻读（Phantom Read）：在同一个事务中，两次读取同一范围的记录集合得到了不同的结果，因为其他事务在此期间插入或删除了记录
下面用一个例子来说明：
假设有以下表：
create table user (
id int primary key,
name varchar(20),
age int
) engine=innodb;
insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);
假设有两个并发事务T1和T2，它们都使用读未提交（Read Uncommitted）隔离级别，并且执行以下操作：
sequenceDiagram participant T1 participant T2 T1->>+T1: start transaction; T2->>+T2: start transaction; T2->>+T2: update user set age = age + 1 where id = 1; Note right of T2: Result: <br/>id ' name ' age <br/>1 ' Alice ' 21 T1->>+T1: select * from user where id = 1; Note right of T1: Result: <br/>id ' name ' age <br/>1 ' Alice ' 21 Note right of T1: Dirty Read: <br/>T1 read the uncommitted data from T2 T2->>+T2: rollback; Note right of T2: Result: <br/>id ' name ' age <br/>1 ' Alice ' 20 T1->>+T1: select * from user where id = 1; Note right of T1: Result: <br/>id ' name ' age <br/>1 ' Alice ' 20 Note right of T1: Non-repeatable Read: <br/>T1 read the different data from the same record T2->>+T2: start transaction; T2->>+T2: insert into user values (4, 'David', 23); Note right of T2: Result: <br/>id ' name ' age <br/>4 ' David ' 23 T1->>+T1: select * from user; Note right of T1: Result: <br/>id ' name ' age <br/>1 ' Alice ' 20 <br/>2 ' Bob ' 21 <br/>3 ' Charlie ' 22 <br/>4 ' David ' 23 Note right of T1: Phantom Read: <br/>T1 read the different data from the same range T2->>+T2: rollback; Note right of T2: Result: <br/>The record with id = 4 is deleted T1->>+T1: commit;从上面的例子可以看到，T1和T2在读未提交隔离级别下，会发生脏读、不可重复读和幻读等并发问题，导致数据的不一致性和不可靠性。
解决方案：
- 不要使用读未提交隔离级别，除非你对数据的一致性和可靠性没有任何要求
- 使用更高的隔离级别，如读已提交、可重复读或者可串行化，来避免脏读、不可重复读或者幻读等问题
- 使用锁机制，如表级锁或者行级锁，来控制对数据的并发访问和修改
读已提交（Read Committed）
读已提交（Read Committed）是一种隔离级别，一个事务只能看到其他事务已经提交的修改，可以避免脏读，但是可能导致以下并发问题：
- 不可重复读（Non-repeatable Read）：在同一个事务中，两次读取同一条记录得到了不同的结果，因为其他事务在此期间修改或删除了该记录
- 幻读（Phantom Read）：在同一个事务中，两次读取同一范围的记录集合得到了不同的结果，因为其他事务在此期间插入或删除了记录
下面用一个例子来说明：
假设有以下表：
create table user (
id int primary key,
name varchar(20),
age int
) engine=innodb;
insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);
假设有两个并发事务T1和T2，它们都使用读已提交（Read Committed）隔离级别，并且执行以下操作：
sequenceDiagram participant T1 participant T2 T1->>+T1: start transaction; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [] T1->>+T1: select * from user where id = 1; Note right of T1: Result: <br/>id ' name ' age <br/>1 ' Alice ' 20 T2->>+T2: start transaction; Note right of T2: Read View: <br/>creator_trx_id: 2 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [1] T2->>+T2: update user set age = age + 1 where id = 1; Note right of T2: Result: <br/>id ' name ' age <br/>1 ' Alice ' 21 T2->>+T2: commit; Note right of T2: Read View: <br/>creator_trx_id: 2 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [1] T1->>+T1: select * from user where id = 1; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 5 <br/>up_limit_id: 3 <br/>trx_ids: [] Note right of T1: Result: <br/>id ' name ' age <br/>1 ' Alice ' 21 Note right of T1: Non-repeatable Read: <br/>T1 read the different data from the same record T2->>+T2: start transaction; Note right of T2: Read View: <br/>creator_trx_id: 2 <br/>low_limit_id: 5 <br/>up_limit_id: 3 <br/>trx_ids: [] T2->>+T2: insert into user values (4, 'David', 23); Note right of T2: Result: <br/>id ' name ' age <br/>4 ' David ' 23 T2->>+T2: commit; Note right of T2: Read View: <br/>creator_trx_id: 2 <br/>low_limit_id: 5 <br/>up_limit_id: 3 <br/>trx_ids: [] T1->>+T1: select * from user; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 6 <br/>up_limit_id: 4 <br/>trx_ids: [] Note right of T1: Result: <br/>id ' name ' age <br/>1 ' Alice ' 21 <br/>2 ' Bob ' 21 <br/>3 ' Charlie ' 22 <br/>4 ' David ' 23 Note right of T1: Phantom Read: <br/>T1 read the different data from the same range T1->>+T1: commit;从上面的例子可以看到，T1和T2在读已提交隔离级别下，会发生不可重复读和幻读等并发问题，导致数据的不一致性和不可靠性。
解决方案：
- 使用更高的隔离级别，如可重复读或者可串行化，来避免不可重复读或者幻读等问题
- 使用锁机制，如表级锁或者行级锁，来控制对数据的并发访问和修改
可重复读（Repeatable Read）
可重复读（Repeatable Read）是一种隔离级别，一个事务在开始时创建一个数据快照，并且在整个事务期间保持不变，可以避免脏读和不可重复读，但是可能导致以下并发问题：
- 幻读（Phantom Read）：在同一个事务中，两次读取同一范围的记录集合得到了不同的结果，因为其他事务在此期间插入或删除了记录
注意：网上有部分文章说MySQL中的可重复读利用MVCC解决了幻读的问题，其实并没有完全解决，MVCC并不能完全消除幻读的可能性。
具体分析可以看本文的MVCC章节，这里不重复说明。
解决方案：
- 使用更高的隔离级别，如可串行化，来避免幻读等问题
- 使用锁机制，如表级锁或者行级锁，来控制对数据的并发访问和修改
可串行化（Serializable）
可串行化（Serializable）是最高的隔离级别，一个事务在执行期间对其他事务不可见，并且对数据进行加锁，并发性能最低，可以避免所有的并发问题，所以这里就无需举例了。
事务的基本操作
在MySQL中，我们可以通过一些简单的命令来操作事务，如：
- 开始一个事务：使用START TRANSACTION语句或BEGIN语句来开始一个事务。事务开始后，MySQL将自动将后续的操作视为一个事务。
- 提交一个事务：使用COMMIT语句来提交一个事务。提交操作将永久保存对数据库的更改，并结束当前的事务。
- 回滚一个事务：使用ROLLBACK语句来回滚一个事务。回滚操作将取消对数据库的更改，并撤销当前事务中的所有操作。
以下是一个示例，展示了如何使用这些语句来操作一个事务：
-- 开始事务
START TRANSACTION;
-- 执行一系列的SQL操作
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;
-- 判断是否满足某些条件
IF condition THEN
-- 执行其他操作
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
ELSE
-- 回滚事务
ROLLBACK;
END IF;
-- 提交事务
COMMIT;
MySQL默认使用自动提交模式（Auto-Commit Mode），即每个SQL语句都被视为一个单独的事务并自动提交。如果要使用显式事务控制，需要在执行任何DML操作之前显式地开始一个事务，并在适当的时候选择提交或回滚事务。
事务的嵌套
MySQL允许在一个事务中嵌套其他事务，即在一个事务内部启动另一个事务。这样的嵌套事务可以通过SAVEPOINT和ROLLBACK TO SAVEPOINT语句进行控制。嵌套事务的主要目的是在更细粒度的操作中实现事务的管理和控制。
保存点是在事务中设置的一个标记，用于标识事务中的一个特定位置。通过设置保存点，您可以在事务进行过程中创建一个可以回滚到该点的标记，以便在发生错误或其他情况时进行回滚操作。
以下是一个示例，展示了事务的嵌套：
START TRANSACTION;
-- 执行一些操作
SAVEPOINT savepoint1;
-- 执行更细粒度的操作
SAVEPOINT savepoint2;
-- 执行更细粒度的操作
ROLLBACK TO SAVEPOINT savepoint1;
-- 回滚到savepoint1，取消savepoint2后的操作
COMMIT;
在上述示例中，我们在一个事务中创建了两个保存点（savepoint1和savepoint2），并在这些保存点之间执行了一些更细粒度的操作。如果需要回滚操作，可以选择回滚到特定的保存点，而不是完全回滚整个事务。
事务日志
事务日志是MySQL数据库的重要组成部分，它们记录了数据库中数据的变化情况，保证了事务的持久性和一致性。
事务日志对事务持久性的重要性是显而易见的。如果没有事务日志，那么当系统发生故障时，内存中的数据页就会丢失，而磁盘上的数据页可能还没有被更新，这样就会导致数据不一致和丢失。而有了事务日志，就可以在系统恢复后，根据日志中的记录将数据页恢复到最近一次提交的状态，保证了数据的完整性和一致性。
Redo Log
Redo Log是重做日志，属于InnoDB存储引擎的日志。它是物理日志，记录了数据页的更改情况，比如向数据页增加或删除一条记录。Redo Log包括两部分：一个是内存中的日志缓冲区（Redo Log Buffer），另一个是磁盘上的日志文件（Redo Log File）。Redo Log采用固定大小，循环写入的方式，当写到结尾时，会回到开头继续写。
Redo Log的主要作用有两个：
- 前滚操作：具备crash-safe能力，提供断电重启时解决事务丢失数据问题。当数据库发生异常崩溃时，可以根据Redo Log恢复数据到最近一次提交的状态。
- 提高性能：先写Redo Log记录更新。当等到有空闲线程、内存不足、Redo Log满了时刷脏。写Redo Log是顺序写入，刷脏是随机写，节省了随机写磁盘的IO消耗（转成顺序写），所以性能得到提升。这种先写日志，再写磁盘的技术就是WAL（Write-Ahead Logging）技术。
Redo Log的工作原理如下：
- 当InnoDB执行一条DML语句时（比如INSERT、UPDATE、DELETE），首先会将该语句对应的记录写入Redo Log Buffer中。
- 当事务提交时（或者达到刷盘时机），会将Redo Log Buffer中的记录批量写入OS Buffer中，并调用fsync()函数将其刷入Redo Log File中。
- 当InnoDB需要将内存中修改过的数据页刷新到磁盘时（比如内存不足、系统空闲、检查点触发等），会先检查该数据页对应的LSN（逻辑序列号）是否小于Redo Log File中已经刷入磁盘的LSN。如果小于，则说明该数据页已经被Redo Log保护了，可以直接刷新；如果大于，则说明该数据页还没有被Redo Log保护，需要先将Redo Log Buffer中该数据页相关的记录刷入磁盘。
- 当数据库发生异常崩溃时，会导致内存中的数据页丢失，此时需要根据Redo Log File中的记录进行恢复。恢复的过程是从最近一个检查点开始，扫描Redo Log File中的记录，将已经提交的事务对应的记录重做到数据页上，将未提交的事务对应的记录忽略。这样就可以将数据页恢复到最近一次提交的状态。
Redo Log的工作原理可以用以下mermaid流程图表示：
graph TD A[InnoDB执行DML语句] --> B[写入Redo Log Buffer] B --> C[事务提交或刷盘时机] C --> D[写入OS Buffer并刷入Redo Log File] D --> E[刷新数据页到磁盘] E --> F[检查LSN是否小于已刷入磁盘的LSN] F -->|是| G[直接刷新数据页] F -->|否| H[先将Redo Log Buffer中相关记录刷入磁盘] H --> G D --> I[数据库异常崩溃] I --> J[根据Redo Log File进行恢复] J --> K[从最近一个检查点开始扫描Redo Log File] K --> L[重做已提交事务对应的记录] K --> M[忽略未提交事务对应的记录] L --> N[恢复数据页到最近一次提交的状态] M --> NUndo Log
Undo Log是回滚日志，属于InnoDB存储引擎的日志。它是逻辑日志，记录了数据页的逆向操作，比如一条修改+3的语句，Undo Log会记录对应一条-3的语句，一条插入语句则会记录一条删除语句。这样发生错误时，根据执行Undo Log就可以回滚到事务之前的数据状态。
Undo Log的主要作用有两个：
- 回滚数据：当程序发生异常错误时等，根据执行Undo Log就可以回滚到事务之前的数据状态，保证原子性，要么成功要么失败。
- MVCC一致性视图：通过Undo Log找到对应的数据版本号，是保证MVCC视图的一致性的必要条件。
Undo Log的工作原理如下：
- 当InnoDB执行一条DML语句时（比如INSERT、UPDATE、DELETE），首先会将该语句对应的逆向操作记录写入Undo Log中。Undo Log是存储在回滚段（Rollback Segment）中的，回滚段是InnoDB存储引擎的一个特殊区域，它包含了多个回滚段槽（Rollback Slot），每个回滚段槽又包含了多个回滚指针（Roll Pointer），每个回滚指针指向一个Undo Log。
- 当事务需要回滚时（比如执行ROLLBACK语句或者发生异常错误），会根据Undo Log中的记录逐条执行逆向操作，将数据页恢复到事务之前的状态。
- 当事务提交时（或者达到清理时机），会将Undo Log中的记录标记为可清理，并释放占用的空间。清理时机有两种：一种是当事务提交后，如果该事务没有影响其他事务的MVCC视图，则可以立即清理；另一种是当事务提交后，如果该事务影响了其他事务的MVCC视图，则需要等待所有依赖该事务的MVCC视图消失后才能清理。
- 当数据库发生异常崩溃时，会导致部分未提交或未清理的Undo Log残留在回滚段中，此时需要根据Redo Log File中的记录进行恢复。恢复的过程是从最近一个检查点开始，扫描Redo Log File中的记录，将已经提交但未清理的Undo Log标记为可清理，并释放占用的空间；将未提交但已写入Undo Log的事务回滚，并释放占用的空间。
Undo Log的工作原理可以用以下mermaid流程图表示：
graph TD A[InnoDB执行DML语句] --> B[写入Undo Log到回滚段] B --> C[事务需要回滚] C --> D[根据Undo Log执行逆向操作] D --> E[恢复数据页到事务之前的状态] B --> F[事务提交或清理时机] F --> G[标记Undo Log为可清理并释放空间] G --> H[清理时机有两种:立即清理或延迟清理] H -->|立即清理| I[该事务没有影响其他事务的MVCC视图] H -->|延迟清理| J[该事务影响了其他事务的MVCC视图] J --> K[等待所有依赖该事务的MVCC视图消失] K --> I B --> L[数据库异常崩溃] L --> M[根据Redo Log File进行恢复] M --> N[从最近一个检查点开始扫描Redo Log File] N --> O[标记已提交但未清理的Undo Log为可清理并释放空间] N --> P[回滚未提交但已写入Undo Log的事务并释放空间] O --> Q[恢复回滚段到最近一次提交的状态] P --> Q并发控制与锁
在MySQL中，有几种常见的并发控制方式用于处理多个并发事务之间的数据访问冲突。下面是一些常用的并发控制方式：
- 锁定（Locking）：MySQL使用锁机制来确保在某个事务修改数据时其他事务不能同时访问或修改同一数据。锁可以分为共享锁（Shared Lock）和排他锁（Exclusive Lock），分别用于读取和修改操作。通过锁定机制，MySQL可以保证数据的一致性和并发性，但可能会导致锁冲突和性能问题。
- 乐观并发控制（Optimistic Concurrency Control）：乐观并发控制假设事务之间的冲突很少发生，并且在提交之前不进行实际的数据锁定。每个事务在提交之前会检查其他事务是否已修改了它要修改的数据，如果检测到冲突，则会回滚事务并重试。乐观并发控制通常使用版本号或时间戳来跟踪数据的变化。
- 多版本并发控制（Multi-Version Concurrency Control，MVCC）：MVCC通过为每个事务创建独立的数据版本来实现并发控制。每个事务都可以在自己的数据版本上进行读取和修改，而不会与其他事务产生冲突。MVCC使用读视图（Read View）来确定每个事务可以看到的数据版本，从而提供了一致的读取和隔离性。
- 时间戳排序（Timestamp Ordering）：时间戳排序是一种基于时间戳的并发控制方法，通过为每个事务分配唯一的时间戳来确定事务的执行顺序。根据时间戳的顺序，事务按照先后顺序进行提交，从而避免了冲突和死锁。
这些并发控制方式在MySQL中都有广泛的应用，并根据不同的场景和需求进行选择和配置。每种方式都有其优缺点，具体使用哪种方式取决于应用程序的特性和性能需求。
MySQL中的锁机制
锁（Lock）是一种用于在数据库中控制并发的机制，通过对表或者行进行加锁，可以限制对数据的非法访问或者修改。MySQL允许客户端会话显式地获取表锁，以便与其他会话协作访问表中的数据。
MySQL中的锁主要分为两种：
- 表级锁（Table-level Lock）：对整个表进行加锁，可以是写锁或者读锁
- 行级锁（Row-level Lock）：对表中的单个行或者多个行进行加锁，可以是共享锁或者排他锁
不同的存储引擎使用不同的锁机制，例如：
- MyISAM, MEMORY, MERGE等存储引擎使用表级锁
- InnoDB, NDB等存储引擎使用行级锁
- Archive存储引擎使用插入锁（Insert Lock），一种特殊的写锁
表级锁
表级锁是一种最简单的锁机制，它对整个表进行加锁，只允许一个会话在同一时间对表进行更新操作。这种锁机制使得这些存储引擎更适合于只读、读多写少或者单用户的应用场景。
这些存储引擎通过总是一次性请求所有需要的锁，并且总是按照相同的顺序对表进行加锁来避免死锁。这种策略的代价是降低了并发性；其他想要修改表的会话必须等待当前的数据修改语句完成。
表级锁有以下优点：
- 相对占用较少的内存（行级锁需要为每行或者每组行占用内存）
- 当对表的大部分数据进行操作时比较快，因为只涉及到一个单一的锁
- 当经常对大部分数据进行GROUP BY操作或者需要频繁扫描整个表时比较快
MySQL按照以下规则给表分配写锁：
- 如果表上没有任何锁，就给它加上写锁
- 如果表上有读锁，就等待所有读操作完成后再给它加上写锁
- 如果表上有写锁，就等待所有写操作完成后再给它加上写锁
MySQL按照以下规则给表分配读锁：
- 如果表上没有任何写操作或者写请求，就给它加上读锁
- 如果表上有写操作或者写请求，就等待所有写操作完成后再给它加上读锁
可以使用LOCK TABLES语句显式地给一个或多个表加上读或者写的表级别的排他性质的共享模式（S）或者排他模式（X）。
例如：
-- 给user表加上读模式的共享模式（S）的排他性质的共享模式（S）的排他性质的共享模式（S）的排他性质的共享模式（S）的排他性质的共享模式（S）的排他性质
LOCK TABLE user READ;
-- 给user和order两个表分别加上不同模式的排他性质
LOCK TABLE user WRITE, order READ;
-- 释放所有已经获取到的表级别排他性
UNLOCK TABLES;
表级锁有以下缺点：
- 并发性能低：表级锁会阻塞其他会话对同一张表的读写操作，导致并发性能降低
- 死锁风险高：表级锁如果不按照相同的顺序对表进行加锁，就可能导致死锁的发生
- 无法解决幻读（Phantom Read）问题：表级锁只能保证在同一个事务中，多次读取同一条记录得到的结果是一致的，但不能保证在同一个事务中，两次读取同一范围的记录集合是一致的，因为可能有其他事务在此期间插入或删除了记录，这就导致了幻读的问题
行级锁
行级锁是一种更细粒度的锁机制，它对表中的单个行或者多个行进行加锁，可以提高并发性能，减少死锁风险，解决幻读问题。MySQL中使用InnoDB存储引擎，并且将隔离级别设置为可重复读（Repeatable Read）或者读已提交（Read Committed）时，默认使用行级锁。
行级锁主要分为两种：
- 共享锁（Shared Lock）：又称为读锁（Read Lock），允许多个事务同时对同一行数据进行读取操作，但不允许其他事务对该行数据进行写入操作
- 排他锁（Exclusive Lock）：又称为写锁（Write Lock），只允许一个事务对同一行数据进行写入操作，同时不允许其他事务对该行数据进行读取或者写入操作
MySQL中使用以下语句可以显式地给一个或多个行加上共享锁或者排他锁：
-- 给user表中id为1的行加上共享锁
select * from user where id = 1 lock in share mode;
-- 给user表中id为2的行加上排他锁
select * from user where id = 2 for update;
MySQL中使用以下规则来分配共享锁和排他锁：
- 如果一个事务要获取共享锁，并且该行没有被其他事务持有排他锁，则获取成功
- 如果一个事务要获取排他锁，并且该行没有被其他事务持有任何类型的锁，则获取成功
- 否则，获取失败，并且根据不同的隔离级别采取不同的策略：
- 如果隔离级别为可重复读（Repeatable Read），则等待直到获取成功或者超时
- 如果隔离级别为读已提交（Read Committed），则返回最新已提交的数据版本
下面用一个例子来说明：
假设有以下表：
create table user (
id int primary key,
name varchar(20),
age int
) engine=innodb;
insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);
假设有两个并发事务T1和T2，它们都使用可重复读（Repeatable Read）隔离级别，并且执行以下操作：
sequenceDiagram participant T1 participant T2 T1->>+T1: start transaction; T1->>+T1: select * from user where id = 1 for update; Note right of T1: Result: <br/>id | name | age <br/>1 | Alice| 20 Note right of T1: Lock: <br/>id | name | age | lock type <br/>1 | Alice | 20 | X T2->>+T2: start transaction; T2->>+T2: select * from user where id = 1 lock in share mode; Note right of T2: Result: <br/>Blocked by T1’s exclusive lock T1->>+T1: update user set age = 21 where id = 1; Note right of T1: Result: <br/>id | name | age <br/>1 | Alice | 21 Note right of T1: Lock: <br/>id | name | age | lock type <br/>1 | Alice | 21 | X T2->>+T2: select * from user where id = 2 for update; Note right of T2: Result: <br/>id | name | age <br/>2 | Bob | 21 Note right of T2: Lock: <br/>id | name | age | lock type <br/>2 | Bob | 21 | X T1->>+T1: select * from user where id = 2 lock in share mode; Note right of T1: Result: <br/>Blocked by T2’s exclusive lock T2->>+T2: commit; Note right of T2: Lock: <br/>Released all locks T1->>+T1: select * from user where id = 2 lock in share mode; Note right of T1: Result: <br/>id | name | age <br/>2 | Bob | 21 Note right of T1: Lock: <br/>id | name | age | lock type <br/>1 | Alice | 21 | X <br/>2 | Bob | 21| S T1->>+T1: commit; Note right of T1: Lock: <br/>Released all locks从上面的例子可以看到，T1和T2在可重复读隔离级别下，使用行级锁来保证数据的一致性和可重复性。当它们对同一行数据进行读或者写操作时，就会发生锁冲突，导致其中一个事务被阻塞，直到另一个事务释放了锁。这样可以避免不可重复读和幻读的问题，但是也会降低并发性能和增加死锁风险。
多版本并发控制（MVCC）
MVCC（Multi-Version Concurrency Control）是一种用来在数据库中控制并发的方法，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC在MySQL中，主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。
MVCC通过维护多个数据版本和使用事务标识符和时间戳来实现并发控制。它能够提供高并发性和读一致性，避免了读写冲突和数据不一致的问题。每个事务都可以获得一个独立的一致性视图，从而实现了更高的并发性和隔离性。
MVCC有以下优点：
- 提高并发性能：MVCC允许多个读操作同时进行，而不需要等待写操作完成，从而提高了数据库的吞吐量。
- 减少锁竞争：MVCC避免了读写之间以及读读之间产生锁冲突，从而降低了死锁和超时发生的概率。
- 简化编程模型：MVCC保证了每个事务都能读取到一致性的数据快照片，从而简化了编程模型和事务管理。
MVCC也有以下缺点：
- 占用更多的存储空间：MVCC需要为每个记录版本保存额外的信息，如DB_TRX_ID, DB_ROLL_PTR等，这会增加数据库的存储开销。
- 增加了GC（Garbage Collection）的复杂度：MVCC需要定期清理过期的记录版本，这需要额外的线程和算法来完成，这会增加数据库的运行负担。
- 可能导致幻读（Phantom Read）：MVCC只能保证读取到的记录版本是一致的，但不能保证在同一个事务中，两次读取的记录集合是一致的，因为可能有其他事务在此期间插入或删除了记录，这就导致了幻读的问题。
MVCC的原理主要是依赖记录中的三个隐式字段、undo日志、Read View来实现的。
隐式字段
每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID等字段。
- DB_TRX_ID：6 byte，最近修改（修改/插入）事务ID：记录创建这条记录/最后一次修改该记录的事务ID
- DB_ROLL_PTR：7 byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
- DB_ROW_ID：6 byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了。
例如：
| name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID | 删除flag |
|---|---|---|---|---|---|
| Smith | 24 | 1 | NULL | 1 | false |
这是一个初始状态的记录，name为Smith, age为24岁，隐式主键是1，事务ID和回滚指针我们假设为NULL。
undo日志
undo log主要分为两种：
- insert undo log：代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
- update undo log：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
purge从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。
为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。
为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。
对MVCC有帮助的实质是update undo log ，undo log 实际上就是存在rollback segment中旧记录链。
例如：
| name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID | 删除flag |
|---|---|---|---|---|---|
| Smith | 24 | 1 | NULL | 1 | false |
假设事务2执行了以下语句：
update user set age = 25 where name = 'Smith';
那么会产生以下变化：
| name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID | 删除flag |
|---|---|---|---|---|---|
| Smith | 25 | 2 | 指向事务1的记录版本 | 1 | false |
| Smith | 24 | 1 | NULL | 1 | true |
可以看到，原来的记录被标记为删除，并且DB_ROLL_PTR指向了新的记录版本，而新的记录版本的DB_TRX_ID为2，表示是由事务2修改的。
这样，如果有其他事务要读取这条记录，就可以根据自己的Read View来判断应该读取哪个版本。
Read View
Read View是InnoDB实现MVCC的核心数据结构，用于提供在给定时间点上的一致性视图，并保存当前事务可见的其他活跃事务ID列表。
在MVCC中，每个事务都可以看到一个特定的数据库状态，该状态是在事务开始时确定的。ReadView就是为了帮助事务获取这个一致性视图而存在的。
Read View主要包含以下几个属性：
- creator_trx_id：创建该Read View的事务ID
- low_limit_id：系统中已经创建过的最大事务ID+1
- up_limit_id：系统中活跃事务中最小的事务ID
- trx_ids：系统中活跃且大于up_limit_id小于low_limit_id的事务ID列表
Read View用来判断一个记录版本是否对当前事务可见，具体规则如下：
- 如果记录版本的DB_TRX_ID等于creator_trx_id，则可见
- 如果记录版本的DB_TRX_ID大于或等于low_limit_id，则不可见
- 如果记录版本的DB_TRX_ID小于up_limit_id，则可见
- 如果记录版本的DB_TRX_ID在trx_ids列表中，则不可见
- 否则，可见
例如：
假设系统中有四个活跃事务，分别为T1, T2, T3, T4, 它们的ID分别为1, 2, 3, 4。假设T4创建了一个Read View，那么它的属性如下：
- creator_trx_id：4
- low_limit_id：5
- up_limit_id：1
- trx_ids：[2, 3]
假设有以下记录版本：
| name | age | DB_TRX_ID |
|---|---|---|
| Smith | 26 | 5 |
| Smith | 25 | 4 |
| Smith | 24 | 3 |
| Smith | 23 | 2 |
| Smith | 22 | 1 |
那么对于T4来说，它能看到的记录版本是：
| name | age |
|---|---|
| Smith | 25 |
因为只有DB_TRX_ID为4的记录版本满足了Read View的可见规则。其他版本要么太新（DB_TRX_ID >= low_limit_id），要么太旧（DB_TRX_ID < up_limit_id），要么正在被其他活跃事务修改（DB_TRX_ID in trx_ids）。
读已提交级别中的执行流程
读已提交（Read Committed）级别保证了一个事务只能读取到已经提交的数据，而不能读取到未提交或回滚的数据。这样可以避免脏读（Dirty Read）的问题，即一个事务读取到另一个事务未提交的数据，然后另一个事务回滚了，导致前一个事务读取到了无效的数据。
在MySQL中，如果使用InnoDB存储引擎，并且将隔离级别设置为读已提交（Read Committed），那么MVCC的工作方式如下：
- 每个事务在执行每个SQL语句之前都会生成一个新的Read View，这样可以保证每个语句都能读取到最新的已提交数据。
- 每个事务在执行插入、更新或删除操作时，都会为被修改的记录生成一个新的版本，并且将DB_TRX_ID设置为当前事务的ID，同时将DB_ROLL_PTR指向上一个版本。
- 每个事务在执行查询操作时，都会根据自己的Read View来判断哪些记录版本对自己可见，具体规则如下：
- 如果记录版本的DB_TRX_ID等于当前事务的ID，则可见
- 如果记录版本的DB_TRX_ID大于或等于当前事务生成Read View时的系统最大事务ID+1，则不可见
- 如果记录版本的DB_TRX_ID小于当前事务生成Read View时的系统最小活跃事务ID，则可见
- 如果记录版本的DB_TRX_ID在当前事务生成Read View时的系统活跃事务ID列表中，则不可见
- 否则，可见
下面用一个例子来说明：
假设有以下表：
create table user (
id int primary key,
name varchar(20),
age int
) engine=innodb;
insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);
假设有两个并发事务T1和T2，它们都使用读已提交（Read Committed）隔离级别，并且执行以下操作：
sequenceDiagram participant T1 participant T2 T1->>+T1: start transaction; T1->>+T1: select * from user; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [] Note right of T1: Result: <br/>id | name | age <br/>1 | Alice | 20 <br/>2 | Bob | 21 <br/>3 | Charlie | 22 T2->>+T2: start transaction; T2->>+T2: update user set age = age + 1 where id = 2; Note right of T2: Read View: <br/>creator_trx_id: 2 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [1] Note right of T2: Record Versions: <br/>id | name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID | 删除flag <br/>1 | Alice | 20 | NULL | NULL | 1 | false <br/>2 | Bob | 21 | NULL | NULL | 2 | true <br/>2 | Bob | 22 | 2 | 指向上一版本| 2 | false <br/>3 | Charlie | 22 | NULL | NULL| 3| false T1->>+T1: select * from user where id = 2; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [] Note right of T1: Result: <br/>id | name | age <br/>2 | Bob | 21 T2->>+T2: commit; Note right of T2: Read View: <br/>creator_trx_id: 2 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [1] Note right of T2: Record Versions: <br/>id | name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID | 删除flag <br/>1 | Alice | 20 | NULL | NULL | 1 | false <br/>2 | Bob | 21 | NULL | NULL | 2 | true <br/>2 | Bob | 22 | 2 | 指向上一版本| 2 | false <br/>3 | Charlie | 22 | NULL | NULL| 3| false T1->>+T1: select * from user where id = 2; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 5 <br/>up_limit_id: 3 <br/>trx_ids: [] Note right of T1: Result: <br/>id | name | age <br/>2 | Bob | 22 T1->>+T1: commit;从上面的例子可以看到，T1在读已提交隔离级别下，每次执行查询语句都会生成一个新的Read View，因此它能读取到T2提交后的数据变化。这样可以保证数据的一致性，但是也可能导致不可重复读（Non-repeatable Read）的问题，即在同一个事务中，两次读取同一条记录得到了不同的结果。
可重复读级别中的执行流程
可重复读（Repeatable Read）保证了在同一个事务中，多次读取同一条记录得到的结果是一致的，而不会受到其他事务的影响。这样可以避免不可重复读（Non-repeatable Read）的问题，即在同一个事务中，两次读取同一条记录得到了不同的结果。
需要注意的是，在MySQL中，MVCC（多版本并发控制）确实可以帮助解决可重复读（REPEATABLE READ）级别下的幻读问题，但并非彻底解决。MVCC使用了间隙锁（Gap Locks）来避免幻读。然而，MVCC并不能完全消除幻读的可能性。在可重复读级别下，如果一个事务在读取数据之后，另一个事务插入了满足原始查询条件的新行，那么第一个事务再次执行相同的查询时，就会发现新增了一个幻读行。
在MySQL中，如果使用InnoDB存储引擎，并且将隔离级别设置为可重复读（Repeatable Read），那么MVCC的工作方式如下：
- 每个事务在开始时生成一个Read View，并且在整个事务期间保持不变，这样可以保证每次查询都能读取到相同的数据快照。
- 每个事务在执行插入、更新或删除操作时，都会为被修改的记录生成一个新的版本，并且将DB_TRX_ID设置为当前事务的ID，同时将DB_ROLL_PTR指向上一个版本。
- 每个事务在执行查询操作时，都会根据自己的Read View来判断哪些记录版本对自己可见，具体规则如下：
- 如果记录版本的DB_TRX_ID等于当前事务的ID，则可见
- 如果记录版本的DB_TRX_ID大于或等于当前事务生成Read View时的系统最大事务ID+1，则不可见
- 如果记录版本的DB_TRX_ID小于当前事务生成Read View时的系统最小活跃事务ID，则可见
- 如果记录版本的DB_TRX_ID在当前事务生成Read View时的系统活跃事务ID列表中，则不可见
- 否则，可见
下面举例来说明，
示例1
我们来详细描述一个场景，展示MVCC如何解决幻读问题。
假设在数据库中有一个表格orders，其中包含订单信息，有一个唯一索引列order_id。在事务开始之前，数据库中存在以下数据：
orders 表：
---------------------------------
| order_id | customer_name |
---------------------------------
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Alice Johnson |
---------------------------------
现在，我们来描述一个事务执行的过程：
graph LR A[Transaction A] -- 1.Begins --> B(Start ReadView) B -- 2.Read Data 'order_id >= 2' --> C C -- 3.Returns Initial Data --> A D[Other Transaction] -- 4.Some Change --> F[Database] A -- 5.Commit --> F[Database]- 事务A开始时，系统为该事务分配一个唯一的事务标识符（Transaction ID）。同时，事务A创建一个空的ReadView。
- 事务A执行一个查询语句：
SELECT * FROM orders WHERE order_id >= 2;。这个查询使用事务A的ReadView来确定可见的数据版本。 - 事务A的ReadView会根据事务A开始时的时间戳和数据库中的事务版本信息来确定可见的数据。在这个例子中，事务A开始时的时间戳为T1，所以它只能看到T1之前已经存在的数据。
- 事务A完成查询后，它返回的结果是
order_id >= 2的数据行。由于事务A的ReadView只能看到T1之前的数据，所以它只能返回order_id = 2和order_id = 3的数据行。 - 最后，事务A提交并将修改持久化到数据库。
在事务A执行期间，其他事务可能会对数据库进行一些操作，例如插入新的订单或删除现有的订单。然而，由于事务A的ReadView只能看到T1之前的数据，所以它不会受到其他事务的影响。即使其他事务在事务A执行期间插入了order_id = 1的新订单，事务A也不会看到该新订单，从而避免了幻读的问题。
通过MVCC的机制，事务A在可重复读级别下可以获得一致性的数据视图，并避免了幻读的情况。
示例2
然后是可能出现幻读的场景，假设有以下表：
create table user (
id int primary key,
name varchar(20),
age int
) engine=innodb;
insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);
假设有两个并发事务T1和T2，它们都使用可重复读（Repeatable Read）隔离级别，并且执行以下操作：
sequenceDiagram participant T1 participant T2 T1->>+T1: start transaction; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [] T1->>+T1: select * from user; Note right of T1: Result: <br/>id | name | age <br/>1 | Alice | 20 <br/>2 | Bob | 21 <br/>3 | Charlie | 22 T2->>+T2: start transaction; Note right of T2: Read View: <br/>creator_trx_id:2 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [1] T2->>+T2: update user set age = age + 1 where id = 2; Note right of T2: Record Versions: <br/>id | name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID | 删除flag <br/>1 | Alice | 20 | NULL | NULL | 1 | false <br/>2 | Bob | 21 | NULL | NULL | 2 | true <br/>2 | Bob | 22 | 2 | 指向上一版本| 2 | false <br/>3 | Charlie | 22 | NULL | NULL| 3| false T1->>+T1: select * from user where id = 2; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [] Note right of T1: Result: <br/>id | name | age <br/>2 | Bob | 21 T2->>+T2: commit; Note right of T2: Read View: <br/>creator_trx_id: 2 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [1] Note right of T2: Record Versions: <br/>id | name | age | DB_TRX_ID | DB_ROLL_PTR | DB_ROW_ID | 删除flag <br/>1 | Alice | 20 | NULL | NULL| 1| false<br/>2| Bob|21|NULL|NULL|2|true<br/>2|Bob|22|2|指向上一版本|2|false<br/>3|Charlie|22|NULL|NULL|3|false T1->>+T1: select * from user where id = 2; Note right of T1: Read View: <br/>creator_trx_id: 1 <br/>low_limit_id: 4 <br/>up_limit_id: 1 <br/>trx_ids: [] Note right of T1: Result: <br/>id | name | age <br/>2 | Bob | 21 T1->>+T1: commit;从上面的例子可以看到，T1在可重复读隔离级别下，整个事务期间使用同一个Read View，因此它不能读取到T2提交后的数据变化。这样可以保证数据的可重复性，但是也可能导致幻读（Phantom Read）的问题，即在同一个事务中，两次读取同一范围的记录集合得到了不同的结果。
标签：事务,Read,深入浅出,T2,T1,MySQL,ID,id From： https://www.cnblogs.com/novwind/p/17486672.html