MySQL事物原理

事务

        在MySQL服务中，系统的主线程负责监听连接，而多个连接线程负责处理连接。这时候多个SQL语句操作数据库，就会出现事务多并发，交叉处理。比如银行存钱后再取钱，如果存钱的中途撤销操作，但取钱操作已经完成等等。

        事务是一个工作单元，它由一系列操作组成，这些操作要么全部成功，要么全部失败，不会结束在中间某个点。在MySQL中，客户端想要开启事务就要向MySQL发送开启事务请求，再发送一系列SQL语句，也要向MySQL发送事务提交请求，中间若出现差错，我们需要回滚操作。这就是一整套事务操作的流程。

BEGIN TRANSACTION;

-- 执行一些数据库操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 如果一切正常，则提交事务
COMMIT;

-- 如果在执行过程中遇到错误，则回滚事务
ROLLBACK;

        事务遵循AICD特性：

• 原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中包含的诸多操作要么全部完成，要么全部不做，不会结束在中间某个点。如果中间出现差错，事务就要回滚，MySQL中有undolog记录事务执行，如果事务出错，就要根据undolog做逆运算回滚。

• 一致性（Consistency）：事务必须保证数据库从一个一致性状态转换到另一个一致性状态。一致性状态的定义是由数据库的完整性约束来定义的。

• 隔离性（Isolation）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。在MySQL中使用锁和MVCC操作实现。

• 持久性（Durability）：一旦事务提交，则其所做的修改会永久保存在数据库中。即使系统发生故障，事务的结果也不会丢失。redolog记录事务提交后物理页的修改内容。

        我们再来过一遍SQL语句到底怎么执行：

        一个语句默认为一个事务，多条语句需要手动提交。

隔离级别（重点）

        数据库的隔离级别是用来解决并发事务处理中可能出现的问题，如脏读、不可重复读和幻读。不同的隔离级别提供了不同程度的数据完整性和一致性保证。以下是SQL标准定义的四种隔离级别，以及它们对并发事务问题的处理：

1. 读未提交（Read Uncommitted）：

• 这是最低的隔离级别，它允许事务读取未提交的数据。
• 脏读：可以读取到其他事务未提交的数据，如果这些数据后来被回滚，那么读取到的数据就是“脏”的。
• 不可重复读：在同一事务中，多次读取同一数据集合可能会得到不同的结果，因为其他事务可能在这期间修改了数据。
• 幻读：在同一事务中，多次执行查询可能会得到不同的结果集，因为其他事务可能在这期间插入了新行。

1. 读已提交（Read Committed）：

• 事务只能读取到其他事务已经提交的数据。
• 脏读：不会发生脏读，因为事务不会读取未提交的数据。
• 不可重复读：仍然可能发生，因为其他事务可以在当前事务的两次查询之间修改数据。
• 幻读：仍然可能发生，因为其他事务可以插入新行。

1. 可重复读（Repeatable Read）：

• 事务在整个过程中可以重复读取到相同的数据行，即使这些数据已经被其他事务修改。MySQL默认隔离级别。
• 脏读：不会发生。
• 不可重复读：不会发生，因为一旦事务开始，它将看到一致的数据视图，即使其他事务提交了修改。所有读的数据都是本次事务执行前的一个版本。
• 幻读：可能发生，因为其他事务可以插入新行。

1. 串行化（Serializable）：

• 这是最高的隔离级别，事务会依次逐个执行，模拟了没有并发的情况。
• 脏读：不会发生。
• 不可重复读：不会发生。
• 幻读：不会发生。

        每种隔离级别都有其适用场景，选择哪种隔离级别取决于应用程序对数据一致性的需求和对并发性能的考虑。通常，隔离级别越高，数据库的并发性能越低，因为需要更多的锁和事务冲突。在MySQL中，你可以使用以下命令设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL [隔离级别];

        其中，[隔离级别]可以是READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ或SERIALIZABLE。

