数据库技术

作者: sschrodinger | 来源:发表于2019-05-28 14:53 被阅读0次

数据库技术

sschrodinger

2019/05/06

引用

MySQL 技术内幕 - InnoDB 存储引擎（第二版）姜承尧著

关键需求

ACID

ACID 指的是 SQL 中设计的标准。分别指如下四个单词：

原子性（atomicity）：指一个修改要么被运用，要么不被运用
一致性（consistency）：指事务将数据库从一种状态变成另外一种状态
隔离性（isolation）：指事务提交前对其他事务都不可见
持久性（durability）：指事务一旦提交，其结果就是永久性的

隔离级别

虽然 SQL 规定了必须满足 ACID 四种特性，但是大部分 SQL 数据库并不能满足所有的 ACID 要求，所以规定了四种隔离级别，隔离级别越高越能满足 ACID 要求。

分别是：

未提交读（Read uncommitted）
已提交读（Read committed）
可重复读（Repeatable read）
可串行化（Serializable ）

脏读

脏读指一个事务读到错误的数据。比如说，事务 1 修改了某数据，这时事务 2 读取了该值并进行了处理。事务 1 在之后又因为错误进行了回滚，那么这时事务 2 就读取到了脏数据。

不可重复读

不可重复读指一个事务对同一个操作在同一个事务中不能返回相同的结果。比如说，事务 1 读取某数据 d，这时事务 2 读取了该值进行了处理并提交。事务 1 在之后又使用相同的方式读取 d。这时，发现第一次读取的数据和第二次不相同

幻读

第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

READ UNCOMMITTED

指的是事务可以读取另一个事务没有提交的数据。

READ COMMITTED

指的是事务可以读取另一个事务提交的数据。

REPEATABLE READ

指的是事务始终读取开始时的数据，但是有幻读现象。

SERIALIZABLE

完全满足 ACID 特性的隔离级别，只能串行化执行。

总结

隔离级别	脏读（Dirty Read	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted	可能	可能	可能
已提交读（Read committed	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

简介

MySQL 存储引擎采用插件式的方法，在一个 MySQL 进程中，可能有多种存储引擎。每一种存储引擎都有其自己的特性。

InnoDB

InnoDB 存储引擎是 MySQL 5.5.8 版本之后的默认引擎，其主要涉及目标是面向在线事务处理（OLTP）的应用。有如下特点：

行锁设计

支持外键、全文索引

非锁定读

多版本并发控制 (MVCC) 来获得高并发性

默认为 REPEATABLE 级别

采用聚集索引的方式，每张表按照主键顺序存放，如果没有显式定义主键，会为每一行生成一个 6 字节的 ROWID，并以此为主键

支持事务

MyISAM

MyISAM 是 MySQL 5.5.8 之前的默认存储引擎，有如下特点：

表锁设计

支持全文索引式定义主键，会为每一行生成一个 6 字节的 ROWID，并以此为主键

不支持事务

NDB

NDB 是一个集群存储引擎，有如下特点：

全部数据放在内存中，主键查找速度极快

通过添加 NDB 数据存储节点可以线性提高数据库性能

NDB 存储引擎的连接操作（join）在 MySQL 数据库层中完成，不在存储引擎中完成，导致大量的网络开销

InnoDB 关键技术

checekpoint 技术

当前事务普遍采用了 Write Ahead Log 策略，即当前事务提交时，先写重做日志，再修改页，当由于发生宕机而导致的数据丢失时，通过重做日志完成数据库的恢复，这也是事务 ACID 中 D（持久性）的要求。

如果重做日志可以做的无限大、内存缓存也可以做的无限大，我们当然不将数据刷回硬盘，但是实际情况我们并不能满足，况且重做日志太大会导致恢复时间过长， checkpoint技术可以保证解决如下几个问题：

缩短数据库的恢复时间

缓冲池不够时，将脏页刷新到磁盘

重做日志不够时，刷新脏页

当数据库发生宕机时，数据库不要重做所有的日志，因为 Checkpoint 之前的页都已经刷新回磁盘，故数据库只需对 Checkpoint 之后的重做日志进行恢复，这样就大大缩短了恢复时间。

