select ... for update表锁还是行锁

1. 概要

Select 查询语句是不会枷锁的,但是Select … for update 除了有查询语句的作用,还是加锁,而且是悲观锁。

  • 使用索引: 行锁
  • 未使用索引:表锁

2. 建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--建表语句
CREATE TABLE t_user (
id INT ( 11 ) NOT NULL AUTO_INCREMENT,
name VARCHAR ( 255 ) DEFAULT NULL,
age INT ( 11 ) DEFAULT NULL,
addr VARCHAR ( 255 ) DEFAULT NULL,
PRIMARY KEY ( id ), -- 主键索引
KEY idx_age ( age ) USING BTREE -- 唯一索引
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

-- 插入数据
INSERT INTO t_user (name, age, addr) VALUES ('张三', 12, '上海');
INSERT INTO t_user (name, age, addr) VALUES ('李四', 31, '广东');
INSERT INTO t_user (name, age, addr) VALUES ('王五', 32, '南昌');
INSERT INTO t_user (name, age, addr) VALUES ('赵六', 24, '广东');

需要关闭自动提交,通过set @@autocommit = 0;设置为手动提交,0代表手动提交,1代表自动提交
关闭自动提交

3. 验证

3.1 场景一

使用主键id为1条件去查询,然后开启另一个事务对主键id为1对数据进行更新;

第一个事务使用select … for update查询,没有提交事务;

第二个事务,去更新主键id为1的数据,被阻塞了,长时间拿不到锁导致报错

结论:使用主键字段进行select … for update操作会锁住当前记录。

3.2 场景二

使用主键id=1为条件查询,开启另一个事务对主键id=2的数据进行更新

第一个事务使用select … for update查询,没有提交事务;

第二个事务对另一条id为2的数据更新,可以看到更新成功。

结论:使用主键字段进行select … for update操作会锁住当前记录,其他行数据可以进行正常的更新操作。

3.3 场景三

使用 唯一索引age12 查询,开启另一个事务对 唯一索引age=12 的数据进行更新

第一个事务使用select … for update查询,没有提交事务;

第二个事务对age=12的数据更新,被阻塞了。

结论:使用唯一索引字段进行select … for update操作会锁住当前记录,其他数据可以进行正常的更新操作。

3.4 场景四

使用普通字段 addr 进行操作

第一个事务使用select … for update查询,没有提交事务;

第二个事务进行任何数据更新操作,被阻塞了。

结论:使用非索引字段进行select … for update操作都会锁表,没有commit之前任何更新操作无法获取锁。