首页 / MYSQL / InnoDB的MySQL死锁问题

InnoDB的MySQL死锁问题

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了InnoDB的MySQL死锁问题，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7235字，纯文字阅读大概需要11分钟。

内容图文

我有一个中央数据库服务器和几个“工作者”服务器,它们同时执行以下查询：

UPDATE job_queue 
SET
  worker = '108.166.81.112',
  attempts = attempts + 1,
  started = '2014-01-14 10:34:03',
  token = '13eb3e6a8c3e1becb34051e08f19fd62'
WHERE completed = '0000-00-00 00:00:00'
  AND (started = '0000-00-00 00:00:00' OR started < '2014-01-14 10:29:03')
  AND attempts < 2
ORDER BY priority DESC, inserted
LIMIT 1

有时我的job_queue表被锁定,并且如果我运行“ SHOW ENGINE INNODB STATUS”,我将得到如下信息：

------------------------
LATEST DETECTED DEADLOCK
------------------------
140114 10:34:15
*** (1) TRANSACTION:
TRANSACTION 0 46984514, ACTIVE 0 sec, process no 590, OS thread id 140366633146112 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3024, 545 row lock(s)
MySQL thread id 677401, query id 19385205 10.179.103.110 root init
UPDATE job_queue SET worker='108.166.81.112', attempts=attempts+1, started='2014-01-14 10:34:03', token='13eb3e6a8c3e1becb34051e08f19fd62' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 10:29:03') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 245767 n bits 128 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984514 lock_mode X waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800210; asc    ;; 1: len 6; hex 000002cced25; asc      %;; 2: len 7; hex 000003c00f1970; asc       p;; 3: len 30; hex 4f3a31343a2243425343616368654170704a6f62223a363a7b733a31393a; asc O:14:"CBSCacheAppJob":6:{s:19:;...(truncated); 4: len 1; hex 80; asc  ;; 5: len 8; hex 800012513c58bf24; asc    Q<X $;; 6: len 8; hex 800012513c58cc17; asc    Q<X  ;; 7: len 14; hex 31302e3137392e3130332e313333; asc 10.179.103.133;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58cc32; asc    Q<X 2;; 10: len 0; hex ; asc ;; 11: len 30; hex 353264393033616162656634346239626536306463346438666432303066; asc 52d903aabef44b9be60dc4d8fd200f;...(truncated);

