MySQL锁等待与死锁问题分析

2022-02-19 01:01:40

前言：

在 MySQL 运维过程中，锁等待和死锁问题是令各位 DBA 及开发同学非常头痛的事。出现此类问题会造成业务回滚、卡顿等故障，特别是业务繁忙的系统，出现死锁问题后影响会更严重。本篇文章我们一起来学习下什么是锁等待及死锁，出现此类问题又应该如何分析处理呢？

1.了解锁等待与死锁

出现锁等待或死锁的原因是访问数据库需要加锁，那你可能要问了，为啥要加锁呢？原因是为了确保并发更新场景下的数据正确性，保证数据库事务的隔离性。

试想一个场景，如果你要去图书馆借一本《高性能MySQL》，为了防止有人提前把这本书借走，你可以提前进行预约（加锁），这把锁可以怎么加？

  • *图书馆（数据库级别的锁）
  • 把数据库相关的书都锁住（表级别的锁）
  • 只锁 MySQL 相关的书（页级别的锁）
  • 只锁《高性能MySQL》这本书（行级别的锁）

锁的粒度越细，并发级别越高，实现也更复杂。

锁等待也可称为事务等待，后执行的事务等待前面处理的事务释放锁，但是等待时间超过了 MySQL 的锁等待时间，就会引发这个异常。等待超时后的报错为“Lock wait timeout exceeded…”。

死锁发生的原因是两个事务互相等待对方释放相同资源的锁，从而造成的死循环。产生死锁后会立即报错“Deadlock found when trying to get lock…”。

2.现象复现及处理

