select...for update 到底是加了行锁，还是表锁？

原文：select...for update 到底是加了行锁，还是表锁？

前言

select...for update 在 MySQL 中，是一种悲观锁的用法，一般情况下，会锁住一行数据，但如果没有使用正确的话，也会把整张表锁住。

1. 要什么要用行锁？

假如现在有这样一种业务场景：用户 A 给你转账了 2000 元，用户 B 给你转账了 3000 元，而你的账户初始化金额是 1000 元。

在事务 1 中会执行下面这条 SQL：

update account set money=money+2000 
where id=123;

在事务 2 中执行下面这条 SQL：

update account set money=money+3000 
where id=123;

这两条 SQL 执行成功之后，你的 money 可能是：3000、4000、6000，这三种情况中的一种。

你之前的想法是，用户 A 和用户 B 总共给你转账 5000，最终你账户的钱应该是 6000 才对，3000 和 4000 是怎么来的？

假如事务 1 在执行 update 语句的过程中，事务 2 同时也在执行 update 语句。

事务 1 中查询到 money 是 1000，此外事务 2 也查询到 money 是 1000。

如果事务 1 先执行 update 语句，事务 2 后执行 update 语句，第一次 update 的 3000，会被后面的 4000 覆盖掉，最终结果为 4000。

如果事务 2 先执行 update 语句，事务 1 后执行 update 语句，第一次 update 的 4000，会被后面的 3000 覆盖掉，最终结果为 3000。

这两种情况都产生了严重的数据问题。

我们需要有某种机制，保证事务 1 和事务 2 要顺序执行，不要一起执行。

这就需要加锁了。

目前 MySQL 中使用比较多的有：表锁、行锁和间隙锁。

我们这个业务场景，非常时候使用行锁。

在事务 1 执行 update 语句的过程中，先要把某一行数据锁住，此时，其他的事务必须等待事务 1 执行完，提交了事务，才能获取那一行的数据。

在 MySQL 中是通过 select...for update 语句来实现的行锁的功能。

但如果你在实际工作中使用不正确，也容易把整张表锁住，严重影响性能。

select...where...for update 语句的用法是否正确，跟 where 条件中的参数有很大的关系。

我们一起看看下面几种情况。

---

假如 user 表现在有这样的数据库，数据库的版本是：8.0.21。

创建的索引如下：

其中 id 是主键字段，code 是唯一索引字段，name 是普通索引字段，其他的都是普通字段。

2. 主键

当 where 条件用的数据库主键时。

例如开启一个事务 1，在事务中更新 id=1 的用户的年龄：

begin;
select * from user where id=1 for update;
update user set age=22 where id=1;

where 条件中的 id 是数据库的主键，并且使用 for update 关键字，加了一个行锁，这个事务没有 commit。

此时，开启了另外一个事务 2，也更新 id=1 的用户的年龄：

begin;
update user set age=23 where id=1;
commit;

在执行事务 2 的 SQL 语句的过程中，会一直等待事务 1 释放锁：

如果事务 1 一直都不释放行锁，事务 2 最后会报下面这个异常：

如果此时开始一个事务 3，更新 id=2 的用户的年龄：

begin;
update user set age=23 where id=2;
commit;

执行结果如下：

由于事务 3 中更新的另外一行数据，因此可以执行成功。

说明使用 for update 关键字，锁住了主键 id=1 的那一行数据，对其他行的数据并没有影响。

3. 唯一索引

当 where 条件用的数据库唯一索引时。

开启一个事务 1，在事务中更新 code=101 的用户的年龄：

begin;
select * from user where code='101' for update;
update user set age=22 where code='101';

where 条件中的 code 是数据库的唯一索引，并且使用 for update 关键字，加了一个行锁，这个事务没有 commit。

此时，开启了另外一个事务 2，也更新 code=101 的用户的年龄：

begin;
update user set age=23 where code='101';
commit;

执行结果跟主键的情况是一样的。

4. 普通索引

当 where 条件用的数据库普通索引时。

开启一个事务 1，在事务中更新 name=周星驰的用户的年龄：

begin;
select * from user where name='周星驰' for update;
update user set age=22 where name='周星驰';

where 条件中的 name 是数据库的普通索引，并且使用 for update 关键字，加了一个行锁，这个事务没有 commit。

此时，开启了另外一个事务 2，也更新 name=周星驰的用户的年龄：

begin;
update user set age=23 where name='周星驰';
commit;

执行结果跟主键的情况也是一样的。

另：如果有多个 name 为周星驰的行，则这些行都会被锁住。

5. 主键范围

当 where 条件用的数据库主键范围时。

开启一个事务 1，在事务中更新 id in (1,2) 的用户的年龄：

begin;
select * from user where id in (1,2) for update;
update user set age=22 where id in (1,2);

where 条件中的 id 是数据库的主键范围，并且使用 for update 关键字，加了多个行锁，这个事务没有 commit。

此时，开启了另外一个事务 2，也更新 id=1 的用户的年龄：

begin;
update user set age=23 where id=1;
commit;

执行结果跟主键的情况也是一样的。

此时，开启了另外一个事务 2，也更新 id=2 的用户的年龄：

begin;
update user set age=23 where id=2;
commit;

执行结果跟主键的情况也是一样的。

6. 普通字段

当 where 条件用的数据库普通字段时。

该字段既不是主键，也不是索引。

开启一个事务 1，在事务中更新 age=22 的用户的年龄：

begin;
select * from user where age=22 for update;
update user set age=22 where age=22 ;

where 条件中的 age 是数据库的普通字段，并且使用 for update 关键字，加的是表锁，这个事务没有 commit。

此时，开启了另外一个事务 2，也更新 age=22 的用户的年龄：

begin;
update user set age=23 where age=22 ;
commit;

此时，执行事务 2 时，会一直阻塞等待事务 1 释放锁。

调整一下 SQL 条件，查询条件改成 age=23：

begin;
update user set age=23 where age=23 ;
commit;

此时，行事务 3 时，也会一直阻塞等待事务 1 释放锁。

也就是说，在 for update 语句中，使用普通字段作为查询条件时，加的是表锁，而并非行锁。

7. 空数据

当 where 条件查询的数据不存在时，会发生什么呢？

开启一个事务 1，在事务中更新 id=66 的用户的年龄：

begin;
select * from user where id=66 for update;
update user set age=22 where id=66 ;

这条数据是不存在的。

此时，开启了另外一个事务 2，也更新 id=66 的用户的年龄：

begin;
update user set age=23 where id=66 ;
commit;

执行结果：

执行成功了，说明这种情况没有加锁。

总结

最后给大家总结一下 select...for update 加锁的情况：

1. 主键字段：加行锁。
2. 唯一索引字段：加行锁。
3. 普通索引字段：加行锁。
4. 主键范围：加多个行锁。
5. 普通字段：加表锁。
6. 查询空数据：不加锁。

如果事务 1 加了行锁，一直没有释放锁，事务 2 操作相同行的数据时，会一直等待直到超时。

如果事务 1 加了表锁，一直没有释放锁，事务 2 不管操作的是哪一行数据，都会一直等待直到超时。

扩展

现在考虑一个问题：在 MySQL 中，事务 A 中使用 select...for update where id=1 锁住了，某一条数据，事务还没提交，此时，事务 B 中去用 select ... where id=1 查询那条数据，会阻塞等待吗？