mysql - 避免简单更新查询上的mysql死锁
问题描述
我在这个简单的更新查询中遇到 mysql 死锁错误:
UPDATE messages SET seen = 1 WHERE fromId=10 AND toId=20 AND seen = 0
此查询用于在用户打开与其他用户的聊天时看到的标记收到的消息。
从 innodb status 命令获取的最新死锁日志:
LATEST DETECTED DEADLOCK
------------------------
2020-01-25 14:19:33 0x7f26cc19c700
*** (1) TRANSACTION:
TRANSACTION 11389580, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 18 lock struct(s), heap size 1136, 128 row lock(s)
MySQL thread id 952386, OS thread handle 139804764247808, query id 48510845 localhost 127.0.0.1 root updating
UPDATE `messages` SET `seen`=1 WHERE `seen` = 0 AND `fromId` = 16746 AND `toId` = 20242
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 163 page no 1965 n bits 280 index PRIMARY of table `game`.`messages` trx id 11389580 lock_mode X locks rec but not gap waiting
Record lock, heap no 57 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 4; hex 80156f39; asc o9;;
1: len 6; hex 000000956b88; asc k ;;
2: len 7; hex 60000001fa115f; asc ` _;;
3: len 4; hex 74657874; asc text;;
4: len 8; hex d8b3d984d8a7d985; asc ;;
5: len 1; hex 81; asc ;;
6: len 5; hex 99a56737d7; asc g7 ;;
7: len 4; hex 8000416a; asc Aj;;
8: len 4; hex 80003e7d; asc >};;
*** (2) TRANSACTION:
TRANSACTION 11389578, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
1515 lock struct(s), heap size 172240, 14139 row lock(s)
MySQL thread id 952395, OS thread handle 139804609726208, query id 48510843 localhost 127.0.0.1 root Searching rows for update
UPDATE `messages` SET `seen`=1 WHERE `seen` = 0 AND `fromId` = 16252 AND `toId` = 15997
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 163 page no 1965 n bits 280 index PRIMARY of table `game`.`messages` trx id 11389578 lock_mode X locks rec but not gap
Record lock, heap no 57 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 4; hex 80156f39; asc o9;;
1: len 6; hex 000000956b88; asc k ;;
2: len 7; hex 60000001fa115f; asc ` _;;
3: len 4; hex 74657874; asc text;;
4: len 8; hex d8b3d984d8a7d985; asc ;;
5: len 1; hex 81; asc ;;
6: len 5; hex 99a56737d7; asc g7 ;;
7: len 4; hex 8000416a; asc Aj;;
8: len 4; hex 80003e7d; asc >};;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 163 page no 1649 n bits 280 index PRIMARY of table `game`.`messages` trx id 11389578 lock_mode X locks rec but not gap waiting
Record lock, heap no 48 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 4; hex 8013206d; asc m;;
1: len 6; hex 000000895755; asc WU;;
2: len 7; hex 800000010d0110; asc ;;
3: len 4; hex 74657874; asc text;;
4: len 12; hex d8a8d8aed8b4db8cd8afd985; asc ;;
5: len 1; hex 80; asc ;;
6: len 5; hex 99a5627af5; asc bz ;;
7: len 4; hex 8000416a; asc Aj;;
8: len 4; hex 80003e66; asc >f;;
*** WE ROLL BACK TRANSACTION (1)
和表结构:
-- Table structure for table `messages`
--
CREATE TABLE `messages` (
`id` int(11) NOT NULL,
`type` varchar(24) COLLATE utf8mb4_bin NOT NULL DEFAULT 'text',
`content` varchar(400) COLLATE utf8mb4_bin NOT NULL,
`seen` tinyint(1) NOT NULL DEFAULT '0',
`createdAt` datetime NOT NULL,
`fromId` int(11) DEFAULT NULL,
`toId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- Indexes for table `messages`
ALTER TABLE `messages`
ADD PRIMARY KEY (`id`),
ADD KEY `fromId` (`fromId`),
ADD KEY `toId` (`toId`),
ADD KEY `messages_created_at` (`createdAt`),
ADD KEY `messages_seen` (`seen`);
我怎样才能避免这个 mysql 死锁?我的查询有什么问题吗?
解决方案
推荐阅读
- cuda - 如何通过 numba 在 CUDA 中顺序执行代码?
- python - 嵌套字典的递归函数返回 None 但是在函数中打印预期的数据
- c++ - 如何使用 std::vector 提供类方法的附加实现
作为参数? - python - 属性子集的只读数据视图(非副本)
- npm - 仅当 tfs build 中的版本更改时 npm publish
- angular - 从 observable 订阅 Children 类没有父字段
- visual-studio-code - 在打印语句中自动完成括号
- jquery - 如何在jquery append中做一个模型函数
- python - 如果在 Windows 上导入 python 包,Apache 不起作用
- python - 画布线未出现在屏幕上