MySQL for update 到底是 row lock / table lock-一一网

前言

MySQL 在使用 for update 进行查询操作时，锁的是 row 还是 table 呢？答案见文末。

验证

MySQL for update 时使用索引检索数据的情况下，使用的是 row lock，而不使用索引检索数据的话，是 table lock，下面我们先来通过实验验证这个说法。

打开两个MySQL连接，将其中一个连接关闭自动提交事务。

-- 查询事务提交方式
select @@autocommit;
-- 关闭自动提交事务
set autocommit = 0;
复制代码

现在有一个 user 表，表中存储数据如下：

表中索引结构是只有主键为聚集索引。

1.1 主键索引检索数据

连接1

begin;

select * from user where id = "1" for update;
复制代码

连接2

update `user` set `name` = "feiyangyang" where id = "1";
复制代码

由于连接1未提交事务，所以 id=”1″ 的行记录被加了锁，导致连接2写数据失败。

update `user` set `name` = "feiyangyang" where id = "1"
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.403s
复制代码

再对 id=”2″ 的数据进行 update 操作

update `user` set `name` = "feiyangyang" where id = "2";
复制代码

update `user` set `name` = "feiyangyang" where id = "2"
> Affected rows: 1
> 时间: 0.002s
复制代码

上述实验测试的有数据的情况，如果无数据的情况呢？

连接1

begin;

select * from user where id = "4" for update;
复制代码

连接2

update `user` set `name` = "feiyangyang" where id = "4";
复制代码

发现没有数据的情况下没有锁

update `user` set `name` = "feiyangyang" where id = "4"
> Affected rows: 0
> 时间: 0s
复制代码

现在确定了 id=”1″的记录是被加了行锁（row lock），得出结论：

结论：根据主键索引检索数据时，row lock（有数据），no lock（无数据）

1.2 根据主键索引和非索引字段检索数据

连接1

begin;

select * from `user` where id = "1" and `name` = "test" for update;
复制代码

连接2

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "1";
复制代码

由于连接1未提交事务，数据行被锁，导致连接2 update 失败

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 51.451s
复制代码

此时，不提交连接1的事务，使用连接2查询其他行记录，执行成功。

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "2";
复制代码

再来看看无数据的情况

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "4";
复制代码

> Affected rows: 0
> 时间: 0s
复制代码

结论：根据主键索引和普通字段检索数据，row lock（有数据）, no lock（无数据）

1.3 根据非索引字段检索数据

连接1

begin;

select * from `user` where `name` = "test" for update;
复制代码

连接2

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "1";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.385s

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "2";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 51.376s
复制代码

连接2 中执行的第一个 sql 语句与连接1中操作中的是同一个记录行，由于连接1未提交事务，所以连接2 更新失败，但第二个 sql 语句与连接1并不是同一个记录行，依然被锁住。

再来看看无数据的情况：

连接2

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "4";
复制代码

> Affected rows: 0
> 时间: 0.001s
复制代码

结论：根据非索引字段检索数据，table lock（有数据） no lock（无数据）

1.4 根据普通索引检索数据

为 1.3 中的普通字段添加索引

create index idx_name on `user`(`name`)
复制代码

连接1

begin;

select * from `user` where `name` = "test" for update;
复制代码

连接2

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "1";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.374s

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "2";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.374s
复制代码

无数据的情况同上，略。

结论：根据普通索引检索，table lock（有数据），no lock（无数据）

1.5 根据 unique 索引检索数据

将 name 字段的索引修改为唯一索引

drop index idx_name on `user`;
create unique index idx_name on `user`(name);
复制代码

连接1

begin;

select * from `user` where `name` = "test" for update;
复制代码

连接2

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "1";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 时间: 50.374s

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "2";

> Affected rows: 1
> 时间: 0.05s
复制代码