select .... for update究竟锁表还是锁行？_dbeaver for update

介绍

select查询语句是不会加锁的，但是​​select …for update​​除了有查询的作用外，还会加锁呢，而且它是悲观锁。

在接触到这个语句之前，我一直认为它是加行锁的，但是我看有些文章是要看索引，因此我想做一个深入了解。

实例验证

实例前准备数据

user表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  `prov_id` int(11) NOT NULL,
  `prov_code` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE,
  KEY `user_prov_id_IDX` (`prov_id`,`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570069 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11

prov_info表

CREATE TABLE `prov_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `prov_info_code_IDX` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

user数据：

INSERT INTO test.`user`
(id, name, age, code, prov_id, prov_code)
VALUES(1, '测试1', 46, '0001', 21, 'K001');
INSERT INTO test.`user`
(id, name, age, code, prov_id, prov_code)
VALUES(2, '测试2', 20, '0002', 22, 'K002');
INSERT INTO test.`user`
(id, name, age, code, prov_id, prov_code)
VALUES(3, '测试3', 10, '0003', 23, 'K003');
1
2
3
4
5
6
7
8
9

prov_info数据

INSERT INTO test.prov_info
(id, name, code)
VALUES(21, '极阴岛', 'K001');
INSERT INTO test.prov_info
(id, name, code)
VALUES(22, '小环岛', 'K002');
INSERT INTO test.prov_info
(id, name, code)
VALUES(23, '中岛', 'K003');
1
2
3
4
5
6
7
8
9

在Dbeaver开启两个会话，分别作为事务1和事务2，设置事务1不自动提交

-- 关闭自动提交
select @@autocommit;

set @@autocommit = 0;
1
2
3
4

实例1–验证通过主键索引查询会锁行

在事务1中对id=1，使用id作为查询条件，执行for update,不提交
在事务2中执行update语句
事务1：

select * from test.`user` u where u.id = 1 for update;
1

结果图：

事务2：（自动提交）

update test.`user` u set age = 46 where u.id = 1;
update test.`user` u set age = 20 where u.id = 2;
1
2

更新id=1的数据阻塞，更新id=2的数据没有阻塞

可以证明是锁行，如果是锁表的话，id=2的数据也会阻塞

实例2–验证通过普通索引作为查询条件会锁行

将之前事务提交。
在事务1中对id=3，使用age作为查询条件，执行for update,不提交
在事务2中执行update语句
事务1：

select * from test.`user` u where u.age = 10 for update;
1

结果图：
![在这里插入图片描述](https://img-blog.csdnimg.cn/301d7c88014a4031b516c0cd1ea496e8.png

事务2：（自动提交）

update test.`user` u set age = 20 where u.id = 2;
update test.`user` u set age = 20 where u.id = 3;

1
2
3

更新id=3的数据阻塞，更新id=2的数据没有阻塞

可以证明是锁行

实例3–验证查询条件不命中索引会锁表

将之前事务提交。
在事务1中对id=3，使用code作为查询条件，执行for update,不提交
在事务2中执行update语句
事务1：

select * from test.`user` u where u.code = '0003' for update;
1

结果图：

事务2：（自动提交）

update test.`user` u set age = 20 where u.id = 2;
update test.`user` u set age = 20 where u.id = 3;

1
2
3

更新id=3的数据阻塞，更新id=2的数据也会阻塞

由此可以证明是锁表

实例4–验证联合索引匹配到第一个字段也会锁行

这个实例跟实例2相同，只不过我想看看联合索引只匹配第一个字段是不是也可以锁行
事务1：

select * from test.`user` u where u.prov_id  = 23 for update;
1

事务2：

update test.`user` u set age = 20 where u.id = 2;
update test.`user` u set age = 20 where u.id = 3;
1
2

结果：
更新id=3的数据阻塞，更新id=2的数据没有阻塞

由此可以证明对于联合索引来说，只要命中了索引就可以锁行（联合索引要按顺序）

实例5–验证关联表情况下关联条件是索引会锁行

事务1：

select * from test.`user` u 
inner join test.prov_info pi2 on u.prov_id = pi2 .id 
where u.prov_id  = 23 for update;
1
2
3

事务2：

update test.`user` u set age = 20 where u.id = 2;
update test.`user` u set age = 20 where u.id = 3;
UPDATE test.prov_info SET name='小环岛', code='K002' WHERE id=22;
UPDATE test.prov_info SET name='中岛', code='K003' WHERE id=23;
1
2
3
4

结果：

user表id=2的数据没有阻塞，id=3的数据阻塞，是锁行
prov_info表id=22的数据没有阻塞，id=23的数据阻塞，是锁行

实例6–验证关联表情况下关联条件不是索引只会锁行

事务1：

select * from test.`user` u 
inner join test.prov_info pi2 on u.prov_code = pi2 .code 
where u.prov_id  = 23 for update;
1
2
3

事务2：

update test.`user` u set age = 20 where u.id = 2;
update test.`user` u set age = 20 where u.id = 3;
UPDATE test.prov_info SET name='小环岛', code='K002' WHERE id=22;
UPDATE test.prov_info SET name='中岛', code='K003' WHERE id=23;
1
2
3
4

结果：

user表id=2的数据没有阻塞，id=3的数据阻塞，是锁行
prov_info表id=22的数据没有阻塞，id=23的数据阻塞，是锁行

结论

如果查询条件命中了索引/主键，那么​​select … for update​​就会进行行锁。
如果是普通字段(没有索引/主键)，那么​​select … for update​​就会进行锁表。

对于关联表的情况：
如果关联条件用到索引，那么关联表就会进行锁行
如果关联条件没有用到索引，那么关联表也会进行锁行
锁行只是跟另外一个关联相关联的行

参考

https://blog.51cto.com/javastack/4635681