MVCC

        多版本并发控制（MVCC, Multi-Version Concurrency Control）是一种数据库管理系统中的并发控制机制，它通过维护数据的多个版本来处理并发操作，从而提高数据库的并发性能。MVCC 允许在不同的事务中，对同一数据行进行读取和更新操作而不需要等待其他事务释放锁，这样可以减少锁争用，提高数据库的吞吐量。MVCC又称一致性非锁定读。

        在MySQL的InnoDB存储引擎中，MVCC主要依赖于以下组件实现：

1. 隐藏字段：InnoDB为每行数据维护了三个隐藏字段，包括事务ID（DB_TRX_ID、同一时刻只会被一个事务修改）、回滚指针（DB_ROLL_PTR）和行ID（DB_ROW_ID）。由聚集索引记录隐藏列。
2. Undo日志：每次对数据行进行修改时，都会在undo日志中保存修改前的版本，形成一个版本链，这样可以实现数据的回滚和多版本控制。
3. Read View：事务在进行快照读操作时，会生成一个Read View，用于确定所读取数据行的版本是否可见。创建Read View时会启动未提交事务的ID列表m_ids，记录最小的事务ID min_trx_id，然后预分配给下一个未开启的事务的ID max_trx_id，最后创建该Read View的ID creator_trx_id。

        事务可见性：在事务执行过程中，如务（A）本身有修改，那么这个事务（A）可以看到事务（A）本身的修改（trx_id==creator_id）。而对于其他事务（B），就需要判断事务（B）id与（A）Read View中的min_trx_id和max_trx_id的大小来决定事务（A）对事务（B）的可见性：

• 如果事务B的ID（trx_id）小于min_trx_id，说明事务B在事务A的Read View创建之前已经提交，因此事务A可以看到事务B的修改。
• 如果事务B的ID大于max_trx_id，说明事务B在事务A的Read View创建之后启动，因此事务A看不到事务B的修改。
• 如果事务B的ID在min_trx_id和max_trx_id之间，说明事务B在事务A的Read View创建时可能正在活跃。这时需要检查事务B的ID是否在m_ids列表中：
  • 如果在m_ids列表中，说明事务B尚未提交，事务A看不到事务B的修改。
  • 如果不在m_ids列表中，说明事务B在事务A的Read View创建之前已经提交，事务A可以看到事务B的修改。

        MVCC解决了脏读、不可重复读和幻读问题，但并不是所有隔离级别都能完全避免这些问题。例如，在可重复读（REPEATABLE READ）隔离级别下，MVCC通过在事务开始时创建一个Read View，确保事务中所有的读取操作都看到一致的数据视图，从而解决了不可重复读的问题。但对于幻读，MVCC只能部分解决，因为幻读涉及到范围查询和新插入的行，而MVCC主要是针对单个数据行的版本控制。

锁与死锁

        在InnoDB中，有全局锁、表级锁、行级锁。

        全局锁是锁整个数据库，整个库可读，通常用于全库备份。

        表级锁：

• 元数据锁（MDL），在对数据修改时，其他连接无法修改表结构。
• 意向锁（共享锁）用于判断表中是否有记录被加锁。
• auto_inc锁实现了自增约束，在语句结束后释放锁。

        行级锁：

• 记录锁（record lock），分为共享锁S与排他锁X。
• 间隙锁（gap lock），可重复读。防止其他事务在记录间插入新记录，避免幻读现象。
• 临建锁（next-key lock），记录锁+间隙锁，可重复读。

        在MySQL的InnoDB存储引擎中，行级锁通常是自动加的，这意味着你不需要手动添加锁来控制并发访问。减少了开发者在编写事务时需要考虑的锁管理复杂性。

        对于死锁，就是在并发事务执行过程中造成的资源争夺互相等待的现象。对于顺序型死锁，我们需要调整执行顺序。对于冲突型死锁，我们得更换语句或者降低隔离级别。可参考以下方法避免死锁：

• 访问索引记录和表尽量顺序相同。
• 在幻读与不可重复读对应用影响不大的情况下考虑降低隔离级别。
• 索引添加要合理。
• 在一个事务中包含所有需要的资源。
• 避免过大的事务。
• 避免同一时间点运行多个对同一个表读写。