事务与锁

1. 事务语句

两种方式启动事务：

显式启动事务：

START TRANSACTION 或 BEGIN -- 开始事务
DML语句，如 UPDATE、DELETE、INSERT
COMMIT -- 提交
ROLLBACK -- 回滚

或自动提交：

SET AUTOCOMMIT=0 --禁止自动提交，需手动 commit 或 rollback，导致大量数据改变的 DDL 操作在执行前可能强制执行 commit 提交当前的活动事务
SET AUTOCOMMIT=1 --开启自动提交

在 MySQL 中，并不是所有的操作都可以回滚。比如创建数据库、创建数据表、删除数据库、删除数据表等 DML 操作，这些操作是不可以回滚的，需提前做好备份。

2. 事务特性

事务 4 个主要特征：

• 原子性：事务中的操作要么全部执行，要买全部不执行
• 一致性：表示数据的完整性不会被事务破坏
• 隔离性：两个事务执行相互独立，互不影响。不同的隔离级别，独立程度不同
• 持久性：表示事务对数据的修改是永久有效的

执行DML语句后，可以使用 SELECT ROW_COUNT();进行验证是否执行成功，如果没有执行成功则回滚。

例子：张三在门店消费结算的时候，开启了一个事务 A，包括这样 3 个操作：读取卡内金额为 100；更新卡内金额为 0；插入一条销售流水。张三的爱人在网上购物，开启了一个事务 B，也来读取卡内金额。如果 B 读取卡内金额的操作，发生在 A 更新卡内金额之后，并且在插入销售流水之前，那么 B 读出的金额应该是多少呢？如果 B 读出 0 元，那么，A 有可能由于后面的操作失败而回滚。因此，B 可能会读到一条错误信息，而导致本来可以成功的交易失败。有什么办法可以解决这个问题呢？这个时候，就会用到 MySQL 的另外一种机制：“锁”。MySQL 可以把 A 中被修改过而且还没有提交的数据锁住，让 B 处于等待状态，一直到 A 提交完成，或者失败回滚，再释放锁，允许 B 读取这个数据。这样就可以防止因为 A 回滚而导致 B 读取错误的可能了。

3. 事务隔离

3.1 隔离级别

• 脏读：事务可以读取未提交的数据，称为脏读（Dirty Read）。即隔离级别为未提交读时存在脏读
• 幻读：当某个在读取某个范围内的记录时，另外一个事务又在该范围内插入新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB 和 XtraDB 存储引擎通过多版本并发控制（MVCC，multiversion Concurrency Control）解决了幻读的问题

MySQL 支持 4 种事务隔离等级。等级越高，消耗的系统资源也越多，需要根据实际情况进行设定。

• READ UNCOMMITTED（未提交读 或 脏读）：事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，也被称为脏读
• READ COMMITTED（提交读或不可重复读）：一个事务开始时，只能”看见“已经提交的事务所做的修改。换句话说：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
• REPEATABLE READ（可重复读）：解决了脏读的问题。一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的（不受其他事务中数据操作的影响）。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。这也是 MySQL 的默认选项。
• SERIALIZABLE（可串行化）：强制事务穿行执行，避免幻读问题。对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。所以会导致大量的锁超时和锁争用问题。很少应用该级别。

例子：

CREATE TABLE T(c int) engine=InnoDB;
INSERT INTO T(c) VALUES(1);

事务 A	事务 B
启动事务，查询得到值 1	启动事务
	查询得到值 1
	将 1 修改为 2
查询得到值 v1
	提交事务
查询得到值 v2
提交事务
查询得到值 v3

不同隔离级别，v1、v2、v3 的返回值：

• 未提交读：v1 为 2；虽然事务 B 未提交，但事务 A 可见。所以 v2、v3 也为 2；
• 读提交：v1 为 1，v2 和 v3 为 2；事务 B 提交后，事务 A 才可见
• 可重复的：v1 和 v2 为 1；v3 为 2；之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 串行化：在事务 B 执行 “将 1 改成 2” 的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在可重复读隔离级别下。这个视图是在事务启动时创建，整个事务存在期间都用这个视图。在 “读提交“ 隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，”读未提交“ 隔离级别下直接返回记录上的最新值，没有视图概念；而 “串行化” 隔离级别下直接用加锁的方式来避免并行访问。

”可重复读“ 隔离级别在财务表多表数据进行金额校对时很方便，因为事务启动时的视图可以认为是静态的，不受其他事物更新的影响。

我们可以看到在不同的，数据库行为是有所不同的。 Oracal 数据库的默认隔离级别是 “读提交”，对与从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，需将 MySQL 的隔离级别设置为 ”读提交“。即将 transaction-isolation 的值设置为 READ-COMMITTED。

3.2 隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录：

当前值时 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行所有的回滚操作得到。

即使当前有另外一个事务正在将 4 改成 5，这个事务跟 read-vew A、B、C 对应的事务是不会冲突的。

回滚日志什么时候删除：系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。即系统中没有比这个回滚日志更早的 read-view 的时候。

3.3 长事务

1. 查找系统中的长事务：

   SELECT * FROM information_schema.innodb_trx where TIME_TO_SEC(timediff(now(), trx_started)) > 60;
   

2. 为什么建议尽量不要使用长事务：

   （1）长事务意味着系统中会存在很多老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

   （2）长事务还占用锁资源，也可能拖垮整个库。

3. 如何避免长事务对业务的影响：

   从应用开发端来看：

   1. 确认是否使用了 set autocommit = 0，若是需改为 1。
   2. 确认是否有不必要的只读事务。如果业务没有需要，无需将 select 语句放到事务中。
   3. 通过 SET_MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间

   从数据库端来看：

   1. 监控 information_schema_innodb_trx 表，设置长事务阈值超过就报警或 kill
   2. 推荐使用 Percona 的 pt-kill
   3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题
   4. MySQL 5.6 以及之后版本，将 innodb_undo_tablespaces 设置成 2 （或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

4. 锁

读写锁

锁粒度：

1. 全局锁
2. 表锁
3. 行锁

死锁

5. 参考

• 《高性能 MySQL》
• 极客时间《MySQL 45讲》https://time.geekbang.org/column/intro/100020801?tab=intro