mysql的innodb死锁问题_InnoDB的MySQL死锁问题

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

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

*** (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

*** (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

*** (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

*** (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

*** 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

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