InnoDB

InnoDB的特性

• 支持提交，回滚和崩溃恢复能力的安全事务（ACID兼容）；
• 支持行级锁；在行级锁定中，InnoDB使用next-key锁定。
• 支持外键（foreign key）；
• 将表和索引储存在同一个表空间（文件）里面，举例来说，现在有两个表格not_null_1和not_null_2，它们都有索引，在磁盘中的储存方式为：
  

AUTOCOMMIT

• 在InnoDB中，所有用户行为都在事务内发生，如果autocommit（自动提交）被允许，每个SQL语句将形成一个单独的事务，默认情况下autocommit是开启的。
• 如果设置 set autocommit = 0，也就是关闭自动提交，那么可以认为一个用户总是有一个事务打开着，该事务需要用COMMIT或者ROLLBACK来结束。

InnoDB锁定

共享锁和排他锁

InnoDB实现标准行级锁定，在这里有两种类型的锁：

• 共享锁(S)允许事务去读一行；
• 排他锁(X)允许事务区更新或删除一行。

意向锁

1）引入意向锁的目的

• 提高对某个数据对象加锁时系统的检查效率；
• 意向锁是有数据引擎自己维护的，用户无法手动操作意向锁。

2）意向锁分类

• IS锁，意向共享锁，事务T意图给表上的某些行设置S锁定；
• IX锁，意向独占锁，事务T意图给表上的某些行设置X锁定。

3）意向锁的工作协议：

• 事务T在获得某行的S锁之前，必须获得该行所在表的IS锁或IX锁；
• 事务T在获得某行的X锁之前，必须获得该行所在表的IX锁；

4）兼容性如下：

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

举例来看：

• 假设没有意向锁，事务T1想要在表table1上面加一个表级S锁，那么T1必须先检查table1有没有被其他事务加上表级X锁；然后再检查table1的每一行是否被加上行级X锁，这意味着需要遍历table1的每一行，效率很低；
• 现在引入意向锁，我们只需要检查table1上面有没有被加上IX即可，不需要遍历table1的每一行，提高了效率。

记录锁

• 记录锁是对索引记录的锁定。例如，select c1 from t where c1 = 10 FOR UPDATE;，会为c1 = 10的行加上记录锁，可以防止其他事务插入，更新或删除t.c1的值为10 的行；
• 记录锁锁定的是索引记录，即使没有定义索引的表也是如此。对于没有索引的表，InnoDB创建一个隐藏的聚簇索引并将该索引用于记录锁定。

间隙锁

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

• 间隙锁是对索引记录之间的间隙的锁定，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定，例如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;，可以防止其他事务插入c1值为15的记录。
• 间隙锁的锁定范围可以是在单列索引上，也可以在多列索引上，也可以是空。
• 对于使用unique索引来锁定唯一行的语句，不需要进行间隙锁定；
• 以语句SELECT * FROM child WHERE id = 100 FOR UPDATE;为例进行分析：
  1）如果id上未建立索引或具有非唯一性索引，则该语句会锁定前面的间隙；
  2）如果id上具有唯一性索引，则该语句不会锁定前面的间隙；
• 间隙锁是可以共存的，例如事务T1和事务T2可以同时锁定某个间隙，间隙锁只是为了防止其他事务在间隙中插入、修改或删除数据。

临键锁

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

• 临键锁锁是记录锁和间隙锁的结合；对于某个索引记录而言，临键锁就是在该索引记录上的记录锁和该索引记录之前的间隙锁的结合；
• InnoDB在执行行级锁定的过程中，当它搜索或扫描表索引时，会在遇到的索引记录上设置X锁或S锁。因此，行级锁实际上是对索引记录的锁定（the row-level locks are actually index-record locks）；
• 举例而言，如果某个事务在某条记录R上拥有X锁或S锁，那么其他事务不能在索引记录R之前插入新的索引记录；
• 假设一个索引树上面有值10、11、13和20，那么在该索引树上面可能存在的临键锁如下：

