Mysql学习笔记 - InnoDB并发死锁问题

全文 ≈ 1.3k字   |   预计阅读时长 7分钟

项目中某个定时任务执行过程中出现死锁问题，具体错误如下:

1
Error 1213: Deadlock found when trying to get lock; try restarting transaction

分析

检查一下表引擎 & 事务隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> select @@global.tx_isolation;
ERROR 1193 (HY000): Unknown system variable 'tx_isolation' // Mysql8.0以后取消了该变量

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

默认使用的引擎是InnoDB，Insert、Update、Delete操作会触发排它锁（X锁）即行锁，所以怀疑是不是触发了共享锁（S锁）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
mysql> show engine innodb status;

=====================================
2021-11-15 02:42:28 0x2b1f5b160700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-12 14:21:02 0x2b204ca06700
*** (1) TRANSACTION:
TRANSACTION 3805455, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 875327, OS thread handle 47411963238144, query id 20765167 10.85.62.212 dsc update
INSERT INTO `user_tag_distributes` (`created_at`,`updated_at`,`deleted_at`,`app_id`,`tag_name`,`tag_value`,`account_num`) VALUES ('2021-11-12 14:21:02.386','2021-11-12 14:21:02.386',NULL,'im','churn_model_v2','0.70709264',1)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 334 page no 341 n bits 160 index PRIMARY of table `api`.`user_tag_distributes` trx id 3805455 lock_mode X locks rec but not gap
Record lock, heap no 89 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 8; hex 000000000002515d; asc       Q];;
 1: len 6; hex 0000003a110f; asc    :  ;;
 2: len 7; hex 81000001960110; asc        ;;
 3: len 7; hex 99ab18e5420f14; asc     B  ;;
 4: len 7; hex 99ab18e5420f14; asc     B  ;;
 5: SQL NULL;
 6: SQL NULL;
 7: len 2; hex 696d; asc im;;
 8: len 14; hex 636875726e5f6d6f64656c5f7632; asc churn_model_v2;;
 9: len 10; hex 302e3730373039323634; asc 0.70709264;;
 10: len 3; hex 800001; asc    ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 334 page no 203 n bits 392 index tag of table `api`.`user_tag_distributes` trx id 3805455 lock mode S waiting
Record lock, heap no 88 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 14; hex 636875726e5f6d6f64656c5f7632; asc churn_model_v2;;
 1: len 9; hex 302e37303730393735; asc 0.7070975;;
 2: len 8; hex 0000000000022d5f; asc       -_;;


*** (2) TRANSACTION:
TRANSACTION 3805441, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 89 lock struct(s), heap size 24784, 17767 row lock(s), undo log entries 10247
MySQL thread id 875354, OS thread handle 47415383693056, query id 20765093 10.85.62.212 dsc updating
DELETE FROM `user_tag_distributes` WHERE app_id = 'im' and tag_name = 'churn_model_v2'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 334 page no 203 n bits 392 index tag of table `api`.`user_tag_distributes` trx id 3805441 lock_mode X locks rec but not gap
Record lock, heap no 88 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 14; hex 636875726e5f6d6f64656c5f7632; asc churn_model_v2;;
 1: len 9; hex 302e37303730393735; asc 0.7070975;;
 2: len 8; hex 0000000000022d5f; asc       -_;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 334 page no 341 n bits 160 index PRIMARY of table `api`.`user_tag_distributes` trx id 3805441 lock_mode X waiting
Record lock, heap no 89 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 8; hex 000000000002515d; asc       Q];;
 1: len 6; hex 0000003a110f; asc    :  ;;
 2: len 7; hex 81000001960110; asc        ;;
 3: len 7; hex 99ab18e5420f14; asc     B  ;;
 4: len 7; hex 99ab18e5420f14; asc     B  ;;
 5: SQL NULL;
 6: SQL NULL;
 7: len 2; hex 696d; asc im;;
 8: len 14; hex 636875726e5f6d6f64656c5f7632; asc churn_model_v2;;
 9: len 10; hex 302e3730373039323634; asc 0.70709264;;
 10: len 3; hex 800001; asc    ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 3945122
Purge done for trx's n:o < 3945122 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328886640459544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640456152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640455304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640457000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640448520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640447672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640452760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640458696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640446824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640453608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640457848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640451912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640451064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640454456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640450216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640449368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640445976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328886640445128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
...
----------------------------
END OF INNODB MONITOR OUTPUT
============================

通过以上信息确认是因为[事务1]Insert & [事务2]Delete互相等待对方事务释放锁的问题造成死锁，最后[事务1]进行回滚。

原因

review代码梳理一下业务逻辑，场景应该如下：

| trx1 | trx2 |
| —— | —— | —— |
| | BEGIN |
| BEGIN | DELETE FROM user_tag_distributes WHERE app_id = ‘im’ and tag_name = ‘churn_model_v2’ |
| DELETE FROM user_tag_distributes WHERE app_id = ‘im’ and tag_name = ‘churn_model_v2’ | |
| INSERT INTO user_tag_distributes (created_at,updated_at,deleted_at,app_id,tag_name,tag_value,account_num) VALUES (‘2021-11-12 14:21:02.386’,’2021-11-12 14:21:02.386’,NULL,’im’,’churn_model_v2’,’0.70709264’,1) | |
| ERROR 1213 (40001): Deadlock found when trying to get lock | |
| ROLLBACK | … |
| | COMMIT |

可以看出[事务1]进行Insert操作时发现[事务2]进行Delete操作且已申请X锁，[事务1]想要获取S锁则需要[事务2]提交，所以[事务1]、[事务2]在相互等待对方提交事务（释放锁）。

解决方案

目前想到的几种措施：

  • 尽量避免在并发程序中Delete & Insert操作无缝执行
  • 并发程序采用分布式锁控制
  • 程序不允许并发执行

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-standard-monitor.html

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

性格内向安静腼腆的<br />小屁屁