本次主要是测试一下MySQL的select ... for update这种悲观锁的表锁和行锁,版本:MySQL5.7.20,存储引擎 InnoDB 操作系统:Windows10 64位,工具:navicat for mysql 11
表名称:test_order,主键是id;
要使用悲观锁,我们必须关闭mysql数据库的自动提交属性,因为MySQL默认使用autocommit模式,也就是说,当你执行一个更新操作后,MySQL会立刻将结果进行提交,所以我们需要手动设置autocommit=0;
1. 对主键进行加锁:
navicat起一个命令行,设置autocommit=0,并且对id=1的记录进行加锁;
mysql> select * from test_order; +----+--------+--------+----------+ | id | status | name | goods_id | +----+--------+--------+----------+ | 1 | 1 | 茅台 | 123 | | 2 | 2 | 五粮液 | 345 | | 3 | 3 | 剑南春 | 567 | +----+--------+--------+----------+ 3 rows in set mysql> set autocommit = 0; Query OK, 0 rows affected mysql> select * from test_order where id = 1 for update; +----+--------+------+----------+ | id | status | name | goods_id | +----+--------+------+----------+ | 1 | 1 | 茅台 | 123 | +----+--------+------+----------+ 1 row in set
navicat再起一个命令行
mysql> select * from test_order where id = 1; +----+--------+------+----------+ | id | status | name | goods_id | +----+--------+------+----------+ | 1 | 1 | 茅台 | 123 | +----+--------+------+----------+ 1 row in set mysql> select * from test_order where id = 1 for update; 1205 - Lock wait timeout exceeded; try restarting transaction mysql> select * from test_order where id = 2 for update; +----+--------+--------+----------+ | id | status | name | goods_id | +----+--------+--------+----------+ | 2 | 2 | 五粮液 | 345 | +----+--------+--------+----------+ 1 row in set mysql>
结论:对主键进行锁定时,对常规的select操作无影响,再次锁id=1的记录时,发现已经被锁,而查询其他的数据则没问题;
2. 对非主键非索引加锁
mysql> select * from test_order where status = 1 for update; +----+--------+------+----------+ | id | status | name | goods_id | +----+--------+------+----------+ | 1 | 1 | 茅台 | 123 | +----+--------+------+----------+ 1 row in set
另起一个命令行
mysql> select * from test_order where id = 1 for update; 1205 - Lock wait timeout exceeded; try restarting transaction mysql> select * from test_order where id = 2 for update; 1205 - Lock wait timeout exceeded; try restarting transaction
结论:对非主键非索引加锁时,锁的是整张表;
3. 对非主键索引加锁
我们对status这个字段加上索引,然后再测试:
mysql> set autocommit = 0; Query OK, 0 rows affected mysql> select * from test_order where status = 1 for update; +----+--------+------+----------+ | id | status | name | goods_id | +----+--------+------+----------+ | 1 | 1 | 茅台 | 123 | +----+--------+------+----------+ 1 row in set
再起一个命令行
mysql> select * from test_order where id = 1 for update; 1205 - Lock wait timeout exceeded; try restarting transaction mysql> select * from test_order where id = 2 for update; +----+--------+--------+----------+ | id | status | name | goods_id | +----+--------+--------+----------+ | 2 | 2 | 五粮液 | 345 | +----+--------+--------+----------+ 1 row in set
结论:对于索引字段来说,使用的是行锁;
总结:MySQL的select ... for update这种悲观锁,有两种锁定级别:行锁(Row Lock ),表锁(Table Lock)。在对表进行进行加锁的时候,由于主键本身就是索引,所以,对包含索引的字段加的是行锁,而对于非索引字段,加的是表锁;Mysql InnoDB存储引擎默认的是Row Lock,所以查询的时候尽量使用索引。