在日常开发工程中,我们常遇到一些需求。
例如:
出售某个商品,库存为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的原理,那么以后在使用的时候,务必要小心使用。