mysql大事务导致数据库OOM问题排查

1. 事务：

1.1 什么是数据库事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

1.2 事物的四大特性(ACID)

关系性数据库需要遵循ACID规则，具体内容如下：

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

1.3 脏读，幻读，不可重复读

• 脏读：一个事务读取到另一个事务未提交的数据
  例：比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读。
• 不可重复读：一个事务的操作导致另一个事务前后两次读取到不同的数据（一个事务读取另一个事务已提交的数据）
  例：以银行取钱为例，事务A开启事务–>查出银行卡余额为1000元，此时切换到事务B事务B开启事务–>事务B取走100元–>提交，数据库里面余额变为900元，此时切换回事务A，事务A再查一次查出账户余额为900元，这样对事务A而言，在同一个事务内两次读取账户余额数据不一致，这就是不可重复读。
• 虚读（幻读）一个事务的操作导致另一个事务前后两次查询的结果的数据量不同。（一个事务读取到另一个事务已提交后添加的数据）
  例：事物A查询数据库中有没有id为1的数据，查到没有，此时事物B进来，直接往数据库中插入了一条id为1的数据，此时事物B再插就会出错
• 更新丢失：2个并发事务同时对一个结果修改，后提交的事务忽略了前一个事务对数据库的影响，造成了先提交的事务对数据库的影响丢失
  例：比如学生信息，事务A开启事务–>修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务–>事务B插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。

1.3.1 不可重复度和幻读的区别：

• 不可重复读 针对的是一份数据的修改
• 幻读 针对的是行数修改

1.4 事务的隔离级别

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

隔离级别	脏读	不可重复读	幻读	加锁读
READ-UNCOMMITTED	√	√	√	×
READ-COMMITTED	×	√	√	×
REPEATABLE-READ	×	×	√	×
SERIALIZABLE	×	×	×	√

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别， Oracle 默认采用的 READ_COMMITTED隔离级别

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVCC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

2. 锁

2.1 数据库并发场景

在高并发场景下，不考虑其他中间件的情况下，数据库会存在以下场景：

• 读读：不存在任何问题，也不需要并发控制。
• 读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读。
• 写写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失。

针对以上问题，SQL 标准规定不同隔离级别下可能发生的问题不一样：

MySQL 四大隔离级别及存在的问题：

隔离级别	脏读	不可重复读	幻读	加锁读
READ-UNCOMMITTED	√	√	√	×
READ-COMMITTED	×	√	√	×
REPEATABLE-READ	×	×	√	×
SERIALIZABLE	×	×	×	√

可以看到，MySQL 在 REPEATABLE READ 隔离级别实际上就解决了不可重复度问题，基本解决了幻读问题，但在极端情况下仍然存在幻读现象。

那么有什么方式来解决呢？一般来说有两种方案：

2.1.1 读操作 MVCC ，写操作加锁

• 对于读，在 RR 级别的 MVCC 下，当一个事务开启的时候会产生一个 ReadView，然后通过 ReadView 找到符合条件的历史版本，而这个版本则是由 undo 日志构建的，而在生成 ReadView 的时候，其实就是生成了一个快照，所以此时的 SELECT 查询也就是快照读（或者一致性读），我们知道在 RR 下，一个事务在执行过程中只有第一次执行 SELECT 操作才会生成一个 ReadView，之后的 SELECT 操作都复用这个 ReadView，这样就避免了不可重复读和很大程度上避免了幻读的问题。
• 对于写，由于在快照读或一致性读的过程中并不会对表中的任何记录做加锁操作并且 ReadView 的事务是历史版本，而对于写操作的最新版本两者并不会冲突，所以其他事务可以自由的对表中的记录做改动。

2.1.2 读写操作都加锁

如果我们的一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本，比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候也就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。

对于脏读，是因为当前事务读取了另一个未提交事务写的一条记录，但如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

对于不可重复读，是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

对于幻读，是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录，我们把新插入的那些记录称之为幻影记录。

怎么理解这个范围？如下：

1. 假如表 user 中只有一条id=1的数据。
2. 当事务 A 执行一个id = 1的查询操作，能查询出来数据，如果是一个范围查询，如 id in(1,2)，必然只会查询出来一条数据。
3. 此时事务 B 执行一个id = 2的新增操作，并且提交。
4. 此时事务 A 再次执行id in(1,2)的查询，就会读取出 2 条记录，因此产生了幻读。

