Update和Insert查询之间的死锁-Java 学习之路

我在Update和Insert查询之间的应用程序中遇到了死锁，我无法理解为什么会以导致死锁的方式给出锁 .

Environment-

应用程序 - Django
数据库 - MySQL 5.7
发动机 - Innodb
隔离级别 - READ COMMITTED .
表（为安全而更改名称） -
M - 主键 - id
MSC - 有M.id的外键
MSC上的索引
M（FK）指数
S（FK）指数
C（FK）指数
关于唯一约束的索引（M，S，C）

Queries- 以下两个查询（截断查询以仅显示相关列） -

Update -

UPDATEMSCSETm_id= 110,s_id= 1234,c_id= '9b39cd', WHEREMSC.id= 54362

Insert -

INSERT INTOMSC(m_id,s_id,c_id) VALUES (110, 1235, '9b39cd')

Deadlock-

首先触发更新查询，然后触发插入查询，但 SHOW ENGINE INNODB STATUS\G; 的输出显示先前启动的插入查询 .
从输出中，执行的时间似乎是以下方式导致死锁 -
Insert获取MSC上的独占（X）锁并等待外键M上的共享（S）锁 .
Update获取M上的独占（X）锁定并等待外键MSC上的独占（X）锁定 .
以下是完整输出 -

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2017-03-17 15:41:03 0x7f8039550700
    * (1) TRANSACTION:
    TRANSACTION 7784084, ACTIVE 2 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 11 lock struct(s), heap size 1136, 46 row lock(s), undo log entries 25
    MySQL thread id 493648, OS thread handle 140188693010176, query id 55263589 ip-10-198-7-203.ec2.internal 10.198.7.203 root update
    INSERT INTO MSC (m_id, s_id, c_id) VALUES (110, 1235, '9b39cd')
    * (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1377 page no 10 n bits 152 index PRIMARY of table "db"."M" trx id 7784084 lock mode S locks rec but not gap waiting
    Record lock, heap no 67 PHYSICAL RECORD: n_fields 42; compact format; info bits 0
     0: len 4; hex 800000ac; asc     ;;
     1: len 6; hex 00000076c69f; asc    v  ;;
     2: len 7; hex 76000001cb24c5; asc v    $ ;;
     3: len 8; hex 999be72e2e07032e; asc    ..  .;;
     4: len 8; hex 999c22fa43025221; asc   " C R!;; 

*** (2) TRANSACTION:
TRANSACTION 7784095, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 493645, OS thread handle 140188694415104, query id 55263635 ip-10-198-3-73.ec2.internal 10.198.3.73 root updating
UPDATE `MSC` SET `m_id` = 110, `s_id` = 1234, `c_id` = '9b39cd', WHERE `MSC`.`id` = 54362
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1377 page no 10 n bits 152 index PRIMARY of table "db"."M" trx id 7784095 lock_mode X locks rec but not gap
Record lock, heap no 67 PHYSICAL RECORD: n_fields 42; compact format; info bits 0
 0: len 4; hex 800000ac; asc     ;;
 1: len 6; hex 00000076c69f; asc    v  ;;
 2: len 7; hex 76000001cb24c5; asc v    $ ;;
 3: len 8; hex 999be72e2e07032e; asc    ..  .;;
 4: len 8; hex 999c22fa43025221; asc   " C R!;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1410 page no 261 n bits 104 index PRIMARY of table "db"."MSC" trx id 7784095 lock_mode X locks rec but not gap waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
 0: len 4; hex 800038e2; asc   8 ;;
 1: len 6; hex 00000076c694; asc    v  ;;
 2: len 7; hex 6f0000055b2a0e; asc o   [* ;;
 3: len 8; hex 999c22fa0d08a51c; asc   "     ;;
 4: len 8; hex 999c22fa3b0dffd8; asc   " ;   ;;

*** WE ROLL BACK TRANSACTION (2)

Questions- 我无法理解以下内容 - 1.为什么更新查询必须等待，并且在插入查询得到锁定时无法获取锁定？ 2.为什么更新查询需要/采用M表上的独占（X）锁定 .

请在这里分享您的想法 . 如果需要任何额外信息，请告诉我 .

2 回答

M表中是否存在id值110？此外，在 START TRANSACTION; 和 COMMIT; 命令中包装这些单独的事务可能很有用，以确保在更新尝试运行之前插入完成 .

例：

START TRANSACTION;
INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd')
COMMIT;

START TRANSACTION;
UPDATE `MSC` SET `m_id` = 110, `s_id` = 1234, `c_id` = '9b39cd', WHERE 
`MSC`.`id` = 54362
COMMIT;

回复于 2024-04-26T08:35:01+08:00

3
正如所说的@SergGr，你的两个查询不能导致死锁 . 但可能的下一个情况 . 例如，我们在 MSC 表中有下一条记录：
```
id      m_id  s_id  c_id
54362   109   1235  9b39cd
```
现在我们尝试并行运行下一个查询（我更改了更新并编写了1235而不是1234）：
```
UPDATE `MSC` SET `m_id` = 110, `s_id` = 1235, `c_id` = '9b39cd', 
WHERE `MSC`.`id` = 54362;

INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd');
```
我们必须在（ m_id ， s_id ， c_id ）上遇到唯一索引的问题 .

更新和插入可以并行启动，因为在开始执行之前没有约束问题 . 但查询不能完成，因为它们都必须产生相等的线，并且它们必须与唯一约束冲突 .

为避免这种情况，您可以使用强制锁定 . 例如，
```
START TRANSACTION;
SELECT * FROM M WHERE id = 110 FOR UPDATE;
UPDATE `MSC` SET `m_id` = 110, `s_id` = 1235, `c_id` = '9b39cd', 
WHERE `MSC`.`id` = 54362;
COMMIT;

START TRANSACTION;
SELECT * FROM M WHERE id = 110 FOR UPDATE;
INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd');
COMMIT;
```
我不喜欢类似的锁，因为在那之后问题可以在这里得到解决但是进入了更高的水平 . 如果可能，请修改数据库架构或算法 . 也许你会找到更优雅的方式来存储和更新你的数据，而不会出现死锁的可能性 .
回复于 2024-04-26T08:35:01+08:00

Update和Insert查询之间的死锁

2 回答

相关问题