下面我们以 MySQL 5.7.23 版本为例（隔离级别是 RR ），来复现下上述两种异常现象。

  1. mysql> show create table test_tb\G
  2. *************************** 1. row ***************************
  3. Table: test_tb
  4. Create Table: CREATE TABLE `test_tb` (
  5. `id` int(11) NOT NULL AUTO_INCREMENT,
  6. `col1` varchar(50) NOT NULL DEFAULT ‘‘,
  7. `col2` int(11) NOT NULL DEFAULT ‘1‘,
  8. `col3` varchar(20) NOT NULL DEFAULT ‘‘,
  9. PRIMARY KEY (`id`),
  10. KEY `idx_col1` (`col1`)
  11. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
  12. 1 row in set (0.00 sec)
  14. mysql> select * from test_tb;
  15. +----+------+------+------+
  16. | id | col1 | col2 | col3 |
  17. +----+------+------+------+
  18. | 1 | fdg | 1 | abc |
  19. | 2 | a | 2 | fg |
  20. | 3 | ghrv | 2 | rhdv |
  21. +----+------+------+------+
  22. 3 rows in set (0.00 sec)
  24. # 事务一首先执行
  25. mysql> begin;
  26. Query OK, 0 rows affected (0.00 sec)
  28. mysql> select * from test_tb where col1 = ‘a‘ for update;
  29. +----+------+------+------+
  30. | id | col1 | col2 | col3 |
  31. +----+------+------+------+
  32. | 2 | a | 2 | fg |
  33. +----+------+------+------+
  34. 1 row in set (0.00 sec)
  36. # 事务二然后执行
  37. mysql> begin;
  38. Query OK, 0 rows affected (0.01 sec)
  40. mysql> update test_tb set col2 = 1 where col1 = ‘a‘;
  41. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

出现上种异常的原因是事务二在等待事务一的行锁，但事务一一直没提交，等待超时而报错。InnoDB 行锁等待超时时间由 innodb_lock_wait_timeout 参数控制，此参数默认值为 50 ，单位为秒，即默认情况下，事务二会等待 50s ，若仍拿不到行锁则会报等待超时异常并回滚此条语句。

对于 5.7 版本，出现锁等待时，我们可以查看 information_schema 中的几张系统表来查询事务状态。

  • innodb_trx 当前运行的所有事务。
  • innodb_locks 当前出现的锁。
  • innodb_lock_waits 锁等待的对应关系
  1. # 锁等待发生时 查看innodb_trx表可以看到所有事务
  2. # trx_state值为LOCK WAIT 则代表该事务处于等待状态
  4. mysql> select * from information_schema.innodb_trx\G
  5. *************************** 1. row ***************************
  6. trx_id: 38511
  7. trx_state: LOCK WAIT
  8. trx_started: 2021-03-24 17:20:43
  9. trx_requested_lock_id: 38511:156:4:2
  10. trx_wait_started: 2021-03-24 17:20:43
  11. trx_weight: 2
  12. trx_mysql_thread_id: 1668447
  13. trx_query: update test_tb set col2 = 1 where col1 = ‘a‘
  14. trx_operation_state: starting index read
  15. trx_tables_in_use: 1
  16. trx_tables_locked: 1
  17. trx_lock_structs: 2
  18. trx_lock_memory_bytes: 1136
  19. trx_rows_locked: 1
  20. trx_rows_modified: 0
  21. trx_concurrency_tickets: 0
  22. trx_isolation_level: REPEATABLE READ
  23. trx_unique_checks: 1
  24. trx_foreign_key_checks: 1
  25. trx_last_foreign_key_error: NULL
  26. trx_adaptive_hash_latched: 0
  27. trx_adaptive_hash_timeout: 0
  28. trx_is_read_only: 0
  29. trx_autocommit_non_locking: 0
  30. *************************** 2. row ***************************
  31. trx_id: 38510
  32. trx_state: RUNNING
  33. trx_started: 2021-03-24 17:18:54
  34. trx_requested_lock_id: NULL
  35. trx_wait_started: NULL
  36. trx_weight: 4
  37. trx_mysql_thread_id: 1667530
  38. trx_query: NULL
  39. trx_operation_state: NULL
  40. trx_tables_in_use: 0
  41. trx_tables_locked: 1
  42. trx_lock_structs: 4
  43. trx_lock_memory_bytes: 1136
  44. trx_rows_locked: 3
  45. trx_rows_modified: 0
  46. trx_concurrency_tickets: 0
  47. trx_isolation_level: REPEATABLE READ
  48. trx_unique_checks: 1
  49. trx_foreign_key_checks: 1
  50. trx_last_foreign_key_error: NULL
  51. trx_adaptive_hash_latched: 0
  52. trx_adaptive_hash_timeout: 0
  53. trx_is_read_only: 0
  54. trx_autocommit_non_locking: 0
  55. 2 rows in set (0.00 sec)
  57. # innodb_trx 字段值含义
  58. trx_id：事务ID。
  59. trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
  60. trx_started：事务开始时间。
  61. trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
  62. trx_wait_started：事务开始等待的时间。
  63. trx_weight：事务的权重。
  64. trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
  65. trx_query：事务正在执行的 SQL 语句。
  66. trx_operation_state：事务当前操作状态。
  67. trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
  68. trx_tables_locked：当前执行 SQL 的行锁数量。
  69. trx_lock_structs：事务保留的锁数量。
  70. trx_isolation_level：当前事务的隔离级别。
  72. # sys.innodb_lock_waits 视图也可看到事务等待状况，且给出了杀链接的SQL
  73. mysql> select * from sys.innodb_lock_waits\G
  74. *************************** 1. row ***************************
  75. wait_started: 2021-03-24 17:20:43
  76. wait_age: 00:00:22
  77. wait_age_secs: 22
  78. locked_table: `testdb`.`test_tb`
  79. locked_index: idx_col1
  80. locked_type: RECORD
  81. waiting_trx_id: 38511
  82. waiting_trx_started: 2021-03-24 17:20:43
  83. waiting_trx_age: 00:00:22
  84. waiting_trx_rows_locked: 1
  85. waiting_trx_rows_modified: 0
  86. waiting_pid: 1668447
  87. waiting_query: update test_tb set col2 = 1 where col1 = ‘a‘
  88. waiting_lock_id: 38511:156:4:2
  89. waiting_lock_mode: X
  90. blocking_trx_id: 38510
  91. blocking_pid: 1667530
  92. blocking_query: NULL
  93. blocking_lock_id: 38510:156:4:2
  94. blocking_lock_mode: X
  95. blocking_trx_started: 2021-03-24 17:18:54
  96. blocking_trx_age: 00:02:11
  97. blocking_trx_rows_locked: 3
  98. blocking_trx_rows_modified: 0
  99. sql_kill_blocking_query: KILL QUERY 1667530
  100. sql_kill_blocking_connection: KILL 1667530

sys.innodb_lock_waits 视图整合了事务等待状况，同时给出杀掉堵塞源端的 kill 语句。不过是否要杀掉链接还是需要综合考虑的。

死锁与锁等待稍有不同，我们同样也来简单复现下死锁现象。

  1. # 开启两个事务
  2. # 事务一执行
  3. mysql> update test_tb set col2 = 1 where col1 = ‘a‘;
  4. Query OK, 1 row affected (0.00 sec)
  5. Rows matched: 1 Changed: 1 Warnings: 0
  7. # 事务二执行
  8. mysql> update test_tb set col2 = 1 where id = 3;
  9. Query OK, 1 row affected (0.00 sec)
  10. Rows matched: 1 Changed: 1 Warnings: 0
  12. # 回到事务一执行 回车后 此条语句处于锁等待状态
  13. mysql> update test_tb set col1 = ‘abcd‘ where id = 3;
  14. Query OK, 1 row affected (5.71 sec)
  15. Rows matched: 1 Changed: 1 Warnings: 0
  16. # 回到事务二再执行 此时二者相互等待发生死锁
  17. mysql> update test_tb set col3 = ‘gddx‘ where col1 = ‘a‘;
  18. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

发生死锁后会选择一个事务进行回滚，想查明死锁原因，可以执行 show engine innodb status 来查看死锁日志，根据死锁日志，结合业务逻辑来进一步定位死锁原因。

在实际应用中，我们要尽量避免死锁现象的发生，可以从以下几个方面入手：

  • 事务尽可能小，不要将复杂逻辑放进一个事务里。
  • 涉及多行记录时，约定不同事务以相同顺序访问。
  • 业务中要及时提交或者回滚事务，可减少死锁产生的概率。
  • 表要有合适的索引。
  • 可尝试将隔离级别改为 RC 。

总结：

本篇文章简单介绍了锁等待及死锁发生的原因，其实真实业务中发生死锁还是很难分析的，需要一定的经验积累。本篇文章只是面向初学者，希望各位对死锁能够有个简单的印象。

MySQL锁等待与死锁问题分析

上一篇：Django基础


下一篇：photoshop利用滤镜制作闪电效特效