(负无穷,10]
(10,11]
(11,13]
(13,20]
(20,正无穷]

• 默认情况下，InnoDB在可重复读事务隔离级别中运行。在这种情况下，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读现象。

事务隔离级别

事务隔离是数据库处理的基础之一，隔离的缩写是ACID中的I；隔离级别是一种设置，用于在多个事务同时进行更改或查询时调节性能与结果的可靠性的平衡。

0.概念

• 快照读：快照读使能通过MVCC来实现的；快照读记录数据的可见版本（可能已经过期）；在RR级别中，普通的select是快照读；
• 当前读：当前读是通过next-lock锁来实现的；当前读总能读取到最新的版本；在序列化级别中，普通的select会隐式转化为当前读。

1.REPEATABLE READ

• InnoDB默认的隔离级别；
• 非锁定读取（普通select语句）：则同一事务中的所有一致读取（非锁定读取）将读取由该事务中的第一个此类读取建立的快照，这些应该是由MVCC来实现的。
• 对于锁定读取（SELECT包含FOR UPDATE或LOCK IN SHARE MODE）、UPDATE和DELETE语句，锁定范围取决于语句的搜索条件：
  1）对于唯一（unique）索引上的唯一性搜索，只会锁定找到的索引记录（通常只有1条？），而不会锁定该记录前的间隙；
  2）对于其他的搜索条件，InnoDB会使用间隙锁或临键锁锁定其扫描的范围，也就是说，REPEATABLE READ可能会锁定自己不进行更新的行。

2.READ COMMITTED

• 非锁定读取，即使在统一事务中，每个普通select都会设置并读取自己的新快照，该快照会保证不会发生脏读；
• 锁定读取（SELECT包含FOR UPDATE或LOCK IN SHARE MODE）、UPDATE、DELETE语句，InnoDB仅锁定索引记录，不会锁定它们之间的间隙，因此可能会产生幻读现象；
• 对于UPDATE或 DELETE语句， InnoDB仅对其更新或删除的行持有锁。MySQL评估WHERE条件后，将释放不匹配行的记录锁 。这大大降低了死锁的可能性，但是仍然可以发生,也就是不使用临键锁。
• 对于UPDATE语句，如果某行已被锁定，则InnoDB 执行“ 半一致 ”读取，将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否匹配WHERE条件。如果该行匹配（需要更新），则MySQL会再次读取该行，这一次将InnoDB其锁定或等待对其进行锁定。（也就是说，对于已被锁定的行，会先根据“ 半一致 ”读取结果判断是否满足where条件，如果满足再去尝试加锁，否则不会加锁）

3.READ UNCOMMITTED

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read.

• 翻译：SELECT语句以非锁定方式执行，但是可能会使用行的早期版本。因此，此隔离级别读取是不一致的，也就是存在脏读；（个人观点：READ COMMITTED中使用了快照读能够保证它不读到脏数据，而READ UNCOMMITTED没有使用快照读）。
• 其他的与READ COMMITED类似；

4.SERIALIZABLE

• 当auto-commit关闭时，会将所有的普通select语句隐式装换为SELECT…FOR UPDATE；
• 当auto-commit开启时，。。。

5.如何修改事务的隔离级别

下列语句会将当前窗口设置为read committed级别，也就是说，所有窗口都要执行一次，才能使所有窗口都是read committed级别：

set session transaction isolation level read committed;
start transaction;
...
commit;

6.semi-consistent read：“半一致性”读

semi-consistent read
A type of read operation used for UPDATE statements, that is a combination of READ COMMITTED and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock on it. This type of read operation can only happen when the transaction has the READ COMMITTED isolation level.