在 InnoDB 中，使用 LSN （Log Sequence Number）标记版本，对于大于 checkpoint 的版本，需要利用重做日志进行恢复，小于其的版本表明已经被持久化，不需要恢复。

插入缓冲（Insert Buffer）

在 InnoDB 中，主键时行唯一的标识符，应用程序中的行记录按照主键递增的顺序进行插入。因此，插入聚集索引（primary key）一般都是顺序的，不需要磁盘的随机读取。

但是对于一个 NOT UNIQUE 的非聚集的辅助索引，在进行插入的时候，数据页的存放还是按照主键的顺序存放的（逻辑顺序），但是对于非聚集索引叶子节点的插入就不在是顺序的了，这时就需要离散的访问非聚集索引页。（B+ 树的特性决定，关于 B+ 树，参考 B+ 树章节）

插入缓冲，对于非聚集索引的插入或者更新操作，不是每一次直接将其插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放到一个 Insert Buffer 中。其他进程在一定的频率下对 Insert Buffer 和辅助索引叶子节点进行合并，这样可以将多个插入合并到一个操作中，就大大提高了效率。

note

索引是辅助索引才会使用 insert buffer

索引要求不唯一，因为，如果是唯一的，就需要去查找索引页判断唯一性，又会产生离散读取的情况发生。

两次写（Double Write）

当数据库发生宕机时，可能 InnoDB 存储引擎正在写入某个页到表中，而这个页只写了一部分，比如 16KB 的页，只写了前 4KB，这样的情况称为部分写失效。

重做日志中记录是对页的物理操作，如偏移量 800，写 ‘aaa’ 记录，但是如果这个页已经发生了损坏，着这样恢复就没有意义。
当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是两次写。

double write 由两部分组成，一部分时内存中的 doublewrite buffer，大小为 2MB，另一部分时物理磁盘上共享表空间中连续的128个页，大小也为2MB。对缓冲池中的脏页进行刷新时，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 doublewrite buffer，饭后通过doublewriter buffer 再分两次，每次 1MB 顺序地写入共享表空间地物理磁盘上，然后马上调用 fsync 函数，同步磁盘。在 double write 写完之后，在将各数据写入到自己的磁盘空间。如果操作系统在将页写入到磁盘中发生了崩溃，则可以通过共享表空间的 double write 找到页的副本，并恢复数据。

自适应哈希（Adaptive Hash Index）

AHI 如果对某个页地连续访问模式相同，即对某个联合索引 (a, b) 使用 where a = XXX 访问多次，InnoDB 就会自动生成 hash，加快访问速度。

异步 IO （Async IO）

即可以在 IO 时进行其他操作。

刷新邻近页（Flush Neibor Page）

当刷新一个脏页时，判断该页所在区的所有页是不是脏页，如果是，则一同刷新，这在机械硬盘上对速度有很大的提高。

索引与算法

在 InnoDB 中，有三种常见的索引，分别是 B+ 树索引，全文索引和哈希索引。

B+ 树索引

B+ 树由 B 树和索引顺序访问组成。是为磁盘或者其他直接存取辅助设备设计的一种平衡查找树。

在 B+ 树中，所有的记录节点都是按照键值的大小顺序存放在同一层的叶子节点上，由各叶子节点的指针进行连接。非叶子节点并不存储数据。如下所示：

B+树

B + 树的具体实现，参见算法-B+树章节

使用 B+ 树的优点

相对于二叉查找树，有更小的树深度（即树更宽），大量的减少了 IO 时间。

相对于 B 树，所有的查找深度都是树高，在效率上较稳定，同时因为叶节点不存放数据，每一个非叶子节点的扇入可以更大，保证树更矮。

叶子节点根据键值大小相联，对批量查找（排序查找，范围查找）效率更高，如返回大于某数的所有节点。

B+ 树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），其内部都是使用 B+ 树实现的。不同的是，叶子节点存放的是否是一整行数据（聚集索引存放一整行数据，辅助索引存放聚集索引的位置）

