时间戳

首页 > 解决方案 > 更新语句可以使用插入意图锁吗？

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

问题描述

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

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 为联合索引，非唯一

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

会话 2 UPDATE table1 SET status =1 WHERE c_Id = 24671 和 d_Id =1247910

标签： mysql

解决方案

避免实际死锁的一种简单方法是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至少应该保证两个事务不会进入涉及更新所针对的行的相同关键部分。

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

推荐阅读