mysql加锁分析（一）等值查询

mysql加锁分析（一）等值查询

等值查询的加锁分析相对简单，这篇先从等值查询开始。

前言

先确认本篇内容，分析在RC（读已提交）和RR（可重复读）下关于等值查询的加锁分析。关于范围查询待下篇讲起。

mysql默认的隔离级别是RR。隔离级别的查询及调整如下：

select @@global.transaction_isolation; ## 查询全局事务隔离级别

select @@session.transaction_isolation; ## 查询会话事务隔离级别

set session(global) transaction isolatin level repeatable read; 设置隔离级别为可重复读

set session(global) transaction isolatin level read committed; 设置隔离级别为读已提交

为分析方便，假设有如下表

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`) USING BTREE,
  KEY `d` (`d`) USING BTREE
) ENGINE=InnoDB


insert into t values(1,2,3,4),(10,11,12,13),(20,21,22,23),(30,31,32,33),(40,41,42,43),(50,51,52,53);

t表有4个字段，id为主键，c为唯一索引，d为普通索引，e为普通字段无索引

在正文开始前，首先普及一些基本知识，这对接下来的加锁分析至关重要。

关于当前读和快照读

有如下语句

select * from t where id = 10 ; # 快照读，不加锁


select * from t where id = 10 for update; # 当前读，加X锁（写锁）


select * from t where id = 10 lock in share mode; # 当前读，加S锁（读锁）

快照读不会加锁，它的实现基于mysql的MVCC(多版本并发控制，Multi-Version Concurrency Control)，简单来说，数据库的每次变更都有记录，每次记录都有一个事务id标识，每个事务都只能看到该事务开启时的数据库视图，它之后的数据变更(事务id递增)是看不到的。

	trans1	trans2	trans3
t1	a
t2	a	b(将a改为b)
t3	a	b	c(把b改为c)

当前读总是读取最新已提交的事务的数据。并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。

关于间隙锁

在可重复读级别下，mysql为保证可重读语义引入了Gap lock(间隙锁)。如id=10和id=20之间的(10,20)开区间即可算一个间隙，在此间隙加锁即为间隙锁。

看下面的例子

	session1	session2
t1	begin
t2	select id from t where d>5 and d<15 for update
t3		insert into t values(18,19,20,21) （被阻塞）
t4	select id from t where d>5 and d<15 for update(两次重复读)
t5	commit

可以发现session2的插入操作被阻塞住了，不仅插入d=20会被阻塞，插入d在（12，22）之间的数据都会被阻塞，这也说明了mysql在这种情况下其实是加了间隙锁的。

那为什么会有间隙锁也就明了了，如果不加间隙锁，两次当前读到的数据就会不一样，这样会影响可重复读的语义。你可以在读提交隔离级别下尝试上面的例子，session2就不会阻塞。间隙锁只有在可重复读及以上（串行化）级别才会有。

RR下的等值查询

所以在可重复读隔离下，加锁逻辑还要考虑间隙锁的影响。考虑的原则主要是如果不加这个锁，是否会影响可重复读的语义，影响就要加锁。另外，还有mysql本身实现加锁的逻辑，有的时候某些值可以不加锁的，但由于mysql实现影响，它就是加了（笑哭），这个没有办法，需要单独注意。

主键的等值查询

关于主键的加锁分析相对简单，不需要考回表操作等，即加锁也只会在主键索引加，不会涉及多个索引树。

1)快照读

select * from t where id = 10;

快照读都不会加锁，后面就省略了。

2. 可查询到值

select * from t where id = 10 for update / lock in share mode;

for update / lock in share mode 都会加锁，一个写锁一个读锁，由于id=10有这条记录，又因为是主键，所以只给id=10这行加行锁。

3. 查询不到值

select * from t where id = 13 for update / lock in share mode;

id=13这条记录不存在，这就需要考虑间隙锁了。

why？

因为如果不把 id（10,20）的间隙锁住，别的事务可能会插入id=13这条记录，导致不可重读，不行。

那只把id=13锁住不就行了？哦，没有那一条记录。可这代价也太大了吧。

确实，间隙锁的代价很大，可为了保证语义正确，也不得不这样。总不能每次当前读就记录下查询的值，这在实现也太…这里演示的是等值查询，更多的情况是范围查询，那锁住一个间隙可以理解了。

所以这里加锁范围是 (10,20)开区间。

唯一索引的等值查询

唯一索引和主键索引加锁类似，唯一不同的是唯一索引加锁后，给需要回表的主键索引也要加锁。不过从现象看，两者是一致的。

1. 可查询到值

select * from t where c = 11 for update / lock in share mode;

由于c=11有这条记录，又因为是唯一索引，所以给c=11这行加行锁。又由于是select *，需要回表，在回表中找到了id=10这条记录，也加行锁。下面验证下。

	session1	session2	session3	session4
t1	begin
t2	select * from t where c = 11 for update
t3		update t set c=16 where c=11 （被阻塞）
t4			select id from t where id = 10 lock in share mode;（被阻塞）
t5				select d from t where d=12 for update; (被阻塞)
t6	commit

加锁范围为c=11这一行，行锁。

session3表明主键索引树c=11这行被锁了，
session4表明d索引树d=12这行也被锁了。（why?）

2. 可查询到值另一种情况

select c from t where c = 11 for update / lock in share mode;

和上面情况不同的是这里只返回c值，看看索引覆盖对加锁的影响。

	session1	session2	session3	session4
t1	begin
t2	select c from t where c = 11 for update
t3		update t set c=16 where c=11 （被阻塞）
t4			select id from t where id = 10 lock in share mode;（被阻塞）
t5				select d from t where d=12 for update; (被阻塞)
t6	commit

加锁范围为c=11这一行，行锁。

session3表明主键索引树c=11这行被锁了，
session4表明d索引树d=12这行也被锁了。（why?）

2. 查询不到值

select * from t where c = 15 for update / lock in share mode;

和主键索引类似，加的是c在（11，21）之间的间隙锁。

普通索引等值查询

普通索引由于不唯一，即使可以查到值，也可能会有间隙锁。

1）可查询到值

select * from t where d = 12 for update / lock in share mode;

总结

查询过程中，访问到的对象才会加锁。

还有就是，在 insert 或update 中，涉及到哪个索引的，则那个索引也会加锁。

insert 插入语句必然涉及索引索引，所以都会加锁。

udpate t set c=xx ,d =yy where id =zz

此时会涉及主键索引，c、d两个索引，这3个索引树都会加锁（在所涉及到的行）。