*** (2) TRANSACTION:
TRANSACTION 0 46984485, ACTIVE 17 sec, process no 590, OS thread id 140366633547520 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 676723, query id 19385209 10.179.103.133 root init
UPDATE job_queue SET worker='10.179.103.133', attempts=attempts+1, started='2014-01-14 10:34:03', token='efd21d0d34f44badbc30386db4dd252e' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 10:29:03') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 245767 n bits 128 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984485 lock_mode X locks rec but not gap
Record lock, heap no 34 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800210; asc    ;; 1: len 6; hex 000002cced25; asc      %;; 2: len 7; hex 000003c00f1970; asc       p;; 3: len 30; hex 4f3a31343a2243425343616368654170704a6f62223a363a7b733a31393a; asc O:14:"CBSCacheAppJob":6:{s:19:;...(truncated); 4: len 1; hex 80; asc  ;; 5: len 8; hex 800012513c58bf24; asc    Q<X $;; 6: len 8; hex 800012513c58cc17; asc    Q<X  ;; 7: len 14; hex 31302e3137392e3130332e313333; asc 10.179.103.133;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58cc32; asc    Q<X 2;; 10: len 0; hex ; asc ;; 11: len 30; hex 353264393033616162656634346239626536306463346438666432303066; asc 52d903aabef44b9be60dc4d8fd200f;...(truncated);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 57 n bits 120 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984485 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800001; asc    ;; 1: len 6; hex 000002ccdab1; asc       ;; 2: len 7; hex 000003c0352b3f; asc     5+?;; 3: len 30; hex 4f3a31323a224175746f50696c6f744a6f62223a363a7b733a31383a2200; asc O:12:"AutoPilotJob":6:{s:18:" ;...(truncated); 4: len 1; hex 82; asc  ;; 5: len 8; hex 800012513c58af57; asc    Q<X W;; 6: len 8; hex 800012513c58bf22; asc    Q<X ";; 7: len 14; hex 3130382e3136362e38312e313132; asc 108.166.81.112;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58bf23; asc    Q<X #;; 10: len 0; hex ; asc ;; 11: len 30; hex 616331376430346339326163613366323330646164323239363764336266; asc ac17d04c92aca3f230dad22967d3bf;...(truncated);

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 46989905
Purge done for trx's n:o < 0 46986227 undo n:o < 0 0
History list length 24
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 590, OS thread id 140366628529920
MySQL thread id 703864, query id 20047015 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 46989894, not started, process no 590, OS thread id 140366636758784
MySQL thread id 702822, query id 20046897 10.179.1.63 root
---TRANSACTION 0 46986223, ACTIVE 39782 sec, process no 590, OS thread id 140366626322176
25 lock struct(s), heap size 3024, 710 row lock(s), undo log entries 9
MySQL thread id 677706, query id 19994505 10.179.103.114 root
Trx read view will not see trx with id >= 0 46986224, sees < 0 46986224

对表的任何进一步写操作都会使我超时,直到我重新启动MySQL服务器(或手动终止死锁的作业)为止：

PHP Fatal error:  Lock wait timeout exceeded; try restarting transaction(Query: "UPDATE job_queue SET worker='108.166.81.250', attempts=attempts+1, started='2014-01-14 21:27:45', token='369eae55a7f0eacad3b678a3410de8e4' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 21:22:45') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1") in /utilities/Database.php on line 53

谁能向我解释为什么该查询会导致死锁？我的印象是,InnoDB表上的所有查询都是原子发生的.有任何想法吗？

解决方法:

这将导致死锁,因为UPDATE查询将锁定表中的所有行,并且根据所使用的索引(或缺少索引),两个不同的会话可能会以略有不同的顺序锁定它们.请记住,UPDATE,DELETE和SELECT … FOR UPDATE将锁定它们遇到的所有行,无论这些行是否符合所有WHERE条件.因此,在使用它们时,应通过使用索引(最好是主键)并避免模糊或广泛选择的条件来努力确保它们遇到的行尽可能少.

我对工作队列的建议非常普遍：尽可能以确定的顺序锁定尽可能少的锁.因此,通常：

>使用非锁定读取(常规SELECT),通过查找您的工作人员知道该怎么做且当前无人认领的东西(lease_owner IS NULL和lease_expiry IS NULL或类似)来查找要完成的工作.
>选择一个工作项(如果您愿意,也可以选择几个,但是一个要简单得多,通常可以实现完全可接受的性能).
>更新您的工作项(要声明它,但无论如何也需要更新)：

>打开交易.
>使用SELECT … FOR UPDATE锁定选择的工作项-如果不再取消它,请中止并选择另一个.
>用您的工作人员ID和您的租约到期时间更新您选择的工作项目.
>立即提交交易.

>开始处理您租用的工作项目.
>在其他过程中,另一位民意调查者将查找废弃的作品并取消认领(通过上述相同的更新过程).

通过这种设计,您可以轻松地获得非常高的吞吐量(每秒数千个作业),并且基本上没有竞争,也没有订购问题.选择不太可能与其他民意测验者冲突的工作的优化是简单有效的(例如,选择工作ID或类似的模数,以避免工作饿死).关键是要记住工作选择上的冲突是可以的-只需中止然后重试,一切都会很快进行.

工作队列项目/作业的所有锁定写操作都只能在单行上执行,并且只能通过主键完成.

内容总结

以上是互联网集市为您收集整理的InnoDB的MySQL死锁问题全部内容，希望文章能够帮你解决InnoDB的MySQL死锁问题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/884346.html

来源：【匿名】