mysql - 为什么我在 MySQL 的延迟作业表上出现死锁？
问题描述
我总是在早上知道我的所有查询都被阻塞了，当我在数据库上执行 SHOW ENGINE INNODB STATUS 时，我总是会在延迟的作业表上发生死锁。
我们在桌子上做什么。
- 如果我们需要任何新工作，我们将数据插入到这个延迟工作表中。
- 当 crontab 拾取任何作业时，它将更新同一张表中的“loacked_at”列。
- 当作业完成时，它将从延迟作业表中删除该行。
可能是由于这个原因，所有 cronjob 都没有按预期工作。此外，表没有更多的行。
请在下面找到详细信息。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-08-11 05:17:18 0x2abdcb005700
*** (1) TRANSACTION:
TRANSACTION 1620501456, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 20 row lock(s)
MySQL thread id 207128, OS thread handle 47005828933376, query id 28453970720 10.0.0.77 root Searching rows for update
UPDATE `delayed_jobs` SET `delayed_jobs`.`locked_at` = '2021-08-11 05:17:18', `delayed_jobs`.`locked_by` = 'delayed_job.10 host:ip-10-0-0-77 pid:26747' WHERE ((run_at <= '2021-08-11 05:17:18.126260' AND (locked_at IS NULL OR locked_at < '2021-08-09 05:17:18.126272') OR locked_by = 'delayed_job.10 host:ip-10-0-0-11 pid:26747') AND failed_at IS NULL) ORDER BY priority ASC, run_at ASC LIMIT 1
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 11874 page no 5 n bits 904 index delayed_jobs_priority of table `plum_production`.`delayed_jobs` trx id 1620501456 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa5563d7; asc Uc ;;
2: len 4; hex 80232ba7; asc #+ ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa5570ba; asc Up ;;
2: len 4; hex 802398cc; asc # ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa5571c9; asc Uq ;;
2: len 4; hex 80239c5e; asc # ^;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa557ca1; asc U| ;;
2: len 4; hex 8023c1df; asc # ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa56420b; asc VB ;;
2: len 4; hex 8024264e; asc $&N;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa5646e0; asc VF ;;
2: len 4; hex 8024c5d7; asc $ ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa565118; asc VQ ;;
2: len 4; hex 80251b54; asc % T;;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa56537b; asc VS{;;
2: len 4; hex 80256288; asc %b ;;
Record lock, heap no 130 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa56544e; asc VTN;;
2: len 4; hex 802569f5; asc %i ;;
Record lock, heap no 283 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa56544e; asc VTN;;
2: len 4; hex 802569f9; asc %i ;;
Record lock, heap no 457 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa56544d; asc VTM;;
2: len 4; hex 802569f0; asc %i ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11874 page no 4 n bits 200 index PRIMARY of table `plum_production`.`delayed_jobs` trx id 1620501456 lock_mode X locks rec but not gap waiting
Record lock, heap no 124 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 802569f9; asc %i ;;
1: len 6; hex 00006096e3d1; asc ` ;;
2: len 7; hex 020000a6cf03ef; asc ;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 7; hex 64656661756c74; asc default;;
6: len 20; hex 00002e6200016bd0000000010000000000005570; asc .b k Up;;
7: len 20; hex 00002e6200016bfd00000001000000000000280f; asc .b k ( ;;
8: len 5; hex 99aa565458; asc VTX;;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: len 5; hex 99aa56544e; asc VTN;;
13: len 5; hex 99aa565452; asc VTR;;
*** (2) TRANSACTION:
TRANSACTION 1620501457, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 207079, OS thread handle 46952045086464, query id 28453970751 10.0.0.77 root updating
UPDATE `delayed_jobs` SET `last_error` = 'Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: DELETE FROM `delayed_jobs` WHERE `delayed_jobs`.`id` = 2451961\n/var/www/apps/plumslice/shared/bundle/ruby/2.5.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query\'\n/var/www/apps/plumslice/shared/bundle/ruby/2.5.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query\'\n/var/www/apps/plumslice/shared/bundle/ruby/2.5.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt\'\n/var/www/apps/plumslice/shared/bundle/ruby/2.5.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query\'\n/var/www/apps/plumslice/shared/bundle/ruby/2.5.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute\'\n/var/www/apps/plumslice/shared/bundle/ruby/2.5.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11874 page no 4 n bits 200 index PRIMARY of table `plum_production`.`delayed_jobs` trx id 1620501457 lock_mode X locks rec but not gap
Record lock, heap no 124 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 802569f9; asc %i ;;
1: len 6; hex 00006096e3d1; asc ` ;;
2: len 7; hex 020000a6cf03ef; asc ;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 7; hex 64656661756c74; asc default;;
6: len 20; hex 00002e6200016bd0000000010000000000005570; asc .b k Up;;
7: len 20; hex 00002e6200016bfd00000001000000000000280f; asc .b k ( ;;
8: len 5; hex 99aa565458; asc VTX;;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: len 5; hex 99aa56544e; asc VTN;;
13: len 5; hex 99aa565452; asc VTR;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11874 page no 5 n bits 904 index delayed_jobs_priority of table `plum_production`.`delayed_jobs` trx id 1620501457 lock_mode X locks rec but not gap waiting
Record lock, heap no 283 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000000; asc ;;
1: len 5; hex 99aa56544e; asc VTN;;
2: len 4; hex 802569f9; asc %i ;;
*** WE ROLL BACK TRANSACTION (2)
我是 MYSQL 技术的新手，请告诉我，我在哪里做错了或者背后的原因是什么。
解决方案
推荐阅读
- backend - 网页链接到后端专有应用的方法有哪些
- redirect - 如何在 http rest 调用上从后端启用浏览器 UI 重定向？
- python - 当我尝试更换 pady 时，为什么我的 Tkinter Button 会消失？
- python - 从二维数组中获取值以列出行列位置
- html - 使用 PowerShell 在电子邮件中将 excel 表作为正文发送
- git - [新到 git]git push origin master 创建了新的 master 分支。（回购中的默认主）
- r - 根据长度拆分字符向量
- python-3.x - pandas 将两列合并为一个新列
- jquery - ASP.NET Core 5 data-ajax 属性未应用于通过函数提交的表单
- sql - SQL 对不同列的多个查询