深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE

SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)

击水三千里

3423人浏览 · 2019-01-04 18:15:06

击水三千里 · 2019-01-04 18:15:06 发布

LOCK IN SHARE MODE

SELECT ... FOR UPDATE

for update锁表

for update的注意点

for update 和 for update nowait区别

应用场景

LOCK IN SHARE MODE

SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

SELECT ... FOR UPDATE

SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话(读取快照)，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)

for update锁表

InnoDB默认是行级别的锁，当有明确指定的主键时候，是行级锁。否则是表级别。

for update的注意点

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读(常规的select)

for update 和 for update nowait区别

（前者阻塞其他事务，后者拒绝其他事务）

for update锁住表或者锁住行，只允许当前事务进行操作（读写），其他事务被阻塞，直到当前事务提交或者回滚，被阻塞的事务自动执行

for update nowait 锁住表或者锁住行，只允许当前事务进行操作（读写），其他事务被拒绝，事务占据的statement连接也会被断开

应用场景

在我看来，SELECT ... LOCK IN SHARE MODE的应用场景适合于两张表存在关系时的写操作，拿mysql官方文档的例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id) values (100)就ok了。

但是如果是同一张表的应用场景，举个例子，电商系统中计算一种商品的剩余数量，在产生订单之前需要确认商品数量>=1,产生订单之后应该将商品数量减1。
1 select amount from product where product_name='XX';
2 update product set amount=amount-1 where product_name='XX';

显然1的做法是是有问题，因为如果1查询出amount为1，但是这时正好其他session也买了该商品并产生了订单，那么amount就变成了0，那么这时第二步再执行就有问题。

那么采用lock in share mode可行吗，也是不合理的，因为两个session同时锁定该行记录时，这时两个session再update时必然会产生死锁导致事务回滚。以下是操作范例(按时间顺序)

session1(开启事务)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)


session2(开启事务,锁定了相同的行)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)


session1(这时session1在update时就会引起锁等待，等待session2)
mysql> update test_jjj set name='jjj1' where name='jjj';


session2(这时session2同样update，引起锁等待，等待session1，接着检测到死锁，回滚session2，注意
执行时间不要超过session1的锁等待超时检测时间，即不要超过innodb_lock_wait_timeout设置的值)
mysql> update test_jjj set name='jjj1' where name='jjj';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


session1(此时session1执行完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 1 row affected (29.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

备注：可以通过以下三个命令查看事务的状态

1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

杀死事务进程id（就是上面命令的trx_mysql_thread_id列）

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

通过该案例可知lock in share mode的方式在这个场景中不适用，我们需要使用for update的方式直接加X锁，从而短暂地阻塞session2的select...for update操作;以下是操作范例

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (0.00 sec)


session2(此时session2处于锁等待状态，得不到结果)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj for update;


session1(这时session1 update之后提交，可完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


session2(session1提交之后session2刚才的查询结果就出来了，也就可以再次update往下执行了)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)


mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.00 sec)