› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
这是一个创建于 1082 天前的主题，其中的信息可能已经有所发展或是发生改变。
# InnoDB , RR 事务隔离级别
# t2 表的 pulbish_id 为非唯一索引
# S1 语句：
INSERT INTO `t1` (contract_no...)
SELECT 'A10-SJZ-21017' ...
FROM DUAL
WHERE NOT EXISTS(SELECT *
FROM `t2` tmrd
where publish_id = '1123424214'); #这里的 publish_id 一定不存在，所以会产生全表锁
# S2 语句：
INSERT INTO t2
(publish_id...)
values ('1123424214'...)
目前的问题是：
- 事务一运行 S1 未提交，事务二运行 S2 会阻塞
- 事务一运行 S1 未提交，事务二运行对 t2 表的
for update/lock in share model不会阻塞 - 事务一运行 S1 未提交，事务二运行 S1 ，S2 （阻塞），事务一运行 S2 ，产生死锁异常
[40001][1213] Deadlock found when trying to get lock; try restarting transaction
19 条回复 • 2022-01-05 09:58:00 +08:00
 |
1
yibo2018 OP 问题一：
事务一种 S1 会在 t2 表产生共享锁，事务二 insert t2 首先会插入意向写锁，所以阻塞 问题二： 事务一运行 S1 同上，所以事务二运行 t2 的 for update / lock in share model 应该会阻塞！但是并没有阻塞，和问题一的答案相悖 问题三： 如果问题一回答的是正确的，那么是可以解释的 |
|
2
yibo2018 OP 翻阅了大量的资料，让我头越来越大，确丝毫用不到自己的问题中，学以致用对我来说真难，我好菜
|
 |
3
orzwalker111 2021-12-31 16:01:11 +08:00
个人理解：
1.s1 事务未提交，因为间隙锁，事务 2 执行 s2 阻塞，正确 2.s1 select 添加悲观锁（ x/s ），命中覆盖索引 publish_id ，加锁范围(xx, 1123424214]，下一个锁范围是(1123424214, xxx]，继续向右遍历加 next-key lock ，因为最右值 xxx 不等于 1123424214 ，这个锁回退化成间隙锁(1123424214,xxx)；加锁结束后，事务 2 执行 s2 ，inset 时，publish_id= 1123424214 不在锁范围，所以写入成功，正确 |
|
4
orzwalker111 2021-12-31 16:04:41 +08:00
--(xx, 1123424214]这个回退化成间隙锁(xx, 1123424214)
|
 |
5
Feiex 2021-12-31 16:12:51 +08:00
INSERT INTO T SELECT……FROM S WHERE……
对于被插入到表 T 中的元组，在其对应的索引项上施加排他记录锁 《数据库事务处理的艺术》 |
|
6
yibo2018 OP @orzwalker111 哇塞分析的很详细
问题 2 中 事务一运行 S1 ，如果 publish_id 没有命中的话，会产生全表的间隙锁吧？（自问自答）也不其然，有一个 semi-consistent read 机制，对于不满足查询条件的记录，MySQL 会提前释放，同时不加 GAP 锁，就和你说的退化一样。 但是对于事务 2 的 select ... where publish_id = 1123424214 for update 也是针对全表的排它锁，如果他也退化，至少也要对 1123424214 左右进行 GAP ，但是目前看下来也没有（没阻塞） |
 |
8
ozipin 2021-12-31 17:10:54 +08:00
产生锁是和索引有关的，有索引应该是间隙锁。然后 s1 加的应该是读锁吧，所以 s2 for update 不会阻塞，读写之间才会阻塞
|
|
9
yibo2018 OP @ozipin 不对哦，我可以试试
我简单的测试了下 事务一 SELECT * FROM `t2` tmrd where publish_id = '1123424214' lock in share mode 事务二 SELECT * FROM `t2` tmrd where publish_id = '1123424214' for update 事务二阻塞了 换句话说 for update 加的是 X 锁（排它锁）也就是写锁 |
|
10
Feiex 2021-12-31 19:52:42 +08:00
@yibo2018 #5 ，在 S 表的索引项加的 s 锁；
你的过程里事务二的 s2 要申请 t2 表的 ix 锁，就需要事务一的 s1 先释放 t2 表的 s 锁；然后事务一又想申请 t2 表的 ix 锁，就成环了 |
|
11
yibo2018 OP @Feiex 嗯，问题三的死锁问题是可以解释的
但是问题 2 就让我很困惑，对 t2 的共享锁（ S1 语句运行）和排它锁（对 t2 进行 for update ）应该是互斥。但其实没有互斥锁。 写着写着突然发现答案了： 那么原因只有一个就是：俩个上锁的地方没有重合！ 即便是全表加锁，也会因为 MySQL 自己的优化机制退化。 为了证实上述的点，我选择了一个已经有的条件，会上间隙锁+定向锁，结果是阻塞了 至此我提出来的 3 个问题就解决了，感谢大家 |
 |
12
YIERIC 2022-01-04 14:40:31 +08:00
@yibo2018 请教一下，“事务一运行 S1 未提交，事务二运行 S2 会阻塞”，为什么“事务一种 S1 会在 t2 表产生共享锁”？
|
|
13
YIERIC 2022-01-04 14:42:20 +08:00
@YIERIC 因为既没有 for update / lock in share model 也没有数据更新，所以我不明白为什么会在 t2 上有共享锁
|
|
14
yibo2018 OP @YIERIC 我的理解是 s1 (insert ... exists (select ...)) 这个形式会对 select 的内容上共享锁
|
|
16
YIERIC 2022-01-04 18:14:49 +08:00
@yibo2018 我的理解是这样的：
1 、事务一持有 t2 间隙锁，事务二插入意向锁被间隙锁阻塞 2 、事务一持有 t2 间隙锁，事务二同样间隙锁，不阻塞 3 、事务一持有 t2 间隙锁，事务二运行 S1 不阻塞（同 2 ），运行 S2 阻塞（同 1 ，插入意向锁被间隙锁阻塞），事务一运行 S2 阻塞（插入意向锁被间隙锁阻塞），彼此的插入意向锁在等对方的间隙锁，所以死锁了 |
|
18
YIERIC 2022-01-04 18:30:53 +08:00
再补充一句，是间隙锁不互斥，而不是 next-lock
|
Select Language