浅析mysql共享锁S与排他锁X

带你认识mysql锁机制

1. InnoDB存储引擎中锁的类型

（1）共享锁 （S Lock），允许事务读一行数据

（2）排它锁 （X Lock），允许事务删除或者更新一行数据

以上两种类型的锁的都是行锁，如果事务T1已经对行r的成功加上共享锁S，那么事务T2任然可以对行r的成功加上共享锁S，

因为读取数据，而不是更新数据，这种情况称为锁兼容，但是如果事务T1对行r成功加上排它锁X，那么此时其他事务不能对

行r加任何锁，必须等待，直到T1事务在行r上的X锁释放了才可以，这种情况称为锁不兼容。

共享锁S与S兼容，排它锁X与其他锁都不兼容。

2. 锁的使用

加共享锁S

SELECT * FROM table_name WHERE 1=1 LOCK IN SHARE MODE

加排它锁X

SELECT * FROM table_name WHERE 1=1 FOR UPDATE

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会 加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁 来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在如上例子中，看起来会话A只给id=2的一行加了排他锁，但会话B在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。

3. mysql 行锁的三种算法

InnoDB存储引擎有三种锁算法

1、Record Lock 单个行记录上的锁

2、Gap Lock 间隙锁，锁定一个范围，但不包含记录本身

3、Next-Key Lock :Gap Lock + Record Lock 锁定一个范围，并锁定本身

Record Lock 总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时候会使用隐式的主键来进行锁定。

Next-Key Lock 是结合了Gap Lock 和 Record Lock 的一种锁定算法，InnoDB存储引擎对于行的查询都是采用这种锁定算法，列如一个索引有10，11，13 和 20四个值，那么该索引可能会被锁定的范围 （-∞，10], (10,11], (11,13], (13,20], (20,+∞]

这种算法的目的，正式为了解决幻读的问题（脏读、不可重复读、幻读），利用这个技术，锁定的不是单个值，而是一个范围。（下一专题专门来说说mysql隔离级别）

当然，在一些情况下，InnoDB存储引擎会对Next-Key Lock进行优化，毕竟锁住很多段范围，对性能影响还是比较大的，所以，当查询的索引含有唯一索引属性，即，唯一索引，Next-Key Lock会优化降级为Record Lock，仅锁住索引本身，而不是范围。

表名：t
|uid| name |
|-1-| a |
| 2 | b |
| 5 | c |

上表 t 中uid设置为唯一索引，

表中uids索引字段共有三个值，1，2，5，上面会话A首先对 uid = 5进行了X锁定，按前面的解释，此时InnoDB存储引擎应该会对行的查询采用Next-Key Lock锁定算法，锁定几段范围，但是由于uid我们设置的是唯一索引，所有会话B可以成功执行，原因就是，对锁进行了优化降级成为Record Lock，从而可以提高表的并发性能。

**

InnoDB存储引擎对Next-Key Lock的优化降级只是针对查询列是唯一索引的情况下，若是普通索引（辅助索引）则情况完全不同，我们来创建一张表

**

create table yyy (a INT,b INT,PRIMARY(a),KEY(b));
a  b
1  1
3  1
5  3
7  6

可以看出表yyy中 a是主键索引，b是辅助索引，若开启会话A执行下列SQL,

select * from yyy where b=3 for update;

根据上面的介绍，此时使用的是传统的 Next-Key Lock 算法加锁，并且由于两个索引，InnoDB会分别进行锁定，对于唯一索引或者叫聚集索引，会采用Record Lock锁定，对列a等于5的索引进行行锁锁定，对于辅助索引或者叫普通索引b等于3列，会锁定范围（1，3），需要特别注意的，InnoDB 存储引擎还会对辅助索引下一个键值加上 gap lock,即（Gap Lock + Record Lock），还有一个辅助索引范围为（3，6）的锁。 若此时，新开会话B中运行下面SQL语句，

(1) select * from yyy where a=5 in share mode;

分析：阻塞，因为a=5行已经被会话A加上排它锁：X（排它锁与共享锁不兼容）。

(2) insert into yyy （a,b）values(4,2);

分析：主键列插入 4，没问题，但是辅助索引列插入2的时候，根据上面分析，2在锁定范围（1，3）中，因此，会被阻塞等待。

(3) insert into yyy （a,b）values(6,5);

分析：主键列插入6，没有被锁定，辅助索引列5也不在锁定范围（1，3）之间，但是别忘了，另个辅助索引gap lock 范围（3，6），所以该sql还是被阻塞。