MySQL自加入死锁

Question

我有以下疑问：

UPDATE messages QM JOIN (SELECT id FROM messages WHERE name = 'name'
 AND state = 0 ORDER BY priority DESC, timestamp DESC LIMIT 1) AS QM2
 ON QM.name ='name' AND QM.id = QM2.id SET QM.weight = QM.weight + 1;

(这里我需要自连接，因为mysql无法通过简单的更新将索引用于ORDER BY )

(name, state, priority, timestamp)列上有复合索引。它与上面的查询完全匹配。

我的问题是当查询率>80RPS时会出现大量的死锁：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-05-15 12:04:49 7f7992119b00
*** (1) TRANSACTION:
TRANSACTION 3923289, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 992, OS thread handle 0x7f7958b5eb00, query id 1325283 172.17.0.1 facade Sending data
UPDATE queue_messages m1 JOIN
      (SELECT id FROM queue_messages WHERE queue_name = ? AND state = 0 ORDER BY priority DESC, message_timestamp DESC LIMIT ?)
          AS m2 ON m1.queue_name = ? AND m1.id = m2.id SET m1.state = 1, m1.transient_token = ?, m1.transient_token_expiration_timestamp = ?, m1.retry_count = retry_count + 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1772 page no 2464 n bits 304 index `QUEUE_NAME_STATE_PRIORITY_MESSAGE_TIMESTAMP_INDEX` of table `actionmq_data`.`queue_messages` trx table locks 1 total table locks 4  trx id 3923289 lock mode S waiting lock hold time 0 wait time before grant 0 

*** (2) TRANSACTION:
TRANSACTION 3923243, ACTIVE 0 sec updating or deleting
mysql tables in use 2, locked 2
9 lock struct(s), heap size 2936, 9 row lock(s), undo log entries 1
MySQL thread id 975, OS thread handle 0x7f7992119b00, query id 1325044 172.17.0.1 facade updating reference tables
UPDATE queue_messages m1 JOIN
      (SELECT id FROM queue_messages WHERE queue_name = ? AND state = 0 ORDER BY priority DESC, message_timestamp DESC LIMIT ?)
          AS m2 ON m1.queue_name = ? AND m1.id = m2.id SET m1.state = 1, m1.transient_token = ?, m1.transient_token_expiration_timestamp = ?, m1.retry_count = retry_count + 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1772 page no 2464 n bits 304 index `QUEUE_NAME_STATE_PRIORITY_MESSAGE_TIMESTAMP_INDEX` of table `actionmq_data`.`queue_messages` trx table locks 2 total table locks 4  trx id 3923243 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1772 page no 2469 n bits 168 index `QUEUE_NAME_STATE_PRIORITY_MESSAGE_TIMESTAMP_INDEX` of table `actionmq_data`.`queue_messages` trx table locks 2 total table locks 4  trx id 3923243 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0 
*** WE ROLL BACK TRANSACTION (1)

我怎么才能修复它呢？

Answer 1

你能试试这个吗：

UPDATE messages QM SET QM.weight = (QM.weight + 1) WHERE QM.id = (SELECT id FROM messages WHERE name = 'name' AND state = 0 ORDER BY priority DESC, timestamp DESC LIMIT 1) 

Answer 2

如果id是PRIMARY KEY，那么在name上加入是多余的：

ON QM.name ='name' AND QM.id = QM2.id

看看这是否有更好的效果：

BEGIN;
SELECT  @id := id
    FROM  messages
    WHERE  name = 'name'
      AND  state = 0
    ORDER BY  priority DESC, timestamp DESC
    LIMIT  1
    FOR UPDATE;
UPDATE messages
    SET weight = weight + 1
    WHERE id = @id;
COMMIT;

或

UPDATE messages
    SET weight = weight + 1
    WHERE id = ( SELECT  id
                   FROM  messages
                   WHERE  name = 'name'
                     AND  state = 0
                   ORDER BY  priority DESC, timestamp DESC
                   LIMIT  1
               );

或者简单地说

UPDATE messages
    SET weight = weight + 1
    WHERE  name = 'name'
      AND  state = 0
    ORDER BY  priority DESC, timestamp DESC
    LIMIT  1;