注：由于 RR 可重复读的原因，其实是查不出 id = 2的记录的，所以如果执行一次 update … where id = 2，再去范围查询就能查出来了。

采用加锁的方式解决幻读问题就有不太容易了，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以读取的时候加锁就有点麻烦，因为并不知道给谁加锁。

那么 InnoDB 是如何解决的呢？我们先来看看 InnoDB 存储引擎有哪些锁。

2.2 锁的分类

在 MySQL 官方文档 中，InnoDB 存储引擎介绍了以下几种锁：

同样，看起来仍然一头雾水，但我们可以按照学习 JDK 中锁的方式来进行分类：

2.3 锁分类（按粒度分）

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

2.3.1 行级锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

实现方式：

• 共享锁：lock in share mode
  select math from zje where math>60 lock in share mode
• 排他锁：for update
  select math from zje where math >60 for update

注意：

1. 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了
2. 两个事务不能锁同一个索引

2.3.2 表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

2.3.3 页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

2.4 锁分类（按类型分）

从锁的类别上来讲，有共享锁和排他锁，这两种锁都属于行锁。

2.4.1 共享锁（Shared Locks，S锁）:

又叫做读锁。 共享锁就是多个事物对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改

共享锁可以同时加上多个。

2.4.2 排他锁（Exclusive Locks， X锁）:

又叫做写锁。 排他锁不能和其他锁共存，如果一个事物获取了一个数据行的排它锁，其他事物就不能再获取该行的其他锁，包括共享锁和排它锁，但是获取排他锁的事务是可以对数据就行进行读取和修改。

对于UPDATE、DELETE和INSERT语句，innodb会自动给涉及数据集加排它锁（X）。
对于普通select语句，innodb不会加任何锁。

来分析一下获取锁的情形：假如存在事务 A 和事务 B

1. 事务 A 获取了一条记录的 S 锁，此时事务 B 也想获取该条记录的 S 锁，那么事务 B 也能获取到该锁，也就是说事务 A 和事务 B 同时持有该条记录的 S 锁。
2. 如果事务 B 想要获取该记录的 X 锁，则此操作会被阻塞，直到事务 A 提交之后将 S 锁释放。
3. 如果事务 A 首先获取的是 X 锁，则不管事务 B 想获取该记录的 S 锁还是 X 锁都会被阻塞，直到事务 A 提交。

因此，我们可以说 S 锁和 S 锁是兼容的， S 锁和 X 锁是不兼容的， X 锁和 X 锁也是不兼容的。

2.4.3 意向锁

意向共享锁（Intention Shared Lock），简称 IS 锁。当事务准备在某条记录上加 S 锁时，需要先在表级别加一个 IS 锁。

意向独占锁（Intention Exclusive Lock），简称 IX 锁。当事务准备在某条记录上加 X 锁时，需要先在表级别加一个 IX 锁。

意向锁是表级锁，它们的提出仅仅为了在之后加表级别的 S 锁和 X 锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说其实 IS 锁和 IS 锁是兼容的，IX 锁和 IX 锁是兼容的。

为什么需要意向锁？

InnoDB 的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务 B 加了 X 锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。

举个例子，如果表中记录 1 亿，事务 A 把其中有几条记录上了行锁了，这时事务 B 需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务 B 先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

说白了意向锁的主要作用是处理行锁和表锁之间的矛盾，能够显示某个事务正在某一行上持有了锁，或者准备去持有锁。

表级别的各种锁的兼容性：

2.5 锁分类（算法实现）

对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

不过即使是行锁，InnoDB 里也是分成了各种类型的。换句话说即使对同一条记录加行锁，如果类型不同，起到的功效也是不同的。通常有以下几种常用的行锁类型。

2.5.1 Record Lock

记录锁，单条索引记录上加锁。

Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

2.5.2 Gap Locks

间隙锁，对索引前后的间隙上锁，不对索引本身上锁。

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。

如存在这样一张表：