• “半一致性”读是指在RC级别下，当update语句读取的行正在被其他事务锁定，不会直接加锁等待，而是先读取最新的一个历史版本（ the latest committed version），如果此版本符合where条件，就会再次读取该行并等待锁，如果不符合where条件，就会忽略此行。这样会避免一些锁等待；

• RR级别默认没有“半一致性”读的，对于RR级别下的update操作，会为扫描过程中遇到的索引记录都加上X锁，并且会持有锁直到事务结束。

• 看几个例子
  1）下面例子中c2上没有索引，所以直接在聚簇索引上扫描，当T2扫描到T1正锁定的记录上时，返回上一个提交的版本(ROW_ID = 1, 1 ,1)，经判断不满足where条件c2=2，因此不加锁直接跳过：

-- RC级别
create table t2(c1 int, c2 int);
insert into t2 values(1,1);  
insert into t2 values(10,10);
		事务T1									事务T2					结果
		begin;						
update t2 set c2=2 where c2=1;							 				成功1行
												begin;
									update t2 set c2=3 where c2=2;	  不阻塞，更新成功0行
	    commit;									commit;

2）顺便再看一个例子中，T2的where条件是c2=1，这时会阻塞：

-- RC级别
create table t2(c1 int, c2 int);
insert into t2 values(1,1);  
insert into t2 values(10,10);
		事务T1									事务T2					结果
		begin;						
update t2 set c2=2 where c2=1;							 				成功1行
												begin;
									update t2 set c2=3 where c2=1;	  阻塞直到T1结束
	    commit;									commit;

7.临键锁如何工作

官方文档例子
在RR级别下使用临键锁，在RC级别下则不会使用临键锁，下面通过例子来对比RR级别和RC级别：
从下表开始：

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

此时，表没有建立索引，事务T1执行update语句：

-- 事务T1
BEGIN;
UPDATE t SET b = 5 WHERE b = 3;

接下来事务T2启动，然后执行update语句：

-- 事务T2
BEGIN;
UPDATE t SET b = 4 WHERE b = 2;

分析结果：
使用默认的RR级别，

• RR级别下，会为扫描过程中遇到的索引记录（本例中是隐藏的自增索引）加X锁（这就是临键锁的工作方式），并且会持有锁直到事务结束。
• 在此例中，对于事务T1，由于b列没有建立索引，所以只好进行全表扫描，并锁定所有行直到事务结束：

-- x-lock表示加X锁，retain x-lock表示继续持有x-lock
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

• 接着，事务T2也试图扫描全表并在每一行上加X锁，但是X锁已经被T1持有，因此T2只能阻塞到T1提交或回滚；

-- 等待持有X锁的权利
wait for x-lock(1,2)... ...
wait for x-lock(2,3)... ...
wait for x-lock(3,2)... ...
wait for x-lock(4,3)... ...
wait for x-lock(5,2)... ...

使用RC级别

• RC级别下，也会为扫描过程遇到的索引记录加X锁，但是经过判断会释放不进行修改的记录的锁；也就是说，T1在RC级别下是这样工作的：

-- unlock表示释放锁
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

• 对于事务T2，对于UPDATE语句，如果某行已被锁定，则InnoDB 执行“ 半一致 ”读取（详细解析看上边），将最新的提交版本（T1未提交，因此不包括T1的修改结果）返回给MySQL，以便MySQL确定该行是否匹配WHERE条件。如果该行匹配（需要更新），则MySQL会再次读取该行，这一次将InnoDB其锁定或等待对其进行锁定：
  

x-lock(1,2); update(1,2) to (1,4); retain x-lock
(2,3)已被锁住，经半一致性读取结果判断无需加锁
x-lock(3,2); update(3,2) to (3,4); retain x-lock
(2,3)已被锁住，经半一致性读取结果判断无需加锁
x-lock(5,2); update(5,2) to (5,4); retain x-lock

