InnoDB引擎事务总结

什么是局部性原理？

数据预读的思路是：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，以便未来减少磁盘IO

局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO

B树

B树，如上图，它的特点是：
(1)不再是二叉搜索，而是m叉搜索；
(2)叶子节点，非叶子节点，都存储数据；
(3)中序遍历，可以获得所有节点；
非根节点包含的关键字个数j满足，(┌m/2┐)-1 <= j <= m-1，节点分裂时要满足这个条件

B+树

(1)非叶子节点不再存储数据，数据只存储在同一层的叶子节点上；
(2)叶子之间，增加了链表，获取所有节点，不再需要中序遍历；
数据库的索引最常用B+树：
(1)很适合磁盘存储，能够充分利用局部性原理，磁盘预读。
比如：每个节点是1页的大小,4k，key占8个字节，j = 512, 根据(┌m/2┐)1 <= j <= m-1, m=j + 1,即513
(2)很低的树高度，能够存储大量数据；
(3)索引本身占用的内存很小；
(4)能够很好的支持单点查询，范围查询，有序性查询；

InnoDB的索引

InnoDB的索引有两类索引，聚集索引(Clustered Index)与普通索引(Secondary Index)。
InnoDB的每一个表都会有聚集索引：
(1)如果表定义了PK，则PK就是聚集索引；
(2)如果表没有定义PK，则第一个非空unique列是聚集索引；
(3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
索引的结构是B+树，这里不展开B+树的细节，说几个结论：
(1)在索引结构中，非叶子节点存储key，叶子节点存储value；
(2)聚集索引，叶子节点存储行记录(row)；
(3)普通索引，叶子节点存储了PK的值；
InnoDB的普通索引，实际上会扫描两遍：
第一遍，由普通索引找到PK；
第二遍，由PK找到行记录；

隔离性

事务ACID特性，其中I代表隔离性(Isolation)
隔离性是指多个并发事务之间要相互隔离。
并发事务之间相互干扰，可能导致事务出现读脏，不可重复度，幻读等问题

不隔离可能导致的问题

假设有InnoDB表：
t(id PK, name);
表中有三条记录：
1, shenjian
2, zhangsan
3, lisi

case 1
事务A，先执行，处于未提交的状态：
insert into t values(4, wangwu);

事务B，后执行，也未提交：
select * from t;

如果事务B能够读取到(4, wangwu)这条记录，事务A就对事务B产生了影响，这个影响叫做“读脏”，读到了未提交事务操作的记录。

case 2
事务A，先执行：
select * from t where id=1;

结果集为：
1, shenjian

事务B，后执行，并且提交：
update t set name=xxoo where id=1;
commit;

事务A，再次执行相同的查询：
select * from t where id=1;

结果集为：
1, xxoo
这次是已提交事务B对事务A产生的影响，这个影响叫做“不可重复读”，一个事务内相同的查询，得到了不同的结果。

case 3
事务A，先执行：
select * from t where id>3;
结果集为：
NULL

事务B，后执行，并且提交：
insert into t values(4, wangwu);
commit;

事务A，首次查询了id>3的结果为NULL，于是想插入一条为4的记录：
insert into t values(4, xxoo);

结果集为：
Error : duplicate key!

事务A的内心OS是：你TM在逗我，查了id>3为空集，insert id=4告诉我PK冲突？

这次是已提交事务B对事务A产生的影响，这个影响叫做“幻读”。
可以看到，并发的事务可能导致其他事务：

读脏
不可重复读
幻读

事务的隔离级别：

按照SQL92标准，InnoDB实现了四种不同事务的隔离级别：

读未提交(Read Uncommitted)
读提交(Read Committed, RC)
可重复读(Repeated Read, RR)
串行化(Serializable)
不同事务的隔离级别，实际上是一致性与并发性的一个权衡与折衷。

隔离级别实现

InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别。

(1)读未提交：select不加锁，可能出现读脏；

(2)读提交(RC)：普通select快照读，锁select /update /delete 会使用记录锁，可能出现不可重复读；

(3)可重复读(RR)：普通select快照读，锁select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录；

(4)串行化：select隐式转化为select ... in share mode，会被update与delete互斥；

InnoDB默认的隔离级别是RR，用得最多的隔离级别是RC

mysql的锁

默认的事务隔离级别为可重复读(Repeated Read, RR)

记录锁(Record Locks)

记录锁，它封锁索引记录，例如：
select * from t where id=1 for update;
它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。
需要说明的是：
select * from t where id=1;
则是快照读(SnapShot Read)，它并不加锁

间隙锁(Gap Locks)

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

依然是上面的例子，InnoDB，RR：
t(id PK, name KEY, sex, flag);
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

这个SQL语句

select * from t 
    where id between 8 and 15 
    for update;

会封锁区间，以阻止其他事务id=10的记录插入。
为什么要阻止id=10的记录插入？
如果能够插入成功，头一个事务执行相同的SQL语句，会发现结果集多出了一条记录，即幻影数据。
间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。
如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

临键锁(Next-Key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。
如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。
依然是上面的例子，InnoDB，RR：
t(id PK, name KEY, sex, flag);
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

PK上潜在的临键锁为：
(-infinity, 1]
(1, 3]
(3, 5]
(5, 9]
(9, +infinity]

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

共享/排它锁(Shared and Exclusive Locks)

(1)事务拿到某一行记录的共享S锁，才可以读取这一行；
(2)事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；

意向锁(Intention Locks)

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。
意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。
意向锁有这样一些特点：
(1)首先，意向锁，是一个表级别的锁(table-level locking)；
(2)意向锁分为：
意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁
举个例子：
select ... lock in share mode，要设置IS锁；
select ... for update，要设置IX锁；

(3)意向锁协议(intention locking protocol)并不复杂：
事务要获得某些行的S锁，必须先获得表的IS锁
事务要获得某些行的X锁，必须先获得表的IX锁
(4)由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行

插入意向锁(Insert Intention Locks)

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。
插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。
多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此
在MySQL，InnoDB，RR下：
t(id unique PK, name);

数据表中有数据：
10, shenjian
20, zhangsan
30, lisi

事务A先执行，在10与20两条记录中插入了一行，还未提交：
insert into t values(11, xxx);
事务B后执行，也在10与20两条记录中插入了一行：
insert into t values(12, ooo);
(1)会使用什么锁？
(2)事务B会不会被阻塞呢？

回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：
使用的是插入意向锁
并不会阻塞事务B

自增锁（Auto-inc Locks）

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。
与此同时，InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。
一，案例说明

MySQL，InnoDB，默认的隔离级别(RR)，假设有数据表：

t(id AUTO_INCREMENT, name);

数据表中有数据：

1, shenjian

2, zhangsan

3, lisi

事务A先执行，还未提交：

insert into t(name) values(xxx);

事务B后执行：

insert into t(name) values(ooo);

问：事务B会不会被阻塞？

二，案例分析

InnoDB在RR隔离级别下，能解决幻读问题，上面这个案例中：

(1)事务A先执行insert，会得到一条(4, xxx)的记录，由于是自增列，故不用显示指定id为4，InnoDB会自动增长，注意此时事务并未提交；

(2)事务B后执行insert，假设不会被阻塞，那会得到一条(5, ooo)的记录；

此时，并未有什么不妥，但如果，

(3)事务A继续insert：

insert into t(name) values(xxoo);

会得到一条(6, xxoo)的记录。

(4)事务A再select：

select * from t where id>3;

得到的结果是：

4, xxx

6, xxoo

画外音：不可能查询到5的记录，再RR的隔离级别下，不可能读取到还未提交事务生成的数据。

这对于事务A来说，就很奇怪了，对于AUTO_INCREMENT的列，连续插入了两条记录，一条是4，接下来一条变成了6，就像莫名其妙的幻影。