MySQL select for...update

场景一：

出售某个商品，库存为100，每出售一件库存减1，当库存减为0后就无法购买该商品。

类似这种对数据表某一字段进行加减并且判断的功能，我们都会对这条记录进行加锁，避免出现高并发时字段数值出现超出临界值的情况。

为了不影响并发响应的效率，行级锁是最佳选择。select … for update 是我们常用的对行加锁的一种方式，在使用select … for update对某条记录查询后，这条记录就被当前连接锁住，其他连接只能读取这条记录的内容，无法对这条记录进行修改和删除。

那是不是只要使用了select … for update就对查询的记录行锁定了呢？并不是这样的，如果我们对select … for update使用不当，期待的行级锁会变成表锁，或者会锁住很多行记录，这对我们程序的执行效率带来相当大的影响。

for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。InnoDB默认是行级别的锁，当有明确指定的主键时候是行级锁，否则是表级别。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。我在这篇文章中对锁机制进行了比较详尽的介绍。

1.正常的行锁(row lock)
where条件明确指定主键

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where id=1 for update;
+----+--------+-------+---------+----------+
| id | title  | price | overage | category |
+----+--------+-------+---------+----------+
|  1 | iPhone |  5888 |     100 |        1 |
+----+--------+-------+---------+----------+
1 row in set (0.00 sec)

新建连接conn2，用conn2连接操作

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

id为1的商品无法查询，长时间未提交还会报错。

用conn2来查id为2的记录试试看

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where id=2 for update;
+----+--------+-------+---------+----------+
| id | title  | price | overage | category |
+----+--------+-------+---------+----------+
|  2 | xiaomi |   999 |     100 |        1 |
+----+--------+-------+---------+----------+
1 row in set (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

id为2的记录是可以操作的，说明id=1这行被加锁了，但是表中其他行没有被锁。

2.多行锁的情况
where条件明确指定索引
我在商品表的category字段建了索引，现在查询。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where category=1 for update;
+----+-----------+-------+---------+----------+
| id | title     | price | overage | category |
+----+-----------+-------+---------+----------+
|  1 | iPhone    |  5888 |     100 |        1 |
|  3 | smartisan |  1999 |     100 |        1 |
+----+-----------+-------+---------+----------+
2 rows in set (0.01 sec)

新建连接conn2，用conn2连接操作。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where category=1 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from t_goods where category=2 for update;
+----+--------+-------+---------+----------+
| id | title  | price | overage | category |
+----+--------+-------+---------+----------+
|  2 | xiaomi |   999 |      99 |        2 |
+----+--------+-------+---------+----------+
1 row in set (0.00 sec)

可以看到，当where条件指定在索引上时，也是行锁。但是，有一点需要注意，当行锁的条件在索引上时，所有满足此条件的行都会被锁定。
例如，我在上例中，category为1的记录都被锁定了，无论我是否有其他的where条件(price=5888等等)，都会把category=1的记录都锁定。

where条件指定的主键不明确

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where id>2 for update;
+----+-----------+-------+---------+----------+
| id | title     | price | overage | category |
+----+-----------+-------+---------+----------+
|  3 | smartisan |  1999 |     100 |        1 |
+----+-----------+-------+---------+----------+
1 row in set (0.00 sec)

新建连接conn2，用conn2连接操作。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where id<2 for update; +----+--------+-------+---------+----------+ | id | title | price | overage | category | +----+--------+-------+---------+----------+ | 1 | iPhone | 5888 | 100 | 1 | +----+--------+-------+---------+----------+ 1 row in set (0.00 sec) mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where id>1 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

可以看到，当where条件不是一条明确的记录，而是一个范围的时候，会将符合条件的记录全部上锁，不符合的不会上锁。

上例中，conn1将id>2的记录都上锁了，所以conn2操作id>1失败了，但是操作id<2是可以的。

3.危险的表锁(table lock)
where条件无主键

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where title="iPhone" for update;
+----+--------+-------+---------+----------+
| id | title  | price | overage | category |
+----+--------+-------+---------+----------+
|  1 | iPhone |  5888 |     100 |        1 |
+----+--------+-------+---------+----------+
1 row in set (0.00 sec)

新建连接conn2，用conn2连接操作。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from t_goods where title="xiaomi" for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我用两个连接做了不同条件的操作，但是conn2失败了，说明整张表都被锁住了。无论conn1是否能够查到数据，都会将整张表锁住。
4.不会锁表和锁行的情况
where条件在主键或索引上，但是没有查到结果，是不会锁表和锁行的。

在使用select … for update的时候，其实是非常危险的，一不小心就会将整表的数据，或者是表中大部分数据锁住。这对程序的性能造成了很大的影响，想操作其他行数据的请求只能等待，当锁被释放了后才可以操作。如果并发比较大，会造成大面积阻塞的情况，服务器会返回大量504错误。例如PHP，所有的php-fpm进程都阻塞在了等待数据锁的释放，那么新的请求就没有空闲的php-fpm进程去处理了。

场景二：

假设有A、B两个用户同时各购买一件 id=1 的商品，用户A获取到的库存量为 1000，用户B获取到的库存量也为 1000，用户A完成购买后修改该商品的库存量为 999，用户B完成购买后修改该商品的库存量为 999，此时库存量数据产生了不一致。

有两种解决方案：

悲观锁方案： 每次获取商品时，对该商品加排他锁。也就是在用户A获取获取 id=1 的商品信息时对该行记录加锁，期间其他用户阻塞等待访问该记录。悲观锁适合写入频繁的场景。

begin;

select * from goods where id = 1 for update;

update goods set stock = stock - 1 where id = 1;

commit;

乐观锁方案：每次获取商品时，不对该商品加锁。在更新数据的时候需要比较程序中的库存量与数据库中的库存量是否相等，如果相等则进行更新，反之程序重新获取库存量，再次进行比较，直到两个库存量的数值相等才进行数据更新。乐观锁适合读取频繁的场景。

#不加锁获取 id=1 的商品对象

select * from goods where id = 1

begin;

#更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新

update goods set stock = stock - 1 where id = 1 and stock = cur_stock;

commit;

如果我们需要设计一个商城系统，该选择以上的哪种方案呢？

查询商品的频率比下单支付的频次高，基于以上我可能会优先考虑第二种方案（当然还有其他的方案，这里只考虑以上两种方案）。