MySQL【悲观锁】for update带where【任意条件】拿到【行锁】而不是表锁的通用方法

问题

数据库的悲观锁select * from tab where name like "%me%" for update;

如果where里不能直接明确table的主键，那么该语句拿到的锁就是表级锁。

update 和 delete与select..for update同理，主键不明确时都会拿到表锁

怎样算直接明确主键

• 直接明确主键：where id = 1或where id in (1,2,3)

• 间接明确主键：where id > 1 或where id between 1 and 10 或 where id=1 and 其他条件

• 不明确主键：where name like "%me%"或where id=1 or name like "%me%"

注意，只有第一种情况拿到的为行级锁，其他拿到的都是表级锁。

解决方法

应该不难想到分成两个查询来实现行级悲观锁。即先把条件涉及的主键查出来，select id from tab where name like "%me%";赋给List<Integer> idList，再传给for update的查询，以Mybatis为例：

select * from tab where id in
<foreach collection="idList" item="periodId" separator="," open="(" close=")">
#{idList}
</foreach>
for update;

就能成功拿到行锁。

一步到位的通用方法

即利用子查询实现直接明确主键

select * from tab where id in

(select id from tab where name like "%me%")

for update;

可套用在任何条件上

select 要返回的字段 from 表名 where 主键 in

(select 主键 from 表名 where 任何条件)

for update;

测试

建表如下

CREATE TABLE `tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
-- ----------------------------
-- Records of tab
-- ----------------------------
INSERT INTO `tab` VALUES ('1', 'me');
INSERT INTO `tab` VALUES ('2', 'thisisme');
INSERT INTO `tab` VALUES ('3', 'nolock');
INSERT INTO `tab` VALUES ('4', 'nolock');

创建如下查询并运行

set autocommit=0;
select * from tab where id in 
(select id from tab where name like "%me%") 
for update;

此时这两行已经被锁住，其他会话无法拿到锁而被阻塞

update tab set name="TrulyNoLock";

同时明确主键不为上面两行的语句能拿到锁，并成功运行

update tab set name="TrulyNoLock" where id=3 or id=4;

所以利用子查询实现带任意条件的悲观锁是能拿到行锁的。

但update不能这样，或许只能先将id查出来再赋到SQL。如果多加一重子查询为中间表，虽然能运行不报错，但拿到的是表锁。