首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我
logo

Java 面试宝典：select...for update 是行锁还是表锁？

原
 2024-11-09
©版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 本文链接：https://www.skjava.com/mianshi/baodian/detail/4560279968

回答

单纯的 select 语句是不会加锁的，但是使用 select ... for update 查询时，MySQL 会加排他锁，至于是行锁还是表锁，需要根据实际情况来确认：没用索引/主键的话就是表锁，否则就是是行锁。

分析

环境准备

  • 新建如下一张表结构
CREATE TABLE user (
    id INT(5) NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) DEFAULT NULL,
    age INT(3) DEFAULT NULL,
    code VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY ( id),
    KEY idx_name (name) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8

id 为主键，name 为普通索引。

  • 准备如下数据
INSERT INTO user (name, age, code) VALUES ('大一', 15, '10002');
INSERT INTO user (name, age, code) VALUES ('小二', 14, '10001');
INSERT INTO user (name, age, code) VALUES ('张三', 15, '10002');
INSERT INTO user (name, age, code) VALUES ('李四', 13, '10001');
INSERT INTO user (name, age, code) VALUES ('王五', 16, '10003');
INSERT INTO user (name, age, code) VALUES ('赵六', 15, '10001');
INSERT INTO user (name, age, code) VALUES ('刘七', 14, '10002');
INSERT INTO user (name, age, code) VALUES ('李四', 12, '10003');
INSERT INTO user (name, age, code) VALUES ('小李', 15, '10001');
  • 关闭自动提交

为了更好的演示，我们需要关闭自动提交。

select @@autocommit;
如果结果为 1 ，则修改为 0
set  @@autocommit = 0;

查询条件为主键

当 where 条件为主键 id 时。

事务 1 使用 select ...for update 查询 id = 10001 的数据，注意，它不提交事务：

begin;
select * from user where id = 10001 for update ;

事务 2 使用 update 语句更新 id = 10001 的这条记录：

begin ;
update user set name = '小熊' where id = 10001;

你会发现，这条 update 语句一直都在转圈圈，也是它在一直等待事务释放锁，如果事务 1 一种都不释放锁，事务 2 会报错：

这个报错说明了事务 2 等待锁的时间超时了，即 id = 10001 这条记录不锁住了但是一直都没有释放。

事务 3 使用 update 语句更新 id = 10002 的记录：

begin ;
update user set name = '小熊' where id = 10003;
commit;

执行成功。

select ...for update where 条件为主键时，锁住的是主键的对应那条数据，其他数据无影响。所以，为行锁！

查询条件为普通索引

当 where 条件为普通索引时。事务 4 使用 for update 查询 name = '大一'：

begin;
select * from user where name = '大一' for update ;

事务 5 更新 name = '大一'：

begin ;
update user set age = 11 where name = '大一';

执行结果与上面一样，报同样的错误：

由于 name = '大一' 只有一条记录，如果我们使用 name= '李四' 会是什么情况呢？

begin;
select * from user where name = '李四' for update ;

你会发现无论是 id = 10004 还是 id = 10008 都是报获取锁超时的错误，但是 id = 10005 可以执行成功：

select ...for update where 条件为普通索引，也是行锁，锁住的是查询出来的数据。

查询条件为主键范围

利用主键查询 id >= 10001 and id <= 10004 的数据：

begin;
select * from user where id >= 10001 and id <= 10004 for update ;

我们一次 update id 为10001、10002、10003、10004、10005 、1006，我们发现只有 10006 的数据执行成了，其余的全部都为获取锁超时：

select ...for update where 条件为主键范围时，为行锁。

查询条件明明是 id >= 10001 and id <= 10004 为什么会所 10005 呢？这个是 MySQL 的 "next-key lock" 机制，以后再来分析。

查询条件为普通字段

我们查询条件调整为 age = 11:

begin;
select * from user where age = 11 for update ;

这里你会发现无论是 update 哪条数据都会报获取锁失败。

select ...for update where 条件为普通字段，加的是表锁，而并非行锁。

查询条件为空

那如果不加查询条件呢？其实到这里小伙伴们应该知道，这里加的也是表锁。

查询结果为空

如果 where 条件查询出来的数据是空的呢？

begin;
select * from user where name = '999' for update ;
or
begin;
select * from user where id = 100010 for update ;

这里可以 update 语句都可以执行成功，但是这两条语句条件分别为主键和索引。如果我们切换为普通字段呢：

begin;
select * from user where age = 999 for update ;

这里锁住的依然是整个表。

最后

为小伙伴们总结下 select ...for update 加锁情况：

  1. 主键字段：行锁
  2. 普通索引：行锁
  3. 主键范围：行锁
  4. 普通字段：表锁
  5. 空条件：表所
  6. 条件结果为：使用索引或者主键，不加锁；使用普通字段，表锁

最后，在实际生产环境下，不建议使用 select ...for update，如果一定要使用则建议使用主键，严禁使用其他字段，锁全表不是开玩笑的，所不定服务就被你这个 for update 搞死了。

阅读全文