MySQL中“for update“、“lock in share mode“解析

前言

为了方便大家理解，这里把共享锁、排它锁、意向共享锁、意向排它锁的概念说明一下：

• 共享锁：又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能改。
• 排它锁：又称为写锁，简称X锁，顾名思义，排它锁就是不能与其它锁并存，如一个事务获取了一个数据行的排它锁，其它事务就不能再获得该行的其它锁，包括共享锁和排它锁，但是获取此数据行排它锁的事务可以对数据进行读取和修改。
• 意向共享锁：表示事务有意向对表中的某些行加共享锁
• 意向排它锁：表示事务有意向对表中的某些行加排它锁

其中：

意向排它锁(IX)：select xxx from xxx where xxx for update;
意向共享锁(IS)：select xxx from xxx where xxx lock in share mode; 

以上二者属于显示加锁

TIP：关于MySQL中锁的相关问题可以参考此篇博客：传送门
MySQL中数据在被修改的时候可能会存在并发问题，为了保证数据的一致性，可以在数据处理的过程中将数据锁定。（只有在数据库层提供的锁机制才能真正保证数据访问的排它性，否则，即使在应用层中实现了加锁机制，也无法保证外部系统不会修改数据）

for update

使用场景举例
商品goods表中一字段status,status为1代表商品未被下单，status为2代表商品已经被下单，那么我们对某个商品下单时必须确保该商品status为1。假设商品的id为1，如果不采用锁，那么操作方式如下：

//1.查询出商品信息
select  status  from  t_goods  where  id=1;
//2.根据商品信息生成订单
insert  into  t_orders (id,goods_id) values ( null ,1);
//3.修改商品status为2
update t_goods  set  status=2 where id=1;

上面这种情况在高并发访问的情况下很可能会出现问题。前面已经提到，只有当goods表中的status字段为1时才能对该商品下单，上面第一步操作中，查询出来的商品的status为1，于是开始下单。但是当我们执行第三步update操作的时候，由于普通查询"select …from…"没有任何锁机制，"id=1"这一行的数据有可能被其它事务先一步下单把status字段修改为2了，但是我们并不知道数据被修改了。这样就可能造成同一个商品被下单2次，使得数据不一致。所以这种方式是不安全的。

使用排它锁来实现
在上面的场景中，商品信息从查询出来到修改，中间有一个处理订单的过程，使用排它锁的原理就是，当我们在查询goods表中"id=1"这一行数据时，就把此行数据锁定，直到我们修改完毕后再解锁。那么这个过程中，因为"id=1"这行的数据被加了排它锁，其它事务也就无法修改此行数据了。要使用排它锁，我们必须关闭MySQL中自动提交的属性：

set  autocommit=0;　　
//设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
//0.开始事务
begin; 
//1.查询出商品信息
select  status  from  t_goods  where  id=1  for  update;
//2.根据商品信息生成订单
insert  into  t_orders (id,goods_id) values ( null ,1);
//3.修改商品status为2
update t_goods  set  status=2 where id=1;
//4.提交事务
commit;

ps:不将自动提交属性关闭的话，事务就会自动提交，这样就无法按照自己的意愿在一段时间内锁住数据行。

上面第一步我们执行了查询操作语句：“select … from … for update”，此方式与普通的"select … from …"查询语句不同，“for update"方式的查询会将查询到的数据加上排它锁，这样被查询到的数据就不能不加上其它锁了。而对于"update”、“delete”、"insert"语句，InnoDB存储引擎会自动给涉及的数据加排它锁，所以使用"for update"方式的查询后，在"for update"排它锁释放之前，update语句无法更新status字段。这样我们就可以保证当前数据不会被其它事务修改。

TIP：因为使用"select … from … for update"是给查询的结果数据加上排它锁，所以无法再使用"select … from … for update"大方式查询相同的数据了。但是对于普通查询"select … from … "来说依然可以查询加上排它锁的数据，因为普通查询"select … from … "没有任何锁机制。

