正确使用select…for update

在日常开发工程中,我们常遇到一些需求。

例如:

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

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

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

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

我建了一个简单的表,来尝试一下。

正常的行锁(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这行被加锁了,但是表中其他行没有被锁。

多行锁的情况

一、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是可以的。

危险的表锁(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是否能够查到数据,都会将整张表锁住。

不会锁表和锁行的情况

where条件在主键或索引上,但是没有查到结果,是不会锁表和锁行的。

最后的总结

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

知道了select … for update的原理,那么以后在使用的时候,务必要小心使用。

发表评论

电子邮件地址不会被公开。 必填项已用*标注