聚集索引

聚集索引按照每一张表的主键构造一棵 B+ 树，同时叶子节点存放的即为整张表的行记录信息，所以也将聚集索引的叶子节点称为数据页。

叶子节点根据键值大小相联，对批量查找（排序查找，范围查找）效率更高，如返回大于某数的所有节点。

如下一张 sql 表：

CREATE TABLE t (
    a INT NOT NULL,
    b VARCHAR(8000),
    c INT NOT NULL,
    PRIMARY KEY (a),
    KEY idx_c (c)
)ENGINE=INNODB

假如使用主键进行批量查询，如下：

SELECT * FROM t ORDER BY ID
// 或者
SELECT * FROM t WHERE t > xx

对于排序范围查询来说，因为 B+ 树通过双向链表连接行，所以，一行一行的读数据必定有序，不需要排序，范围查询只需要按指针访问数据节点就行。

辅助索引

辅助索引的叶子节点不存储行数据，只是存储一个书签，用来存储主键值。

利用辅助索引查找时，会先遍历辅助索引的 B+ 树，获得主键值，然后在聚集索引中，利用主键值遍历找到对应数据。

具体结构如下：

辅助索引

联合索引

联合索引即多个列组成的索引，如下 SQL 语句：

CREATE TABLE t (
    a INT,
    b INT,
    PRIMARY KEY(a),
    KEY idx_A_B (a,b)
)

联合索引本质上也是一棵 B+ 树，只不过内部排序按照列的顺序依次排序，如下：

联合索引

可以看到，当使用如下 SQL 语句时，会使用该索引：

SELECT * FROM t WHERE a = XXX AND b = XXX
SELECT * FROM t WHERE a = XXX

但是使用SELECT * FROM t WHERE b = XXX并不会使用索引，因为 b 在树中无序排列。

当需要对非第一列进行排序时，会优先使用联合索引，因为联合索引的非第一列都进行了排序。如下表，虽然有关于 a 的单独索引，但是因为要按照 b 排序，所以优化器选择 idx_A_B 作为使用的索引，这样就不需要再次排序。

CREATE TABLE t (
    a INT,
    b INT,
    KEY idx_A (a),
    KEY idx_A_B (a,b)
);

SELECT * FROM t WHERE a = XXX ORDER BY b

覆盖索引

由于辅助索引包含了主键信息，所以主键的搜索不需要再通过聚集索引。

假设某辅助索引叶子节点包括如下数据：

{
    "key1": "primary key1",
    "key2": "primary key2"
}

那么如下的 SQL 都可以直接返回结果：

SELECT key2 FROM t WHERE key1=xxx;
SELECT primary key2, key2 FROM t WHERE key1=xxx;
SELECT primary key1, key2 FROM t WHERE key1=xxx;
SELECT primary key1, primary key2, key2 FROM t WHERE key1=xxx;

B+ 树的分裂

在实际实现中，B+ 树的分裂并不总是从中间节点开始分裂。

按照理论的方式，假设 B+ 树的容量为 10，当在 B+ 树已经有 1,2,3,4,5,6,7,8,9 ，当下一个数据来时，需要进行分裂，假如从中间点，即 5 作为分裂点记录，那么会得到以下分裂结果：

{
    "P1":[1, 2, 3, 4],
    "P2":[5, 6, 7, 8, 9, 10],
}

假设该 B+ 树为聚集索引，插入是根据自增顺序进行自增的，那么将不会有新的数据被分配到 P1 段，造成了空间的巨大浪费。

InnoDB 使用三个变量保存插入的顺序信息，如下：

PAGE_LAST_INSERT
PAGE_DIRECTION
PAGE_N_DIRECTION

根据如上三个变量决定保分裂的方向和分裂点记录。对于自增操作，分裂点就是插入的数据本身。

建立索引的要求

并不是所有列都适合作为索引，索引的选择是唯一值尽量的多。

比如说在按照性别选择时，可取值的范围只有 "M" 和 "F"，如下：

SELECT * FROM student WHERE sex = "M"