测试
上面我们提到，使用"select … from … for update"会把数据给锁住，不过我们需要注意一些锁的级别，MySQL中InnoDB存储引擎默认Row-Level Lock，所以只有明确地指出主键，MySQL才会执行Row lock(只锁住被选取的数据)，否则MySQL将会执行Table Lock(将整个数据表给锁住)。

测试环境准备：
goods表包括id、status、name三个字段，id为主键
 CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `status` enum('1','2') NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

goods表中的数据行如下：
db01 [test]>select * from goods;
+----+--------+------------------+
| id | status | name             |
+----+--------+------------------+
|  1 | 1      | java编程思想     |
|  2 | 2      | 高性能MySQL      |
|  3 | 2      | java并发实战     |
+----+--------+------------------+
3 rows in set (0.00 sec)

• 情况一：明确指定主键，并且有此数据时，MySQL执行行锁
  事务1使用"for update"查询id=1的数据行
  
  事务2中使用普通查询"select … from …"可以查询到id=1数据行的数据，使用"for update"查询方式查询id=1行的数据时就会被阻塞。且如果事务1长时间不提交，事务2中还会报错。
  
• 情况二：明确指定主键，若查无此数据时，MySQL就不会加锁
  事务1中查询结果为空
  
  事务2中查询结果为空，且查询无阻塞，说明事务1没有对id=4这行数据锁定
  
• 无主键时，MySQL执行table lock
  事务1查询status=1的数据行结果
  
  事务2查询status=2的结果，但是查询阻塞，说明事务1把整张表都锁住了
  
• 主键不明确时，MySQL执行table lock
  事务1查询正常
  
  事务2查询被阻塞，说明事务1把整张表都给锁住了
  
• 明确指定索引时，并且有此数据，MySQL执行row lock
• 明确指定索引时，若查无此数据，MySQL不加锁

lock in share mode

"select … from …lock in share mode"是给查询的结果集加上共享锁，数据被一事务加上共享锁后，此数据只能被其它事务读取，不能被修改，除非其它事务对此数据施加的也是共享锁或意向共享锁。

测试
事务1给id=1行数据加上共享锁

事务2使用"update"更新id=1行的数据，可以观察到事务被阻塞了。因为"update"语句执行时，InnoDB存储引擎会自动给涉及的数据加上排它锁，而共享锁只允许兼容共享锁和意向共享锁，所以事务2被阻塞。

注意死锁
相对于"for updata"给涉及的数据加上的是排它锁，"lock in share mode"给涉及的数据加上共享锁可能会导致死锁的产生，因为相同的数据可以被多个事务加上共享锁或意向共享锁。

事务1给id=1行数据加上共享锁

db01[test]>begin;
db01[test]>select * from goods where id=1 lock in share mode;

事务2给id=1行数据加上共享锁

db01[test]>begin;
db01[test]>select * from goods where id=1 lock in share mode;

这个时候两个事务同时持有id=1这行数据的共享锁，这个时候我们分别在事务1和事务2中执行"update"操作：

事务1：
db01[test]>update goods set name='Go语言学习' where id=1;
事务2：
db01[test]>update goods set name='Go语言学习' where id=1;

这时就会发现，事务1和事务2都卡住了，事务1的"update"语句等待着事务提交，而事务2也在等待着事务1提交，从而陷入了死锁的状态。这个时候MySQL会检测到发生了死锁，因为由于事务2执行"update"语句后发生了死锁，所以系统会中断此语句的执行，并退出事务2，然后新开一个事务。
这个时候，事务1可以更新成功了。

到这里可能有人会问了，"lock in share mode"感觉没啥用啊，而且还会有死锁。

用"select … lock in share mode"获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。
但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用"select… for update"方式获得排他锁。

总结：“for update"和"lock in share mode"这两种加锁的方式，在查询结果为空时，锁不会起作用。同时，无论在使用"for update"或"lock in share mode”，都应尽快释放锁。

参考文章：https://blog.csdn.net/qq_35779969/article/details/80074998
https://www.jb51.net/article/170025.htm
http://www.hollischuang.com/archives/923