mysql - 为什么这两个查询会出现死锁？

Question

隔离级别为已提交读

第一个查询正在查找 service_id 为 0 的记录...另一个查询正在查找 service_id 不在 (0, ... other ... ) 的记录；

我认为他们会锁定不同的行？

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2017-08-18 09:01:24 7f2d05641700
    *** (1) TRANSACTION:
    TRANSACTION 201694975, ACTIVE 1 sec starting index read
    mysql tables in use 2, locked 2
    LOCK WAIT 46 lock struct(s), heap size 6544, 194 row lock(s)
    MySQL thread id 33600289, OS thread handle 0x7f2d0812b700, query id 3703173090 inf-rtpctllb02-prd.rtp.netapp.com 10.60.56.150 ctl Copying to tmp table
    SELECT
                        re.*,
                        r.config_id,
                        r.reserve_all_or_nothing,
                        r.owner,
                        r.charges
                    FROM
                        `job_charge` AS re,
                        `job` AS r WHERE
                        re.job_id = r.id AND ((re.status ='dispatched') or (re.status= 'running') or (re.status= 'held') or (re.status= 'reserved')) AND  ((re.service_id ='0')) AND r.disable = 0  ORDER BY r.priority,r.id LIMIT 10000  FOR UPDATE
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1485 page no 987 n bits 104 index `PRIMARY` of table `ctl`.`job_charge` trx table locks 2 total table locks 2  trx id 201694975 lock_mode X locks rec but not gap waiting lock hold time 1 wait time before grant 0 
    *** (2) TRANSACTION:
    TRANSACTION 201691925, ACTIVE 185 sec fetching rows
    mysql tables in use 4, locked 2
    1164 lock struct(s), heap size 128552, 2 row lock(s)
    MySQL thread id 33599597, OS thread handle 0x7f2d05641700, query id 3703158120 inf-rtpctllb02-prd.rtp.netapp.com 10.60.56.150 ctl updating
    UPDATE
                `job_charge`
            SET
                service_id = '0'
            WHERE 
                service_id NOT IN ('0','ctl5-staging_command-launcher.674d8c96-7c76-11e7-bc6c-ee0cf095fd00','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-S63.3987fd54-ee31-4c81-add4-4be53a6ed363:80','ctl5-staging_scheduler.912d008f-7c76-11e7-bc6c-ee0cf095fd00','ctl5-production_capacity-manager.6a869ee7-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_scheduler.91de7d76-7919-11e7-bc6c-ee0cf095fd00','mysql','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-S63.48fe0555-83e9-4811-bcbc-f301da498fa6:80','ctl5-production_cleaner.6a86c5fa-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_command-launcher.9f97a534-8413-11e7-bc6c-ee0cf095fd00','ctl5-production_reservation-manager.7ac1771d-7a9e-11e7-bc6c-ee0cf095fd00','ctl5-s
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 1485 page no 987 n bits 104 index `PRIMARY` of table `ctl`.`job_charge` trx table locks 1 total table locks 2  trx id 201691925 lock_mode X locks rec but not gap lock hold time 13 wait time before grant 12 
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1485 page no 1606 n bits 88 index `PRIMARY` of table `ctl`.`job_charge` trx table locks 1 total table locks 2  trx id 201691925 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 
    *** WE ROLL BACK TRANSACTION (1)

Answer 1

是的。两者必须是不同的行。您可以看到声明中所示的第987页和第1606页

这里交易 1 是

SELECT
                  re.*,
                  r.config_id,
                  r.reserve_all_or_nothing,
                  r.owner,
                  r.charges
              FROM
                  `job_charge` AS re,
                  `job` AS r WHERE
                  re.job_id = r.id AND ((re.status ='dispatched') or (re.status= 'running') or (re.status= 'held') or (re.status= 'reserved')) AND  ((re.service_id ='0')) AND r.disable = 0  ORDER BY r.priority,r.id LIMIT 10000  FOR UPDATE

交易 2 是

UPDATE
          `job_charge`
      SET
          service_id = '0'
      WHERE 
          service_id NOT IN ('0','ctl5-staging_command-launcher.674d8c96-7c76-11e7-bc6c-ee0cf095fd00','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-S63.3987fd54-ee31-4c81-add4-4be53a6ed363:80','ctl5-staging_scheduler.912d008f-7c76-11e7-bc6c-ee0cf095fd00','ctl5-production_capacity-manager.6a869ee7-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_scheduler.91de7d76-7919-11e7-bc6c-ee0cf095fd00','mysql','inf-mesos-slave001.ctl.gdl.englab.netapp.com:mesos-6b256982-4ef1-4a84-ba60-58245ee7406d-S63.48fe0555-83e9-4811-bcbc-f301da498fa6:80','ctl5-production_cleaner.6a86c5fa-7919-11e7-bc6c-ee0cf095fd00','ctl5-production_command-launcher.9f97a534-8413-11e7-bc6c-ee0cf095fd00','ctl5-production_reservation-manager.7ac1771d-7a9e-11e7-bc6c-ee0cf095fd00','ctl5-s

从给定的消息中，我们可以看到事务 1 正在等待表“ctl”主键上的独占锁(用 X 锁表示，将某些值写入表所需的锁)。

但与此同时，事务 2 出现，它已经持有“ctl”表主键上的 X 锁(第 987 页)。因此，由于事务 2 已经在“ctl”上获得了 X 锁，因此事务 1 无法获得 X 锁，因此正在等待。

但是事务 2 本身正在等待“ctl”上的另一个 X 锁(与上面不同的行，第 1606 页)。我认为该行由交易 1 持有。

所以，事务 1 持有第 1606 页中的行的锁，而事务 2 正在等待该行和事务 2 持有第 987 页中事务 1 正在等待的行的锁

因此，两者都在互相等待，因此发生了死锁。