嗨客网搜索
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 相关列信息:

字段 描述
trx_id innodb 存储引擎内部事务唯一的事务 id。
trx_state 当前事务的状态。
trx_started 事务开始的时间。
trx_requested_lock_id 等待事务的锁 id,如 trx_state 的状态为 LOCK WAIT,那么该值代表当前事务之前占用锁资源的 id,如果 trx_state 不是 LOCK WAIT 的话,这个值为 null。
trx_wait_started 事务等待开始的时间。
trx_weight 事务的权重,反映了一个事务修改和锁住的行数。在 innodb 的存储引擎中,当发生死锁需要回滚时,innodb 存储引擎会选择该值最小的事务进行回滚。
trx_mysql_thread_id 正在运行的 mysql 中的线程 id,show full processlist 显示的记录中的 thread_id。
trx_query 事务运行的sql语句,在实际中发现,有时会显示为 null 值,当为 null 的时候,就是 t2 事务中等待锁超时直接报错(ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction)后,trx_query 就显示为 null 值。
比如事务 t2 正在运行 trx_query: update test.t1 set b=‘t2’ where a=1 的 sql 语句,t1 先执行,所以是 trx_state: RUNNING 先申请的资源一直在运行,而 t2 后run 的所以是 trx_state: LOCK WAIT 一直在等待 t1 执行完后释放资源。 但是并不能仔细判断锁的一些详细情况,我们需要再去看当前锁定的事务表数据。

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

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

字段 描述
lock_id 锁的 id 以及被锁住的空间 id 编号、页数量、行数量。
lock_trx_id 锁的事务 id。
lock_mode 锁的模式。
lock_type 锁的类型,表锁还是行锁。
lock_table 要加锁的表。
lock_index 锁的索引。
lock_space innodb 存储引擎表空间的 id 号码。
lock_page 被锁住的页的数量,如果是表锁,则为 null 值。
lock_rec 被锁住的行的数量,如果表锁,则为 null 值。
lock_data 被锁住的行的主键值,如果表锁,则为 null 值。

如以下查询 :

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 相关列信息:

字段 描述
requesting_trx_id 申请锁资源的事务 id。
requested_lock_id 申请的锁的 id。
blocking_trx_id 阻塞的事务 id。
blocking_lock_id 阻塞的锁的 id。

如以下查询:

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 锁多张表。


赞助商链接:


嗨客网顶部