数据库系统原理

1. 事务
   1. 概念
   2. ACID
   3. AUTOCOMMIT
2. 并发一致性问题
   1. 丢失修改
   2. 读脏数据
   3. 不可重复读
   4. 幻影读
3. 封锁
   1. 封锁粒度
   2. 封锁类型
   3. 封锁协议
   4. MySQL隐式与显式锁定
4. 隔离级别
   1. 未提交读（READ UNCOMMITTED）
   2. 提交读（READ COMMITTED）
   3. 可重复读（REPEATABLE READ）
   4. 可串行化（SERIALIZABLE）
5. 多版本并发控制
   1. 基本思想
   2. 版本号
   3. Undo 日志
   4. ReadView
   5. 快照读与当前读
6. Next-Key Locks
   1. record locks
   2. gap locks
   3. next-key locks
7. 关系数据库设计理论
   1. 函数依赖
   2. 异常
   3. 范式
8. ER图
   1. 实体的三种联系
   2. 表示出现多次的关系
   3. 联系的多向性
   4. 表示子类

一、事务

概念

事务指的是满足ACID特性的一组操作，可以通过Commit提交一个事务，也可以使用rollback进行回滚。

 ACID

1.原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

2.一致性（Consistency）

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

3.隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其他事务是不可见的。

4.持续性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

系统发生崩溃可以用重做日志（Redo log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的数据页的物理修改。

总结：事务的ACID特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要满足原子性，就一定能满足一致性
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久性是为了能应付系统崩溃的情况.

 AUTOCOMMIT

MySQL默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询操作都会被当作一个事务并自动提交。

二、并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

丢失修改

丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：1号事务与2号事务都对一个数据进行修改，1号先修改然后提交生效，2号随后修改，2号的修改覆盖了1号的修改。

读脏数据

读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。

 不可重复读

不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束时，另一事务也访问了该同一数据集合并做了修改，使得第一次事务两次访问结果不一致。

 幻影读

本质上也属于不可重复读

总结：产生并发不一致问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

 三、封锁

封锁粒度

MySQL中提供了两种封锁粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发度就越高。

但是加锁需要消耗资源，锁的各种操作（获取，释放，检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

封锁类型

1.读写锁

• 互斥锁（Exclusive），简写为X锁，又称写锁
• 共享锁（Shared），简写为S锁，又称读锁

有以下两个规定：

• 一个事务对数据对象加了X锁，就可以对A进行读取和更新。加锁期间其他事务不能对A加任何锁。
• 一个事务对数据对象A加了S锁，可以对A进行读取操作，但是不能进行更新操作。加锁期间其他事务能对A加S锁，但是不能加X锁。

 2.意向锁

使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

在存在行级锁和表级锁地情况下，事务T想要对表A加X锁，就需要先检测是否有其他事务对表A或者表A中地任意一行加了锁，那么就需要对表A的每一行都检测一次，这是非常耗时的。

意向锁在原来的X/S锁之上引入了IX/IS，IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁。有以下两个规定：

• 一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁；
• 一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁。

通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其他事务对表A加了X/IX/S/IS锁，如果加了就表示有其他事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败。

 封锁协议

1.三级封锁协议

一级封锁协议

事务T要修改数据A时必须加X锁，直到T结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

二级封锁协议

在一级的基础上，要求读取数据A时必须加S锁，读取完马上释放S锁。

可以解决读脏数据的问题，因为如果一个事务在对数据A进行修改，根据一级封锁协议，会加X锁，此时就不能加S锁了，也就是不会读入数据。

三级封锁协议

在二级的基础上，要求读取数据A时必须加S锁，直到事务结束了才能释放S锁。

可以解决不可重复读的问题，因为读A时，其他事务不能对A加X锁，从而避免了数据在读的期间发生修改。

2.两段锁协议

加锁和解锁分为两个阶段进行。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，是可串行化调度。

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但它还是可串行化调度。

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

MySQL隐式与显式锁定

MySQL的InnoDB存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。

InnoDB也可以使用特定的语句进行显式锁定：

SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

四、隔离级别

未提交读：事务中的修改，即便没有提交，对其他事务也是可见的。

提交读：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交执勤对其他事务是不可见的。

可重复读：保证在同一事务中多次读取同一数据的结果是一样的。

可串行化：强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

 五、多版本并发控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读总是读取最新的数据行，要求很低，无需使用MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用MVCC无法实现。

基本思想

在封锁一节中提到，加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中，读操作往往多余写操作，因此又引入读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而MVCC利用多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和CopyOnWrite类似。

在MVCC中事务的修改操作（DELETE , INSERT, UPDATE）会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其他事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照。

版本号

• 系统版本号 SYS_ID：是一个递增的数字，每开始一个新事务，系统版本号就会自动递增
• 事务版本号 TRX_ID：事务开始时的系统版本号。

