|
|
|
|
公众号矩阵

MySQL锁等待与死锁问题分析

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

作者: MySQL技术 来源: MySQL技术|2021-03-26 10:40

 

本文转载自微信公众号「MySQL技术」,作者MySQL技术。转载本文请联系MySQL技术公众号。

前言:

在 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 TableCREATE 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) 
  13.  
  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. rows in set (0.00 sec) 
  23.  
  24. # 事务一首先执行 
  25. mysql> begin
  26. Query OK, 0 rows affected (0.00 sec) 
  27.  
  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) 
  35.  
  36. # 事务二然后执行 
  37. mysql> begin
  38. Query OK, 0 rows affected (0.01 sec) 
  39.  
  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 则代表该事务处于等待状态 
  3.  
  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. rows in set (0.00 sec) 
  56.  
  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:当前事务的隔离级别。 
  71.  
  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 
  6.  
  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 
  11.  
  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. # 回到事务二再执行 此时二者相互等待发生死锁 
  18. mysql> update test_tb set col3 = 'gddx' where col1 = 'a'
  19. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 

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

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

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

总结:

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

【编辑推荐】

  1. mysql基础与提升教程
  2. MySQL性能优化知识:长连接、短连接、连接池
  3. mySQL数据库教程
  4. 面试官上来就问MySQL事务,瑟瑟发抖...
  5. MYSQL8.0新特性及WINDOWS平台安装演示01
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

7人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

36人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

229人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微