如果有一千万条数据，则根据索引找到 "M" 之后还需找到 M 的结尾，完全没有必要。

MySQL 保存了一个值 Cardinality，表示该索引唯一值的预估树，如果 $\frac{Cardinality}{n_rows_in_table} \approx 1$ ，即唯一值很多（高选择性），则值得做索引。

Cardinality 采取抽样的形式获取，InnoDB 默认对 8 个叶子节点进行采样。如下：

取得 B+ 树索引中叶子节点的数量，记为 A

随机获得 8 个 B+ 树索引中叶子节点，统计每个页不同记录的个数，即为 P1,P2,...P8

根据采用结果给出 Cardinality 的预估值： $Cardinality = \frac{P_1 + P_2 + \cdots + P_8}{8} * A$

Hash 索引

InnoDB 采用哈希表和链表机制构成非冲突的哈希，类似于 HashMap。InnoDB 对热点的字典查询建立自适应哈希索引，即对 = 操作建立哈希索引，对缓冲池的页进行缓存，极大的提高了效率。

全文检索

因为 B+ 树的特点，我们可以根据索引子段的前缀对数据进行查找，如：

SELECT * FROM blog WHERE content like 'AAA%'

因为该查找类似于范围查找，等效于如下 SQL：

SELECT * FROM blog WHERE content >= 'XXX' and content <= 'AAB'

但是大多数时候我们需要对列中出现的单词进行查找，如下 SQL：

SELECT * FROM blog WHERE content like '%AAA%'

这就需要用全文检索。

全文检索的实现

InnoDB 使用倒排索引实现全文检索，使用字典存储单词所在的行。

有两种表现形式的关联数组：

inverted file index，其表现形式为 {单词, 所在文档ID}
full inverted index，其表现形式为 {单词, (单词文档所在ID, 在文档中的具体位置)}

如下一列：

row_id	Test
1	Pease porridge hot, pease porridge cold
2	pease porridge pot
3	Nine days old
4	Some like it hot, some like it cold
5	Some like it in the pot
6	Nine days old

对于 inverted file index 关联数组，如下：

{
    "code":{1,4},
    "days":{3,6},
    "hot":{1,4},
    "in":{2,5},
    "it":{4,5},
    "like":{4,5},
    "nine":{3,6},
    "old":{3,6},
    "pease":{1,2},
    "porridge":{1,2},
    "pot":{2,5},
    "some":{4,5},
    "the":{2,5}
}

对于 full inverted index 关联数组，如下：

{
    "code":{1:6,4:8},
    "days":{3:2,6:2},
    "hot":{1:3,4:4},
    "in":{2:3,5:4},
    "it":{4:(3,7),5:3},
    "like":{4:(2,6),5:2},
    "nine":{3:1,6:1},
    "old":{3:3,6:3},
    "pease":{1:(1,4),2:1},
    "porridge":{1:(2,5),2:2},
    "pot":{2:5,5:6},
    "some":{4:(1,5),5:1},
    "the":{2:4,5:5}
}

通过查询单词，找到所在行号或者主键，进而找到所在行。

note
全文检索现在有如下限制

每张表只能有一个全文检索索引

由多列组合而成的索引列必须使用相同的字符集与排序规则

不支持没有单词界定符的语言，如中文。

Mysql 也允许使用如 SELECT * FROM t WHRER MATCH(body) AGAINST ('+PEASE -hot IN BOOLEAN MODE') 进行查找。

事务管理

事务是数据库区别于文件系统的重要特性之一。事务会把数据库从一种一直状态转换为另一种一致状态，在数据库提交工作时，可以确保要么所有修改都已经保存了，要么所有修改都不保存。

理论上来说，事务需要严格满足 ACID 特性。但是各数据引擎出于各种目的，没有实现所有的 ACID 特性，如 Oracle 数据库，其默认事务隔离级别为 READ COMMITTED，不满足 I 特性，InnoDB 引擎，其默认级别为 REPEATABLE READ，满足所有特性。

事务分类

总体来说，事务可以分为如下五种类型：

扁平事务
带保存点的扁平事务
链事务
嵌套事务
分布式事务

