mysql - 更新语句可以使用插入意向锁吗？

Question

数据库遇到两个事务并发更新导致的死锁问题。

LATEST DETECTED DEADLOCK
------------------------
2019-04-18 15:54:09 0x7f85cff7e700

*** (1) TRANSACTION:
TRANSACTION 70678199277, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 137 lock struct(s), heap size 24784, 689 row lock(s), undo log entries 10
MySQL thread id 6314744, OS thread handle 140210780473088, query id 1764862374 10.32.94.170 m_pr_d090 Searching rows for update
UPDATE table1 SET  status =1 WHERE c_Id = 24671 and d_Id =1247910

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12918 page no 4088 n bits 688 index idx_cinemaid_dcardid_status of table `mr`.`table1` trx id 70678199277 lock_mode X waiting



*** (2) TRANSACTION:
TRANSACTION 70678199289, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
144 lock struct(s), heap size 24784, 721 row lock(s), undo log entries 13
MySQL thread id 6313652, OS thread handle 140212696508160, query id 1764862806 10.4.189.142 m_pr_d090 updating
UPDATE table1 SET  status =1 WHERE c_Id = 24670 and d_Id =1247910

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12918 page no 4088 n bits 688 index idx_cinemaid_dcardid_status of table `mr`.`table1` trx id 70678199289 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12918 page no 4088 n bits 688 index idx_cinemaid_dcardid_status of table `mr`.`table1` trx id 70678199289 lock_mode X locks gap before rec insert intention waiting

cId,d_Id为联合索引，非唯一

session 1UPDATE table1 SET status =1 WHERE c_Id = 24670 和 d_Id =1247910

第二节课UPDATE table1 SET status =1 WHERE c_Id = 24671 和 d_Id =1247910

Answer 1

避免实际死锁的一个简单方法是在运行实际更新之前使用 SELECT ... FOR UPDATE:

SELECT * FROM table1 WHERE c_Id = 24670 AND d_Id = 1247910 FOR UPDATE;

然后:

UPDATE table1 SET status = 1 WHERE c_Id = 24670 AND d_Id = 1247910;

使用 FOR UPDATE 至少应该保证两个事务不会进入涉及更新目标行的同一关键部分。

这并不一定意味着饥饿之类的事情不会仍然发生，但它应该避免正式的僵局。