MySQL 事务和索引

本文记录下对事务隔离的理解。

事务

存储引擎InnoDB支持事务行级锁

并发问题

当两个事务同时进行的时候,两者之间互相不知道对方的存在,对自身所处的环境过分乐观,从而没有对操作的数据做一定的保护处理,导致出现以下问题:

更新丢失:两个事务同时读取某一数据,但不同时进行更新操作,导致数据被覆盖的问题(重点在于覆盖更新)。

脏读:一个事务修改了数据但是没有提交,另外一个事务会读取到这些未被提交的数据(重点在于读取未提交的数据)。

不可重复读:一个事务在执行的过程中由于其他事务提交了新数据导致前后两次读取的内容不同(重点在于两次读取内容的不同)。

幻读:一个事务由于其他事务同时的插入或删除造成按照相同条件的两次检索结果不同(重点在于读取条数的不同)。

隔离级别

InnoDB支持四种隔离级别:

  • 未提交读(read uncommited)
  • 提交读(read commited)
  • 可重复读(repeatable read)
  • 串行化(serializable)

默认隔离级别为可重复读

四个隔离级别逐渐增强。

更新丢失:可以通过CAS(对数据添加对应的版本)来实现数据的更新,当发现数据版本发生变化,则可触发回滚操作。

提交读及以上隔离级别设定,一个事务只能读取另一个事务已经提交的数据,就避免了上面的脏读现象。

提交读是通过只读取提交后的数据来避免脏读问题。

InnoDB可重复读不保证避免幻读,需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-keylocks,

1
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

next-key lock

InnoDB支持的锁的类型包括:共享锁S排他锁X意向共享IS意向排他IX

为了方便检查表级锁和行级锁之间的冲突,引入了意向锁。
意向锁是表锁,仅仅代表要对某行记录进行操作,只有在对行加锁时,才会判断是否冲突。

InnoDB中行锁的算法包括:

  • record lock:当个行记录上的锁;
  • gap lock:间隙锁,锁定一个范围(不包括记录本身),为了避免幻读现象(保证同一事物两次读取内容相同);
  • next-key lock:行锁和间隙锁的组合,锁定一个范围,并且锁定记录本身,解决幻读问题。

特殊情况:唯一索引上不采用Next-Key Lock,而是降级为行级锁, 只锁一行。

间隙锁的区间是右闭区间,例如[1, 5)。

具体例子可以查看

普通索引

对于普通的索引(a)来说,InnoDB的行查询都采用了next-key lock算法,锁定不是单个值,而是一个范围(间隙)。

例如,如果表中已有[1, 3, 5, 8, 11],那么所有的间隙如下,

1
(-无穷, 1] (1, 3] (3, 5] (5, 8] (8, 11] (11, +无穷]

当执行三个事务如下,

1
2
3
4
5
6
7
8
事务A:会锁住8的上下间隙,(5, 8] (8, 11]
select * from t where a = 8 for update;

事务B: 正常运行
insert into t values(2);

事务C:由于该范围被锁住,阻塞
insert into t values(11);

唯一索引

对于唯一索引,next-key lock会做优化,将锁降级为record lock,也就是行锁,仅仅锁定行本身而不是范围。

因此,如果a为唯一索引(primary or unique)时,事务B和事务C将正常运行。

对于主键或唯一索引来说,如果锁定的是不存在的值,也会产品间隙锁。

索引实现

Btree(B+Tree)

参考:http://blog.codinglabs.org/articles/theory-of-mysql-index.html