扁平事务

扁平事务的所有操作都在同一层次，由 BEGIN 开始，到 COMMIT 或者 ROLLBACK 结束，其间的操作是原子的，要么都执行，要么都不执行。

如下：

-- 成功完成
BEGIN;
Op 1;
Op 2;
...
Op K;
COMMIT;

-- 回滚情况
BEGIN;
Op 1;
Op 2;
...
Op K;
ROLLBACK;

-- 外界原因要求停止
BEGIN;
Op 1;
Op 2;
...
-- 由于外界原因要回滚，如超时等

带有保存点的扁平事务

保存点用来通知系统应该记住事务当前的状态，以便能够在发生错误时，事务能回到保存点当时的状态。

关于保存点的语法如下所示：

-- 定义一个保存点
SAVEPOINT identifier;
-- 回滚到特定保存点
ROLLBACK [work] TO [savepoint] identifier;
RELEASE SAVEPOINT identifier

note

如果使用 ROLLBACK [work] TO [savepoint] identifier;，事务仍在运行，如果需要彻底回滚并且结束任务，需要使用 ROLLBACK [work] 语法。

所有保存点都是易失的，即如果在重图出现崩溃，下次运行时，会重新运行所有事务。

链事务

链事务可以视为保存点的一个变种。链事务的思想是：在提交一个任务时，释放不需要的数据对象，将必要的处理上下文隐士地传给下一个要开始的事务，并将事务提交操作和开始的下一个事务合并成一个原子操作。

链事务在过程链中提交任务类似于新建了一个保存点，每次回滚，都会回滚到最近当前事务，即只能回复到最近的一个保存点。

与带保存点扁平事务区别

链事务只能回复到最近一个保存点，但是之前的操作永久化了，崩溃时不需要恢复。

链事务在执行链上的每一次提交时，都会释放掉不需要的锁，带有保存点的扁平事务不会。

嵌套事务

嵌套事务是一个层次结构的框架，有一个顶层事务控制着各个层次的事务，顶层事务之下嵌套的事务被称为子事务，代码如下：

BEGIN WORK
            BEGIN WORK
                SubOperationX
            COMMIT WORK
            BEGIN WORK
                SubOperationY
            COMMIT WORK
            ...
            BEGIN WORK
                SubOperationN
            COMMIT WORK
COMMIT WORK

note

注意：子事务既可以提交也可以回滚，但是他的提交操作并不能马上生效，必须等待父事务也提交

可以使用保存点来模拟嵌套事务，InnoDB 并不支持原生嵌套事务

事务的实现

事务的实现采用 redo 日志和 undo 日志实现，redo 日志用来保证事务的原子性和持久性，undo 日志用来保证事务的一致性。要求当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，写入到重做日志中，才能进行提交

redo 日志

redo 日志是物理日志，记录的是磁盘上每一个位置存储的是什么，redo 日志可以重复利用。

在 redo 日志内部，维持着一个日志序列号（LSN）, LSN 单调递增，代表了三个含义：

重做日志写入的总量
checkpoint 的位置
页的版本

如果发生崩溃时，InnoDB 引擎会对比 checkpoint 的位置和页的版本，如果页的版本低于 checkpoint，则需要恢复。

redo 日志是物理日志，所以是等幂的，即，不管根据该日志恢复多少次，结果都不会变，重复执行没有影响。

undo 日志

undo 日志记录的是逻辑日志，比如说在任务中有一个删除，在逻辑日志中就将其删除位置为 True，如果是修改日志，就在 undo log 中记录他原来的值和他修改之后的值，最后通过另外一个线程合并，得到真正的修改。

锁

Lock 与 Latch

在数据库中，Lock 与 Latch 都是锁，Latch 是用来控制程序的正确性。在 InnoDB 中，latch 又可以分为 mutex 和 rwlock，其目的是保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

lock 的对象是事务，用来锁定数据库中的对象，如表、页、行。并且一般仅在事务 commit 或者 rollback 后进行释放（不同事务级别释放的时间可能不同），并且大部分有死锁检测机制。