Undo日志

MVCC的多版本指的是多个版本的快照，快照存储在Undo日志中，该日志通过回滚指针 ROLL_PTR把一个数据行的所有快照连接起来。

例如在MySQL创建一个表t，包含主键id和一个字段x。先插入一个数据行，然后对该数据行执行两次更新操作。

INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用START TRANSACTION将上面的操作当成一个事务来执行，根据MySQL的AUTOCOMMIT机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号TRX_ID和操作之外，还记录了一个bit的DEL字段，用于标记是否被删除。

 INSERT, UPDATE, DELETE操作会创建一个日志，并将事务版本号TRX_ID写入。DELETE可以看成是一个特殊的UPDATE，还会额外将DEL字段设置为1。

ReadView

MVCC维护了一个ReadView结构，主要包含了当前系统未提交的事务列表TRX_IDs{TRX_ID_1, TRX_ID_2, ···}，还有该列表的最小值TRX_ID_MIN和TRX_ID_MAX。

 在进行SELECT操作时，根据数据行快照的TRX_ID与TRX_ID_MIN和TRX_ID_MAX的关系，从而判断数据行快照是否可以使用：

• 小于最小值，表示该数据行快照是在当前所有未提交事务之前进行更改的，因此可以使用。
• 大于最大值，表示是在事务启动之后被修改的，因此不可以使用。
• 在之间，需要根据隔离级别进行再判断
  • 提交读：还在列表中，表示还未提交，因此不可用；否则可以用
  • 可重复读：都不可以使用，

 在数据行快照不可使用的情况下，需要沿着Undo Log的回滚指针找到下一个快照，再进行上面的判断。

快照读和当前读

1.快照读

MVCC的SELECT操作是快照中的数据，不需要进行加锁操作。

SELECT * FROM table ...;

2.当前读

MVCC其他会对数据库进行修改的操作（INSERT, UPDATE, DELETE）需要进行加锁操作，从而读取最新的数据。可以看到MVCC并不是完全不用加锁，而只是避免了SELECT的加锁操作。

在进行SELECT操作时，可以强制进行加锁操作。

SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;

六、Next-Key Locks

Next-Key Locks是MySQL的InnoDB存储引擎的一种锁实现。

MVCC不能解决幻影读问题，Next-Key Locks就是为了解决这个问题引入的。在可重复读隔离级别下，使用MVCC+Next-Key Locks可以解决幻读问题。

Record Locks

锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB会自动在主键上创建隐藏的聚簇索引。

Gap Locks

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其他事务就不能在t。c中插入15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks

他是以上两种锁的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隔。它锁定一个前开后闭区间，例如一个索引包含以下值：10，11，13，20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

七、关系数据库设计理论

函数依赖

记A->B表示A函数决定B，也可以说B函数依赖于A。

如果{A1， A2， ···}是关系的一个或多个属性的集合，该集合函数决定了关系的其他所有属性并且是最小的，那么该集合就称为键码。

对于A->B，如果能找到A的真子集A‘，使得A’->B，那么A->B就是部分函数依赖，否则就是完全函数依赖。

对于A->B， B->C，则A->C是一个传递函数依赖。

异常

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其他信息。

不符合范式的关系，会产生很多异常，主要有以下四种异常：

• 冗余数据：例如学生2出现了两次
• 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
• 删除异常：删除一个信息，那么也会丢失其他信息。例如删除课程1会导致学生1的信息丢失
• 插入异常：例如想要插入一个学生的信息，如果学生还没选课，那么就无法插入。

范式

范式理论是为了解决以上提到的四种异常。

高级别的范式依赖于低级别的范式，1NF是最低级别的范式。

第一范式：属性不可分

第二范式：每个非主属性完全函数依赖于键码。

可以通过分解满足。

分解前

 以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname
• Sno, Cname-> Grade

Grade完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

关系1

有以下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname

关系2

有以下函数依赖：

• Sno, Cname -> Grade

第三范式：非主属性不传递依赖于键码。

关系1存在如下传递函数依赖，可以继续分解

• Sno -> Sdept -> Mname

关系11

关系12

八、ER图

Entity-Relationship，有三个部分组成：实体、属性、联系。

用来进行关系数据库系统的概念设计。

实体的三种联系

包含一对一，一对多，多对多三种。

• 如果A到B是一对多关系，那么画个带箭头的线段指向B；
• 如果是一对一，画带两个箭头的线段
• 如果是多对多，画不带箭头的线段

 表示出现多次的关系

一个实体在联系出现几次，就要用几条线连接。

下图表示一个课程的先修关系，先修关系出现两个课程实体，第一个是先修课程，后一个是后修课程，因此需要用两条线来表示这种关系。

 联系的多向性

虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一个三元联系。

 表示子类

用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类上。

更多文章请关注《万象专栏》