Mysql死锁解决

什么是死锁

所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

产生死锁条件

产生死锁的四个必要条件：

1. 互斥条件：一个资源每次只能被一个进程使用。
2. 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
3. 不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。
4. 循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

死锁的影响

当产生某表死锁的一开始，所有涉及这张表的操作都将受到阻塞。假设这张表在业务逻辑上是读写频繁的，那就会使很多操作在那里排队等待，而排队等待会占用数据库连接，当该达到该数据库连接数的最大承载数之后，就会使所有数据库操作均无法再继续下去，致使数据库各项指标异常，导致整个环境崩溃。

在生产环境中出现这种问题，那是相当致命的，当发现数据库指标异常时因快速处理！

如何排查死锁

查询数据库进程

主要看 State 字段，如果出现大量 waiting for ..lock 即可判定死锁：

SHOW FULL PROCESSLIST;

注意：需要拥有 root 组权限（supper），否则只能看到当前用户的进程，无法查询所有。

查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

INNODB_TRX 表包含信息关于每个事务(排除只读事务)当前执行的在 InnoDB，包含是否事务是等待一个锁, 当事务启动后, SQL 语句事务是正在执行。

INNODB_TRX Columns 相关列信息：

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

INNODB_LOCKS 表包含信息关于每个锁一个 InnoDB 事务已经请求,但是没有获得锁，每个 lock 一个事务持有是堵塞另外一个事务。INNODB_LOCKS Columns 相关列信息：

如以下查询 :

mysql> select * from INNODB_LOCKSG

输出行一：

lock_id: 3015646:797:3:2

lock_trx_id: 3015646

lock_mode: X

lock_type: RECORD

lock_table: test.t1

lock_index: PRIMARY

lock_space: 797

lock_page: 3

lock_rec: 2

lock_data: 1

行二：

lock_id: 3015645:797:3:2

lock_trx_id: 3015645

lock_mode: X

lock_type: RECORD

lock_table: test.t1

lock_index: PRIMARY

lock_space: 797

lock_page: 3

lock_rec: 2

lock_data: 1

这里我们可以看到当前的锁信息了，2 个事务都锁定了，看相同的数据 lock_space: 797、lock_page: 3、lock_rec: 2 可以得出事务 t1 和事务 t2 访问了相同的 innodb 数据块，再通过 lock_data 字段信息 lock_data: 1，看到锁定的数据行都是主键为 1 的数据记录，可见 2 个事务 t1 和 t2 都申请了相同的资源，因此会被锁住，事务在等待。

通过 lock_mode: X 值也可以看出事务 t1 和 t2 申请的都是排它锁。

PS：当执行范围查询更新的时候，这个 lock_data 的值并非是完全准确。当我们运行一个范围更新时，lock_data 只返回最先找到的第一行的主键值 id；另外如果当前资源被锁住了，与此同时由于锁住的页因为 InnoDB 存储引擎缓冲池的容量，而导致替换缓冲池页面，再去查看 INNODB_LOCKS 表时，这个 lock_data 会显示未 NULL 值，意味着 InnoDB 存储引擎不会从磁盘进行再一次查找。

查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

INNODB_LOCK_WAITS 表包含了 blocked 的事务的锁等待的状态。当事务量比较少，我们可以直观的查看，当事务量非常大，锁等待也时常发生的情况下，这个时候可以通过 INNODB_LOCK_WAITS 表来更加直观的反映出当前的锁等待情况。

INNODB_LOCK_WAITS Columns 相关列信息:

如以下查询:

mysql> select * from INNODB_LOCK_WAITSG

输出：

requesting_trx_id: 3015646

requested_lock_id: 3015646:797:3:2

blocking_trx_id: 3015645

blocking_lock_id: 3015645:797:3:2

这里我们可以看到事务 t1(3015646) 申请了锁资源，而事务 t2(3015645) 则阻塞了事务 t1 的申请。

收集死锁信息

1. 利用命令 SHOW ENGINE INNODB STATUS 查看死锁原因。
2. 调试阶段开启 innodb_print_all_deadlocks，收集所有死锁日志。

如何处理死锁

查询对应死锁的数据库进程,可以直接杀掉

kill 进程ID

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

如何防止死锁

1. 使用事务，不使用 lock tables。
2. 保证没有长事务。
3. 操作完之后立即提交事务，特别是在交互式命令行中。
4. 如果在用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)，尝试降低隔离级别。
5. 修改多个表或者多个行的时候，将修改的顺序保持一致。
6. 创建索引，可以使创建的锁更少。
7. 最好不要用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE)。

如果上述都无法解决问题，那么尝试使用 lock tables t1, t2, t3 锁多张表。