Lock 类型 ?

InnoDB 采用行锁设计，共有两种标准的行级锁：

共享锁（S Lock）：允许读取某一行
排他锁（X Lock）：允许事务删除或更新一行数据

下表展示了其兼容性：

、	X	S
X	不兼容	不兼容
S	不兼容	兼容

InnoDB 同时还支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。（使用意向锁）

若将上锁的对象看成一棵树，根为数据库A，以下依次为表、页和行记录，对细粒度的对象上锁，则必须对粗粒度的对象上锁。例如，如果对记录进行上锁，则必须对数据库A，表、页上锁，最后对 r 上 X 锁。

InnoDB 支持意向锁比较简单，其意向锁即为表级别的锁，其支持两种意向锁：

意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
1. 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁

下表展示了其兼容性：

、	IS	IX	S	Y
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

读类型

在 InnoDB 中，分为两种读，一种是一致性非锁定读，一种是一致性锁定读。

一致性非锁定读

所谓的一致性非锁定读，即不需要等待访问的行上的 X 锁的释放，使用该行之前版本的数据。

InnoDB 存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据，如下所示：

一致性非锁定读

该实现通过 undo 段完成，通过读取 undo 段数据，得到原来数据的快照，此外，读取快照文件并不需要上锁，因为没有事务需要对历史的数据进行修改。undo 的具体实现参见事务管理-undo。

一个行记录可能有不止一个快照历史数据，一般称这种技术为行多版本技术，由此带来的并发控制，称之为多版本并发控制（MVCC Multi Version concurrency）

note

在 InnoDB 中，在事务隔离级别 READ COMMITTED 下和 REPEATABLE READ 下，使用非锁定一致性读

在 READ COMMITTED 下事务隔离级别下，对于快照数据，非一致性锁定读总是读取被锁定行的最新一份快照数据

在 REPEATABLE READ 下，对于快照数据，总是读取事务开始时的行数据版本

按照如下的顺序执行两个事务：

gantt
dateFormat s
section T1
BEGIN: 1, 01s 
SELECT XXX WHERE id = 1: 2, 01s
SELECT XXX WHERE id = 1: 5, 01s
SELECT XXX WHERE id = 1: 7, 01s
COMMIT: 8, 01s
section T2
BEGIN: 3, 01s
UPDATE XXX WHERE id = 1: 4, 01s
COMMIT: 6, 01s

在 REPEATABLE READ 事务隔离级别下，T1 中通过显式地执行 BEGIN 开启了一个事务，在 02 时间段读取，可以得到如下结果：

SELECT * FROM t WHERE id = 1;

/* 
OUTPUT:
    +----+
    | id |
    +----+
    |  1 |
    +----+

*/

在 04 时间段，T2 修改了数据，如下：

UPDATE t SET id = 3 WHERE id = 1;

之后 T1 再进行查询，如下：

--时间段 05， 07
SELECT * FROM t WHERE id = 1;

/* 
OUTPUT:
    +----+
    | id |
    +----+
    |  1 |
    +----+

*/

不管 T2 是不是已经提交，会发现 id 的值始终是 1，不会改变。

如果是 READ COMMITTED 隔离级别下，当 T2 提交之后，T1 会读取最新的数据，即如下输出如下：

SELECT * FROM t WHERE id = 1;

/* 
OUTPUT:
    +----+
    | id |
    +----+

*/

一致性锁定读

在 MySQL 中，在默认事务隔离级别下，需要显式地指定 SELECT 操作使用一致性锁定读，如下两种语句：

SELECT XXX FOR UPDATE
-- 加 X lock
SELECT XXX LOCK IN SHARED MODE
-- 加 S lock

note

以上两种语句需要放在事务中，当事务提交之后锁才会释放。

对于外键值的插入或更新，首先需要查询父表中的记录，即 SELECT 父表，注意需要对父表加上 SELECT XXX LOCK IN SHARE MODE

行锁算法

在 InnoDB 中，总共有 3 种行锁算法：

Record Lock：单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包括记录本身
Next-Key Lock：锁定一个范围，并且锁定记录本身

