MySQL数据库的共享锁和排他锁

MySQL数据库的锁，按照作用范围划分为： 行级锁、页级锁和表级锁，行级锁是锁定粒度最细的一种锁，能大大减少数据库操作的冲突。行级锁又分为共享锁和排他锁两种，本文将详细介绍共享锁和排他锁的概念、使用方式及注意事项。

共享锁（Share Lock）
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法

SELECT … LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，MySQL 就会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

使用场景
　　SELECT … LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他人可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的过程执行完成(完成的情况有：事务的提交，事务的回滚，否则直接锁等待超时)。
　　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)就不会存在这种问题了。

排他锁（Exclusive Lock）
排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法

SELECT … FOR UPDATE;

在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

使用场景：

订单的商品数量
　　　　如果是同一张表的应用场景，举个例子，电商系统中计算一种商品的剩余数量，在产生订单之前需要确认商品数量>=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时必然会产生死锁导致事务回滚。以下是操作范例(按时间顺序)。
　　　　　
　　　　通过对比，lock in share mode适用于两张表存在业务关系时的一致性要求，for update适用于操作同一张表时的一致性要求。