• 这里假设T2执行的是UPDATE t SET b = 3 WHERE b = 5;而不是UPDATE t SET b = 4 WHERE b = 2;，会发现结果如下，说明了InnoDB 确实执行“ 半一致 ”读取，将最新的提交版本（T1未提交，因此不包括T1的修改结果）返回给MySQL：
  

-- 从半一致性读取情况来看，不存在b = 5的行，因此不需要update
x-lock(1,2); unlock(1,2)
(2,3)已被锁住，经半一致性读取结果判断无需加锁
x-lock(3,2); unlock(3,2)
(2,3)已被锁住，经半一致性读取结果判断无需加锁
x-lock(5,2); unlock(5,2)

• 再假设T2执行的是UPDATE t SET b = 5 WHERE b = 3;而不是UPDATE t SET b = 4 WHERE b = 2;，也就是同T1一样，会发现被阻塞：
  

-- 由于(2,3)和(4,3)已经的X锁已经被T1持有，因此T2只能等待
x-lock(1,2); unlock(1,2)
wait for x-lock(2,3)... ...  # (2,3)已被锁住，并且由半一致性读取结果判断需要加锁，所以会在此处阻塞
x-lock(3,2); unlock(3,2)
wait for x-lock(4,3)... ...
x-lock(5,2); unlock(5,2)

• 最后，再验证一下“半一致性读”值在UPDATE下使用，不会在DELETE情况下使用：假设T2执行的是delete from t WHERE b = 2;而不是UPDATE t SET b = 4 WHERE b = 2;，这时候会发现T2会被阻塞：
  

-- T2是DELETE操作，所以不会使用“半一致性读”
x-lock(1,2); delete(1,2); unlock(1,2)
wait for x-lock(2,3)... ...  # (2,3)已被锁住，所以T2扫描到(2,3)时会等待锁
x-lock(3,2); delete(2,3); unlock(3,2)
wait for x-lock(4,3)... ...
x-lock(5,2); delete(5,2); unlock(5,2)

8.当前读与快照读

在RC、RR级别下，分为当前读和快照读。
当前读
对于update、insert、delete操作，都会执行当前读；

9.一些疑问及理解

1.快照是如何建立的？

• 个人理解，快照就是在某一时间点数据库的状态。然而数据库不可能在每一个时间点都备份一遍，因此需要多版本控制MVCC；
• 所有时间点的快照都是一致性的，不会包含未提交的结果（避免了脏读）。

2.为什么可重复读级别下存在幻读？

• 个人认为，可重复读与序列化的区别在于可重复读允许快照读，而快照读是导致幻读的原因；
• 在序列化级别中，普通SELECT被隐式转换为SELECT…FOR UPDATE，也就是当前读，并且还会使用临键锁进行锁定；
• 在可重复级别中，普通的SELECT是快照读，即数据库某个时间点的状态（具体而言是事务在执行第一个select时建立的快照），之后的事务的提交可能会被快照读忽略，进而产生幻读；
• 举个例子，在下面的例子中，事务T1就产生了幻读：

create table t(id int primary key);

		事务T1												事务T2
		BEGIN;												BEGIN
select * from t where id=1;# 建立快照读，结果为空集
												 	insert into t values(1);
															COMMIT;
insert into t values(1);   # 发生id冲突
select * from t where id=1;# 快照读，结果依然为空集

3.为什么RR级别能够避免“不可重复读现象”，而RC级别不行？

• 在RR和RC级别中，对于普通的select，都采用了快照读的方式，但是它们的快照读有所区别；
• RR级别中，所有普通类型的select将读取由该事务中的第一个此类读取建立的快照，也就是说，在此快照后提交的事务对数据的修改不会被select察觉到，因此就不会有“不可重复读现象”；
• 在RC级别中，每个普通select都会设置并读取自己的新快照，新的快照可能会比旧的快照多出其他事务修改并提交的结果，因此就可能产生“不可重复读现象”。

TODO：
1）不同语句锁定的范围
2) mysql中的死锁