假设某表有索引记录 10， 11， 13， 20 四个值，如下：

key	content
10	XXX
11	XXX
13	XXX
20	XXX

在 Record Lock 下，修改 key = 10 的行，会给当前行上锁。

在 Gap Lock 下，修改 key = 10 的行，会给索引 $(-\infty, 10)$ 加上锁。

在 Next-Key Lock 下，修改 key = 10 的行，会给索引 $(-\infty, 10]$ 加上锁。

综上。

在 Gap Lock 下，锁定的行如下表对照：

10 \implies (-\infty, 10)

11 \implies (10, 11)

13 \implies (11, 13)

20 \implies (13, 20)

> 20 \implies (20, \infty)

在 Gap Lock 下，锁定的行如下表对照：

10 \implies (-\infty, 10]

11 \implies (10, 11]

13 \implies (11, 13]

20 \implies (13, 20]

> 20 \implies (20, \infty)

若事务 T1 已经通过 next-key locking 锁定了如下范围：

(10, 11],(11, 13]

当新插入了新的记录 12 时，则锁定的范围会变成：

(10, 11],(11, 12],(12, 13]

如果查询的索引是 unique 的，则使用 Record Lock 锁。

对于普通索引，InnoDB 默认使用 Gap Lock 和 Next-Key Lock 的组合。

CREATE TABLE z (
    a INT,
    b INT,
    PRIMARY KEY(a),
    KEY(b)
)
INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;

对于如上表，列 b 是辅助索引，若在会话 A 中执行下面的 SQL 语句：

SELECT * FROM z WHERE b = 3 FOR UPDATA;

辅助索引的值为 1, 3, 6, 8，如上的语句，会对 b = 3 加上 Next-key Lock，即对 (1, 3] 上锁。同时会对下一个索引，即 b = 6 加上 Gap Lock，即对 (3, 6) 上锁。最终上锁的范围是 (1, 6)，如下的 SQL 语句都会被诸塞。

SELECT * FROM z WHERE a = 5 LOCK IN SHARED MODE;
-- 行被 b = 3 阻塞
INSERT INTO z SELECT 4,2;
-- 行被 next-key lock 阻塞
INSERT INTO z SELECT 6,5;
-- 行被 gap key lock 阻塞

在 InnoDB 中，采用 next-key lock 加上 gap key lock 的方式解决不可重复读的问题（幻读），即在默认的事务隔离级别就可以避免幻读。幻读详见关键需求-事务隔离级别。

幻读指的是在同一个事务下，连续执行两次同样的 SQL 语句可能导致不同的结果。

对于如上数据表，当使用如下语句查询时：

SELECT * FROM z WHERE b > 3 FOR UPDATE;

InnoDB 不仅仅对 6 加锁，而是给 $(3, \infty)$ 这个范围加锁，所有在这个范围内的操作在其他事务中都会被阻塞。

note

InnoDB 存储引擎默认的事务隔离级别为 REPEATABLE READ，在该隔离级别下，采用 Next-Key Locking 加锁，而在事务隔离级别 READ COMMITTED 仅采用 RECORD Lock

数据库技术

数据库技术

引用

关键需求

ACID

隔离级别

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

总结

简介

InnoDB

MyISAM

NDB

InnoDB 关键技术

checekpoint 技术

插入缓冲（Insert Buffer）

两次写 （Double Write）

自适应哈希 （Adaptive Hash Index）

异步 IO （Async IO）

刷新邻近页 （Flush Neibor Page）

索引与算法

B+ 树索引

聚集索引

辅助索引

联合索引

覆盖索引

B+ 树的分裂

建立索引的要求

Hash 索引

全文检索

全文检索的实现

事务管理

事务分类

扁平事务

带有保存点的扁平事务

链事务

嵌套事务

事务的实现

redo 日志

undo 日志

锁

Lock 与 Latch

Lock 类型 ?

读类型

一致性非锁定读

一致性锁定读

行锁算法

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

两次写（Double Write）

自适应哈希（Adaptive Hash Index）

刷新邻近页（Flush Neibor Page）