CREATE TABLE test (
	id INT (1) NOT NULL AUTO_INCREMENT,
	number INT (1) NOT NULL COMMENT '数字',
	PRIMARY KEY (id),
	KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 插入以下数据
INSERT INTO test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

如下：

开启一个事务 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。

如果此时在开启一个事务 B 进行插入数据，如下：

为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如：

INSERT INTO test (id, number) VALUES (8,8);

2.5.3 Next-Key Locks

next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。

默认情况下，InnoDB 以 REPEATABLE READ 隔离级别运行。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

2.5.4 总结：

• innodb对于行的查询使用next-key lock
• Next-locking keying为了解决Phantom Problem幻读问题
• 当查询的索引含有唯一属性时，将next-key lock降级为record key
• Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
• 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

2.6 乐观锁和悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。

2.6.1 悲观锁：

假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。

实现方式：使用数据库中的锁机制如共享锁或者排它锁

2.6.2 乐观锁：

假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
实现方式：乐观锁一般会使用版本号机制或CAS算法实现

2.6.3 两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

2.7 Mysql的加锁操作

2.7.1 读操作的锁

对于 MySQL 的读操作，有两种方式加锁。

1. SELECT * FROM table LOCK IN SHARE MODE 如果当前事务执行了该语句，那么它会为读取到的记录加 S 锁，这样允许别的事务继续获取这些记录的 S 锁（比方说别的事务也使用 SELECT … LOCK IN SHARE MODE 语句来读取这些记录），但是不能获取这些记录的 X 锁（比方说使用 SELECT … FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。
   如果别的事务想要获取这些记录的 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S 锁释放掉
2. SELECT FROM table FOR UPDATE 如果当前事务执行了该语句，那么它会为读取到的记录加 X 锁，这样既不允许别的事务获取这些记录的 S 锁（比方说别的事务使用 SELECT … LOCK IN SHARE MODE 语句来读取这些记录），也不允许获取这些记录的 X 锁（比如说使用 SELECT … FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。
   如果别的事务想要获取这些记录的 S 锁或者 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 X 锁释放掉。

2.7.2 写操作的锁

对于 MySQL 的写操作，常用的就是 DELETE、UPDATE、INSERT。隐式上锁，自动加锁，解锁。

1. DELETE
   对一条记录做 DELETE 操作的过程其实是先在 B+树中定位到这条记录的位置，然后获取一下这条记录的 X 锁，然后再执行 delete mark 操作。我们也可以把这个定位待删除记录在 B+树中位置的过程看成是一个获取 X 锁的锁定读。
2. INSERT
   一般情况下，新插入一条记录的操作并不加锁，InnoDB 通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。
3. UPDATE
   在对一条记录做 UPDATE 操作时分为三种情况：

1. 如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在 B+树中定位到这条记录的位置，然后再获取一下记录的 X 锁，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在 B+树中位置的过程看成是一个获取 X 锁的锁定读。
2. 如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在 B+树中定位到这条记录的位置，然后获取一下记录的 X 锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在 B+树中位置的过程看成是一个获取 X 锁的锁定读，新插入的记录由 INSERT 操作提供的隐式锁进行保护。
3. 如果修改了该记录的键值，则相当于在原记录上做 DELETE 操作之后再来一次 INSERT 操作，加锁操作就需要按照 DELETE 和 INSERT 的规则进行了。

2.7.3 为什么上了写锁，别的事务还可以读操作？

因为InnoDB有 MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。

2.8 隔离级别与锁的关系

• 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
• 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
• 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
• SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

3. 数据库死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

例如说两个事务，事务A锁住了1 ~ 5行，同时事务B锁住了6 ~ 10行，此时事务A请求锁住6 ~ 10行，就会阻塞直到事务B施放6 ~ 10行的锁，而随后事务B又请求锁住1 ~ 5行，事务B也阻塞直到事务A释放1~5行的锁。死锁发生时，会产生Deadlock错误。 锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。

3.1 产生的条件

• 互斥条件：一个资源每次只能被一个进程使用；
• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
• 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺；
• 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。

3.2 死锁排查：

• 正在运行的任务

• show full processlist; 找到卡住的进程

• 解开死锁

• UNLOCK TABLES ；

• 查看当前运行的事务

• SELECT * FROM information_schema.INNODB_TRX;

• 当前出现的锁

• SELECT * FROM information_schema.INNODB_LOCKS;

• 观察错误日志
• 查看InnoDB锁状态

• show status like "innodb_row_lock%";

lnnodb_row_lock_current_waits:当前正在等待锁定的数量;
lnnodb_row_lock_time :从系统启动到现在锁定的总时间长度，单位ms;
Innodb_row_lock_time_avg :每次等待所花平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;
lnnodb_row_lock_waits :从系统启动到现在总共等待的次数。

• kill id 杀死进程

3.3 常见的解决死锁的方法

• 死锁无法避免，上线前要进行严格的压力测试
• 快速失败

• innodb_lock_wait_timeout 行锁超时时间

• 拆分sql，严禁大事务
• 充分利用索引，优化索引，尽量把有风险的事务sql使用上覆盖索，优化where条件前缀匹配，提升查询速度，引减少表锁
• 无法避免时：

• 操作多张表时，尽量以相同的顺序来访问避免形成等待环路
• 单张表时先排序再操作
• 使用排它锁 比如 for update

如果业务处理不好可以用分布式事务锁或者使用乐观锁

4. XA协议

 

• AP（Application Program）：应用程序，定义事务边界（定义事务开始和结束）并访问事务边界内的资源。
• RM（Resource Manger）资源管理器: 管理共享资源并提供外部访问接口。供外部程序来访问数据库等共享资源。此外，RM还具有事务的回滚能力。
• TM（Transaction Manager）事务管理器：TM是分布式事务的协调者，TM与每个RM进行通信，负责管理全局事务，分配事务唯一标识，监控事务的执行进度，并负责事务的提交、回滚、失败恢复等。

4.1 流程：

• 应用程序AP向事务管理器TM发起事务请求
• TM调用xa_open()建立同资源管理器的会话
• TM调用xa_start()标记一个事务分支的开头
• AP访问资源管理器RM并定义操作，比如插入记录操作
• TM调用xa_end()标记事务分支的结束
• TM调用xa_prepare()通知RM做好事务分支的提交准备工作。其实就是二阶段提交的提交请求阶段。
• TM调用xa_commit()通知RM提交事务分支，也就是二阶段提交的提交执行阶段。
• TM调用xa_close管理与RM的会话。

• 这些接口一定要按顺序执行，比如xa_start接口一定要在xa_end之前。此外，这里千万要注意的是事务管理器只是标记事务分支并不执行事务，事务操作最终是由应用程序通知资源管理器完成的。另外，我们来总结下XA的接口

• xa_start:负责开启或者恢复一个事务分支，并且管理XID到调用线程
• xa_end:负责取消当前线程与事务分支的关系
• xa_prepare:负责询问RM 是否准备好了提交事务分支 xa_commit:通知RM提交事务分支
• xa_rollback:通知RM回滚事务分支

4.2 mysql xa事务？

mysql的xa事务分为两部分：

1. InnoDB内部本地普通事务操作协调数据写入与log写入两阶段提交
2. 外部分布式事务

5.7 SHOW VARIABLES LIKE '%innodb_support_xa%';
8.0 默认开启无法关闭

XA 事务语法示例如下：

XA START '自定义事务id';

SQL语句...

XA END '自定义事务id';
XA PREPARE '自定义事务id';
XA COMMIT\ROLLBACK '自定义事务id';

XA PREPARE 执行成功后，事务信息将被持久化。即使会话终止甚至应用服务宕机，只要我们将【自定义事务id】记录下来，后续仍然可以使用它对事务进行 rollback 或者 commit。

4.3 xa事务与普通事务区别：

xa事务可以跨库或跨服务器，属于分布式事务，同时xa事务还支撑了InnoDB内部日志两阶段记录

普通事务只能在单库中执行

4.4 什么是2pc 3pc？

两阶段提交协议与3阶段提交协议，额外增加了参与的角色保证分布式事务完成更完善

5. select for update流程：

查询库存 = 100  0 扣减库存  = -1 99
记录日志 = log
提交  commit

select本身是一个查询语句，查询语句是不会产生冲突的一种行为，一般情况下是没有锁的，用select for update 会让select语句产生一个排它锁(X), 这个锁和update的效果一样，会使两个事务无法同时更新一条记录。
https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html https://dev.mysql.com/doc/refman/8.0/en/select.html

• for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。
• 在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。
• InnoDB默认是行级别的锁，在筛选条件中当有明确指定主键或唯一索引列的时候，是行级锁。否则是表级别。

示例

SELECT … FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
select * from t for update 会等待行锁释放之后，返回查询结果。
select * from t for update nowait 不等待行锁释放，提示锁冲突，不返回结果
select * from t for update wait 5 等待5秒，若行锁仍未释放，则提示锁冲突，不返回结果
select * from t for update skip locked 查询返回查询结果，但忽略有行锁的记录