数据库

发表于 分类于

第2章：关系模型基础

2.1 关系模型的基本概念

2.1.1 关系模型是什么

关系模型是用二维表来表示数据以及数据之间关系的一种数学模型。
例如学生表

sno sname age dept
001 张三 20 计算机
002 李四 21 软件工程

在关系模型中，这样的表叫做一个关系

2.1.2 关系模型的基本术语

普通说法 关系模型术语
表 关系<
行 元组
列 属性
列的取值范围 域
表结构 关系模式
某时刻表中的数据 关系实例

2.2 关系的基本性质

  1. 每一列数据类型相同

  2. 每一个属性不可再分，属性具有原子性

  3. 行，列的顺序无关

  4. 不允许出现完全相同的元组

2.3 码/键

2.3.1 超码

能唯一标识一个元组的属性或者属性组

例如：

Student(Sno, Sname, IDCard, ClassNo)

若  Sno 唯一，则：

1
2
3
Sno
Sno, Sname
Sno, ClassNo

都可以是超码

2.3.2 候选码 Unique

最小的超码

例如

Student(Sno, Sname, IDCard, ClassNo)

已知

1
2
3
4
Sno 唯一
IDCard 唯一
Sname 不唯一
ClassNo 不唯一

候选码是：

Sno IDCard

而不是

Sno, Sname

因为，相比于Sno, Sname 这个作为超码的属性组，还有更小的超码Sno等存在，不满足最小

2.3.3 主码 Primary Key

从候选码中指定一个作为主码

候选码可以有很多个，主码通常只有一个

主码要求：

唯一(Unique)， 非空(NOT NULL)

2.3.4 主属性和非主属性

主属性 ：包含在任一候选码中的属性

非主属性：不包含在任何候选码中的属性

2.3.4 外码 Foreign Key

一个关系中的属性引用另一个关系的主码

例如：

1
2
Student(Sno, Sname, DeptNo) Primary Key sno
Dept(DeptNo, DeptName) Primary key DeptNo

则有（Student中）：

FOREIGN KEY (DeptNo) REFERENCES Dept(DeptNo)

Student.DeptNo是外码

2.4 关系完整性

2.4.1 实体完整性

主码不能取空值，且不能重复

一句话总结：主码非空且唯一

2.4.2 参照完整性

要求：

    外码的值要么为空，要么参照等于被参照关系的某个主码的值。

一句话总结：外码是已存在的主码的值

2.4.3 用户自定义的完整性

第3章：关系代数

3.1 关系代数概述

3.1.1 定义

关系代数是一种关系数据库查询语言的理论基础

用一组运算对关系进行操作，得到新的关系

用符号对表进行查询

3.2 基本运算

关系代数常见基本运算：

1
2
3
4
5
6
7
选择 σ            --重点
投影 π            --重点
并 ∪
差 −
笛卡尔积 ×
连接 ⋈            --重点
除 ÷              --重点

3.3 选择运算σ

3.3.1 含义

选择运算用于从关系中选出满足条件的元组

也就是：筛选满足条件的行

3.3.2 形式

σ 条件(关系名称)

例如：

Student(Sno, Sname, Age, Dept)

查询计算机系的学生

σ Dept = '计算机系'(Student)

查询年龄大于20岁的学生

σ Age > 20 (Student)

3.4 投影运算 π

3.4.1 含义

投影运算用于从关系中选出若干属性列

3.4.2 形式

π 属性列表(关系名)

例子：

查询所有学生的姓名：

π Sname(Student)

查询所有学生的姓名和学号：

π Sname, Sno(Student)

3.5 选择和投影组合

例题：查询计算机系学生的姓名

π Sname (σ dept = '计算机系'(Student))

3.6 连接运算⋈

3.6.1 连接运算是什么？

连接运算用于把两个关系按照某种关系组合成一个新的关系。

直观理解：

连接 = 笛卡尔积 + 选择条件

也就是说，连接不是凭空而来的，它可以看成：

1
2
先 R x S
再从结果中筛选满足要求的元组。

形式上：

R ⋈ 条件 S

等价于：

σ 条件 (R × S)

3.6.2 θ连接

θ连接就是带一般比较条件的连接：

θ连接可以是：

=, >, <, >=, <=, !=

形式：

R ⋈ A θ B S

等价于:

σ A θ B(R × S)

例如:

Student ⋈ Student.DeptNo = Dept.DeptNo Dept

Student ⋈ Student.Age > Teacher.Age Teacher

3.6.3 等值连接

等值连接是θ连接的一种特殊情况。

条件中使用的是：

=

例如：

Student ⋈ Student.DeptNo = Dept.DeptNo Dept

fbf7eaa3-5a01-4ba8-9fe8-87608224d0ca

这里Student.DeptNo 和 Dept.DeptNo两列值相同，但是都在

3.6.4 自然连接⋈

含义

自然连接是最常考的连接。

自然连接要求：

  1. 两个关系中含有同名属性；

  2. 自动按照同名属性相等进行连接；

  3. 结果中同名属性只保留一列。

形式：

R ⋈ S

例如：

1
2
Student(Sno, Sname, DeptNo)
Dept(DeptNo, DeptName)

两个关系有同名属性DeptNo

所以：

Student ⋈ Dept

结果：

9a9eee24-562f-4335-a2ae-2b2b57751ee3

自然连接和等值连接的区别

类型 条件 同名属性是否只保留一列
等值连接 手动写A = B 不一定，会保留两个连接属性
自然连接 自动找同名属性 同名属性只保留一列

3.7 除运算÷

除运算是关系代数里最抽象也最易考的，通常对应这种查询：

查询：“满足所有条件”的对象

关键词一般是：

1
2
3
4
全部
所有
每一个
至少选修了全部...

比如：

查询选修了所有课程的学生。

3.7.1 除运算的直观理解

假设有两个关系：

1
2
SC(Sno, Cno)
Course(Cno)

SC表示学生选课：

Sno Cno
S1 C1
S1 C2
S1 C3
S2 C1
S2 C2
S3 C1

Course表示所有课程：

Cno
C1
C2
C3

现在问：

哪些学生选修了所有课程？

1
2
3
S1 选了 C1 C2 C3
S2 只选了 C1 C2
S3 只选了 C1

所以答案是：

S1

关系代数写作：

SC ÷ Course

3.7.2 除运算的形式

设：

1
2
R(X, Y)
S(Y)

那么：

R ÷ S

结果是：

S

含义是：找出哪些X，使得对于S中的每一个Y，组合(X, Y)都出现在R中。

一句话：

R ÷ S = 在R中，找出能配齐S中所有Y中的X

3.7.3 除法例题

例题：查询选修了全部课程的学生学号

关系：

1
2
SC(Sno, Cno)
Course(Cno, Cname)

注意，Course里有两个属性：

Cno, Cname

但是SC中只有Cno对的上，所以要先投影出Cno

π Cno(Course)

然后:

SC ÷ π Cno(Course)

如果要查学生姓名

π Sname(Student ⋈ (SC ÷ π Cno(Course)))

第4章：结构化语言SQL

4.1 SQL概述

4.1.1 SQL是什么

SQL，全称：

1
Stuctured Query Lauguage

中文叫做：结构化查询语言

4.1.2 SQL的特点

SQL最大的特点就是：非过程化

也就是说你只需要告诉数据库：我要什么

而不需要告诉数据库：怎么一步一步找

比如你写：

1
2
3
SELCT Sname
FROM Student
WHERE Dept = '计算机';

4.1.3 SQL的功能分类

SQL常分成四大类

分类 英文 作用 常见关键字
DDL Data Definition Language 数据定义 CREATE, DROP, ALTER
DML Data Manipulation Language 数据操纵 INSERT, UPDATE, DELETE
DQL Data Query Language 数据查询 SELECT
DCL Data Control Language 数据控制 GRANT, REVOKE

4.2 SQL基本书写规则

4.2.1 SQL不区分大小写

一般开始：

1
2
3
select* from Student;
和
SELECT* FROM Student;

效果一样

4.2.2 SQL语句用分号结尾

1
2
SELECT*
FROM Student;

末尾的；表示一条SQL语句结束

4.2.3 SQL推荐分行缩进

比如不要写成：

1
SELECT Sno,Sname,Age FROM Student WHERE Dept='计算机' AND Age>20;

建议写成：

1
2
3
4
SELECT Sno, Sname, Age
FROM Student
WHERE Dept = '计算机'
  AND Age > 20;

4.3 数据定义DDL

DDL用来定义数据库对象

常见的对象有：

1
2
3
4
5
数据库
表
视图
索引
模式

4.3.1 创建表

基本格式：

1
2
3
4
5
CREATE TABLE 表名 (
    列名 数据类型 约束，
    列名 数据类型 约束，
    ...
);

比如创建学生表：

1
2
3
4
5
6
CREATE TABLE Student(
    Sno CHAR(10) PRIMARY KEY,
    Sname VARCHAR(20) NOT NULL,
    Age INT,
    Dept VARCHAR(30)
);

4.3.2 常见数据类型

类型 含义 例子
INT 整数 年龄、数量
CHAR(n) 定长字符串 学号、身份证号
VARCHAR(n) 变长字符串 姓名、地址
DATE 日期 出生日期
DECIMAL(p, s) 定点小数 金额、成绩

重点区分：

1
2
CHAR(n)：定长
VARCHAR(n)：变长

4.3.3 删除表：

1
DROP TABLE Student;

注意，不是清空表的数据，而是把表本身删掉。

4.3.4 修改表

添加列：

1
2
ALTER TABLE Student
ADD Email VARCHAR(50);

删除列：

1
2
ALTER TABLE Student
DROP COLUMN Email;

修改列类型：

1
2
ALTER TABLE Student
MODIFY Sname VARCHAR(30);

4.4 SQL约束

4.4.1 主码约束

1
2
3
4
CREATE TABLE Student(
    Sno CHAR(10) PRIMARY KEY,
    Sname VARCHAR(20)
);

主码要求：唯一且非空

也可以写成表级约束：

1
2
3
4
5
CREATE TABLE Student(
    Sno CHAR(10) PRIMARY KEY,
    Sname VARVHAR(20),
    PRIMARY KEY (Sno)
);

4.4.2 非空约束 NOT NULL

1
Sname VARCHAR(20) NOT NULL

表示姓名不能为空。

4.4.3 唯一约束 UNIQUE

1
IDCard CHAR(18) UNIQUE

表示身份证号不能重复

4.4.4 外码约束 FOREIGN KEY

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Dept (
    DeptNo CHAR(10) PRIMARY KEY,
    DeptName VARCHAR(30)
);

CREATE TABLE Student (
    Sno CHAR(10) PRIMARY KEY,
    Sname VARCHAR(20),
    DeptNo CHAR(10),
    FOREIGN KEY (DeptNo) REFERENCES Dept(DeptNo)
);

含义：

Student.DeptNo的值必须来自Dept.DeptNo，或者为空

对应前面关系完整性里面的参照完整性

4.4.5 约束检查 CHECK

1
Age INT CHECK (Age >= 0 AND Age <= 150)

表示年龄必须在合理范围内

再比如成绩：

1
Grade INT CHECK (Grade >= 0 AND Grade <= 100)

4.5 SQL 数据操作DML

4.5.1 插入数据 INSERT

1
2
INSERT INTO Student
VALUES ('001', '张三', 20, '计算机');

这种写法要求值的顺序和表的结构完全一致

更推荐写列名：

1
2
INSERT INTO Student (Sno, Sname, Age, Dept)
VALUES ('001', '张三', 20, '计算机');

4.5.2 修改数据 UPDATE

1
2
3
UPDATE Student
SET Age = 21
WHERE Sno = '001';

4.5.3 删除数据 DELETE

1
2
DELETE FROM Student
WHERE Sno = '001'

4.6 SQL查询 SELECT

基本格式

1
2
3
SELECT 列名
FROM 表名
WHERE 条件;

4.6.1 查询全部列

1
SELECT *FROM Student;

含义：
查询 Student 表中的所有列

* 表示所有属性。

但考试/实际开发里，如果明确知道要哪些列，更推荐写列名：

1
SELECT Sno, Sname, AgeFROM Student;

4.6.2 查询部分列

SELECT Sname, AgeFROM Student;

对应关系代数：
π Sname, Age(Student)

4.6.3 消除重复 DISTINCT

SQL 默认不消除重复行。

比如：SELECT DeptNo FROM Student;

结果可能是：

DeptNo
D1
D1
D2
D2

如果想去重：
SELECT DISTINCT DeptNo FROM Student;

结果：

DeptNo
D1
D2

对应关系代数里，关系本身是集合，天然不允许重复；但 SQL 表更接近“多重集合”，所以需要 DISTINCT。

4.7 WHERE 条件查询

4.7.1 比较运算

常见比较符：
=, >, <, >=, <=, <> 或 !=

例如：
SELECT Sname FROM StudentWHERE Age > 20;

查询年龄大于 20 的学生姓名。

4.7.2 逻辑运算 AND / OR / NOT

AND：同时满足

1
SELECT Sname FROM StudentWHERE DeptNo = 'D1'  AND Age > 20;

含义：
院系是 D1，并且年龄大于 20

OR：满足任意一个

1
SELECT Sname FROM StudentWHERE DeptNo = 'D1'   OR DeptNo = 'D2';

含义：
院系是 D1 或 D2

NOT：取反

1
SELECT Sname FROM StudentWHERE NOT DeptNo = 'D1';

更常写成：
SELECT Sname FROM StudentWHERE DeptNo <> 'D1';

4.7.3 BETWEEN 范围查询

1
SELECT Sname, Age FROM StudentWHERE Age BETWEEN 18 AND 22;

等价于：
SELECT Sname, AgeFROM StudentWHERE Age >= 18 AND Age <= 22;

注意：
BETWEEN 包含边界

也就是包括 18 和 22。

4.7.4 IN 集合查询

1
SELECT Sname FROM Student WHERE DeptNo IN ('D1', 'D2', 'D3');

等价于：
SELECT SnameFROM StudentWHERE DeptNo = 'D1' OR DeptNo = 'D2' OR DeptNo = 'D3';

所以：
IN = 属于某个集合

反过来：
SELECT SnameFROM StudentWHERE DeptNo NOT IN ('D1', 'D2');

含义：
不属于 D1 或 D2

4.7.5 LIKE 模糊查询

LIKE 用来做字符串匹配。

常用通配符：

通配符 含义
% 任意长度字符串，包括空串
_ 任意单个字符

查询姓张的学生

1
SELECT *FROM StudentWHERE Sname LIKE '张%';

含义：
以“张”开头，后面随便多少字符

比如：
张三张小明张伟

都满足。

查询名字第二个字是“三”的学生

1
SELECT *FROM Student WHERE Sname LIKE '_三%';

解释：
_ 表示第一个字符任意三 表示第二个字符必须是三% 表示后面任意

4.7.6 NULL 空值查询

这个很重要，也很容易错。

查询某列为空：
SELECT *FROM Student WHERE Email IS NULL;

查询某列不为空：
SELECT *FROM Student WHERE Email IS NOT NULL;

不能写：
WHERE Email = NULL

这是错的。

因为 NULL 不是普通值，它表示：
未知不存在不可用

所以判断空值必须用：
IS NULL / IS NOT NULL

4.8 ORDER BY 排序

4.8.1 升序 ASC

1
SELECT Sno, Sname, Age FROM Student ORDER BY Age ASC;

ASC 表示升序，默认就是升序，所以也可以写：
SELECT Sno, Sname, AgeFROM StudentORDER BY Age;

4.8.2 降序 DESC

1
SELECT Sno, Sname, Age FROM Student ORDER BY Age DESC;

表示按年龄从大到小排序。

4.8.3 多列排序

1
SELECT Sno, Sname, DeptNo, Age FROM Student ORDER BY DeptNo ASC, Age DESC;

含义：先按 DeptNo 升序如果 DeptNo 相同，再按 Age 降序

4.9 聚集函数

聚集函数对一组数据进行统计

函数 含义
COUNT(*) 统计行数
COUNT(列名) 统计该列非空值个数
SUM(列名) 求和
AVG(列名) 求平均值
MAX(列名) 最大值
MIN(列名) 最小值

4.9.1 COUNT

查询学生总人数：

1
2
SELECT COUNT(*)
FROM Student;

查询有邮箱的学生人数：

1
2
SELECT COUNT(Email) //只统计Email的非NULL行
FROM Student;

4.9.2 AVG平均值

查询平均年龄：

1
2
SELECT AVG(Age)
FROM Student;

查询数据库课程的平均年龄:

1
2
3
SELECT AVG(Age)
FROM Student
WHERE Cname = '数据库';

4.9.3 MAX/MIN

查询最高成绩：

1
2
SELECT MAX(Grade)
FROM sc;

4.9.4 SUM

查询某门课的总成绩：

1
2
3
4
SELECT SUM(Grade)
FROM sc
WHERE Cno = 'C1';

4.10 GROUP BY 分组

GROUP BY是SQL查询里非常核心的东西。

4.10.1 为什么需要分组

比如你要查：

每个院系有多少个学生

不是查全校总人数，而是按照院系分开看

1
2
3
SELECT DeptNo, COUNT(*)
FROM Student
GROUP BY DeptNo;

GROUP BY的规则

如果使用了GROUP BY,那么SELECT的后面通常只能出现：

  • 分组函数

  • 聚集函数

比如这个是对的：

1
2
3
SELECT DeptNo, COUNT(*)
FROM Student
GROUP BY DeptNo;

但是这个通常是错的：

1
2
3
SELECT DeptNo, Sname, COUNT(*)
FROM Student
GROUP BY DeptNo;

一个DeptNo组里面可能有多个学生，数据库不知道你想显示哪个Sname

4.11 HAVING 分组后的筛选

4.11.1 WHERE 和 HAVING的区别

子句 作用对象 能不能用聚集函数
WHERE 分组前的行 一般不能直接用聚集函数
HAVING 分组后的组 可以用聚集函数

一句话：

WHERE 筛行，HAVING 筛组

4.11.2 例子：查询人数超过20的院系

1
2
3
4
SELECT DeptNo, COUNT(*)
FROM Student
GROUP BY DeptNo
HAVING COUNT(*) > 20;

4.12 SQL 查询执行顺序

虽然SQL写法是：

1
2
3
4
5
6
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...

但是逻辑执行顺序大概是：

1
2
3
4
5
6
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

4.14 多表连接查询

4.14.1 旧式连接写法：FROM 多表 + WHERE 条件

1
2
3
4
5
SELECT Student.Sname
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno
    AND SC.Sno = Course.Cno
    AND Course.Cname = '数据库';

4.14.2 标准的JOIN写法

1
2
3
4
5
SELECT Student.Sname
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '数据库';

结构是：

1
2
3
4
表1
JOIN 表2 ON 连接条件
JOIN 表3 ON 连接条件
WHERE 普通筛选条件;

注意区分：

1
2
ON：写表和表之间怎么连接
WHERE：写连接之后筛哪些行

表太长时，可以取别名：

1
2
3
4
5
SELECT S.Sname
FROM Student AS S
JOIN SC AS SC ON S.Sno = SC.Sno
JOIN Course AS C ON SC.Cno = C.Cno
WHERE C.Cname = '数据库';

AS能够省略

4.14.3 多表连接易错的地方：

错误 1：忘记连接条件

1
2
SELECT S.Sname, C.Cname
FROM Student S, Course C;

这会产生笛卡尔积，结果会乱配。

比如 100 个学生、20 门课：
100 × 20 = 2000 行

很可能不是你想要的。

错误 2：连接条件写少了

1
2
3
SELECT S.Sname, C.Cname
FROM Student S, SC, Course C
WHERE S.Sno = SC.Sno;

这里只连了 Student 和 SC，但 Course 没连上，也会导致课程乱配。

4.15 内连接，外连接

4.15.1 INNER JOIN 内连接

普通JOIN默认就是内连接

含义就是：只保留两边能匹配上的记录

4.15.2 LEFT JOIN 左外连接

1
2
3
SELECT S.Sname, SC.Cno
FROM Student S
LEFT JOIN SC ON S.Sno = SC.Sno;

含义：

左表 Student 的所有学生都保留

如果右表 SC 没用匹配记录，则右表就会显示 NULL

4.15.3 RIGHT JOIN 右外连接

1
2
3
SELECT S.Sname, SC.Cno
FROM Student S
RIGHT JOIN SC ON S.Sno = SC.Sno;

含义：

右表 SC 的所有学生都保留

如果左表 Student 没用匹配记录，则左表就会显示 NULL

4.16 子查询 Subquery

4.16.1 子查询是什么

子查询就是：

一个SELECT语句里面嵌套另一个SELECT语句

基本形式：

1
2
3
4
5
6
7
SELECT ...
FROM ...
WHERE 某列 IN (
    SELECT ...
    FROM ...
    WHERE ...
);

4.17 不相关子查询

4.17.1 含义

不相关子查询指：

内层子查询可以独立执行，不依赖外层查询

例如：

1
2
3
4
5
6
7
SELECT Sno, Sname
FROM Student
WHERE Sno IN (
    SELECT Sno
    FROM SC
    WHERE Cno = 'C1'
);

4.17.2 IN的含义

IN表示：

某个属于子查询返回结果的集合

格式：

1
2
3
表达式 IN(
    子查询
)

实例：

1
2
3
4
5
6
7
SELECT Sno, Grade
FROM SC
WHERE Cno IN (
    SELECT Cno
    FROM Course
    WHERE Cname = '数据库'
);

4.17.3 IN子查询也可以改写成JOIN

刚才这题也可以写成连接：

1
2
3
4
SELECT SC.Sno, SC.Grade
FROM SC
JOIN Course ON SC.Sno = Course.Cno
WHERE Course.Cname = '数据库';

4.18 比较运算符子查询

4.18.1 子查询返回单值时，可以用=、>、<

格式：

1
2
3
表达式 比较符 (
    子查询
)

例如：查询年龄大于全体学生平均年龄的学生姓名。

1
2
3
4
5
6
SELECT Sname
FROM Student
WHERE Age > (
    SELECT AVG(*)
    FROM Student
);

4.19 ANY / SOME 子查询

4.19.1 ANY / SOME 是什么

ANY 和SOME含义一样

表示：ANY / SOME = 至少满足其中一个

4.19.2 例子：年龄小于信息系某个学生的其他系学生

1
2
3
4
5
6
7
SELECT Sname, Age
FROM Student
WHERE Age < ANY(
    SELECT Age
    FROM Student
    WHERE Dept = 'Information'
) AND Dept <> 'Information';//排除信息系自己的学生

4.20 ALL 子查询

4.20.1 ALL是什么

ALL表示：

对所有子查询返回值都满足

例子：年龄小于信息系所有学生的其他系学生

1
2
3
4
5
6
7
SELECT Sname, Age
FROM Student
WHERE Age < ALL (
    SELECT Age
    FROM Student
    WHERE Dept = 'Information'
) AND Dept <> 'Information';

4.21 EXISTS 子查询

EXISTS 子查询判断查询的结果是否为空。

例题：查询选修了C1的学生姓名

1
2
3
4
5
6
7
8
SELECT Sname
FROM Student
WHERE EXISTS (
    SELECT*
    FROM SC
    WHERE SC.Sno = Student.Sno
    AND SC.Cno = 'C1'
);

第6章：事务、并发与故障恢复

6.1 为什么需要事务？

通常一个SQL语句可以看作关系数据库的一个原子操作，但实际应用中，很大业务不是一个SQL能完成的，所以要把多个SQL语句定义成一个原子操作。

比如转账：

1
2
UPDATE Account SET balance = balance - 100 WHERE name = 'A';
UPDATE Account SET balance = balance + 100 WHERE name = 'B';

这两句话必须绑在一起，否则如果A已经扣了钱，系统突然崩了，B没加钱，那数据库就坏了。

所以事务就是：

用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是不可分割的工作单位。相关SQL：

1
2
3
BEGIN TRANSACTION:    开始事务
COMMIT:    提交事务，确认修改生效
ROLLBACK：   回滚事务，撤销修改

6.2 事务的ACID特性：

事务的四个特性是：原子性、一致性、隔离性、持久性。

A：原子性

事务中的操作：

1
2
3
要么全部完成
要么全部不完成
不能只完成一半

C：一致性

事务执行前后，数据库都应该从一个正确状态变到另一个正确状态。

比如银行转账前后，总额没变，这叫一致。

I：隔离性

多个事务并发执行时，结果应该一个一个串行执行。

比如：两个事务同时操作A:

1
2
T1: A = A - 100
T2: A = A * 2

虽然实际可能交替执行，但是最终效果必须等价于：

先T1后T2或先T2后T1

D：持久性

事务一旦COMMIT，结果就应该永久保存。

哪怕提交后系统崩溃，重启后也应该能恢复这个结果。

6.3 并发导致的不一致性：

6.3.1 丢失修改 Lost Update

核心：

两个事务都读同一个旧值；

都基于旧值修改；

后写入的覆盖前写入的。

6.3.2 脏读 Dirty Read

核心：

读到了别人还没提交的数据；

但是后来别人回滚了。

例子：

1
2
3
4
5
初始 A = 50

T1 写 A = 30
T2 读 A = 30
T1 ROLLBACK，A 恢复为 50

T2 读到的30是“脏”的，因为它来自一个最终失败的事务。

脏读 = 读到未提交，后来被回滚

6.3.3 不可重复读 Non-repeatable Read

核心：

同一个事务内，两次读到同一个数据，结果不一样

1
2
3
4
5
初始 A = 100

T1 读 A = 100
T2 修改 A = 200 并提交
T1 再读 A = 200

T1 第一次读是100，第二次读是200。

6.3.4 幻读 Phantom Read

核心：

同一个事务内，两次按相同条件查询，结果集的行数变了

例子：

1
2
3
4
T1: SELECT * FROM Student WHERE age > 18;
T2: INSERT INTO Student VALUES (..., age = 20);
T2: COMMIT;
T1: SELECT * FROM Student WHERE age > 18;

T1 第二次查询的时候，突然多出一条符合条件的记录

6.4 为什么会出现这些问题？

出现不一致的原因是：不同数位间冲突操作的无序进行。

也就是说，并发本身不是错，冲突操作乱序执行才是问题。

常见冲突：

1
2
3
读-写冲突：一个读，一个写
写-读冲突：一个写，一个读
写-写冲突：两个都写

并发控制的两大思路：

  • 对事务规定一定顺序：时间戳

  • 对共享资源进行控制：加锁

6.4.1 封锁

封锁的意思是：

事务在操作了某个数据对象之前，先向系统请求向它加锁。加锁后，事务对该对象有一定控制器，释放锁之前，其他事务的操作会受到限制。

数据库里最基本的两类锁：

  • S锁：共享锁，也叫读锁

  • X锁：排他锁，也叫写锁

6.4.2 S 锁：共享锁 / 读锁

事务要读数据时，可以加 S 锁。

特点：
多个事务可以同时读同一个数据所以 S 锁和 S 锁相容

例子：
T1 给 A 加 S 锁，读 AT2 也可以给 A 加 S 锁，读 A

因为大家都只是看，不改。

6.4.3 X 锁：排它锁 / 写锁

事务要修改数据时，需要加 X 锁。

特点：
X 锁很霸道别人既不能读，也不能写

例子：
T1 给 A 加 X 锁，修改 AT2 不能再给 A 加 S 锁T3 也不能再给 A 加 X 锁

因为一旦有人在写，别人读可能读到脏数据，别人写可能造成丢失修改。

锁相容矩阵

     对方已有锁
      S     X
申请 S √     ×
申请 X ×     ×

也就是：
只有S-S相容，其他都不相容

6.5 封锁协议：

光有S锁、X锁还不够，还要规定：

1
2
3
什么操作加什么锁？
什么时候加锁？
什么时候释放锁？

常考的是三级封锁协议：

6.5.1 一级封锁协议

事务T在修改数据R之前，必须先对R加X锁；

知道事务结束才释放X锁。

它主要解决：丢失修改，但是不能防止脏读

6.5.2 二级封锁协议

事务读数据前必须加上S锁；

读完之后即可释放S锁。

6.5.3 三级封锁协议

在二级的基础上，把读锁也延长：

事务读数据之前加S锁；

S锁要保持到事务结束之后才释放。

三级封锁协议对比：

协议 规则                     能解决
一级 写前加 X，事务结束释放 丢失修改
二级 一级 + 读前加 S，读完释放 丢失修改、脏读
三级 一级 + 读前加 S，事务结束释放 丢失修改、脏读、不可重复读

一句话总结：

1
2
3
一级管写写
二级管写读
三级管重复读

6.6 两段锁协议2PL

三级封锁协议是在讲：

不同的锁保持多久，能够避免哪些异常

接下来常考的是二封锁协议，2PL，主要用来保证：

并行调度是可串行化的

也就是说：虽然事务交叉执行，但是结果等价于某个串行执行顺序。

6.6.1 两段锁协议的规则

一个事务加锁和解锁的过程必须分成两个阶段：

1
2
3
4
5
第一阶段：扩展阶段
只能加锁，不能解锁

第二阶段：收缩阶段
只能解锁，不能再加锁

也就是说，一旦一个事务释放了第一个锁，它以后就不能再申请新锁。

化成时间线：

1
2
加锁  加锁  加锁  |  解锁  解锁  解锁
     扩展阶段    |      收缩阶段

6.7 可串行化调度

6.7.1 串行调度：

串行调度就是事务一个接一个执行，中间不交叉。

1
2
T1: R(A) W(A) COMMIT
T2: R(A) W(A) COMMIT

串行调度通常是安全的，但是效率低

6.7.2 并发调度：

1
2
3
4
T1: R(A)
T2: R(A)
T1: W(A)
T2: W(A)

这样效率搞，但可能出问题，如丢失修改

6.7.3 冲突可串行化

考试里常考“冲突操作”。

两个操作冲突，需要同时满足：

1
2
3
1. 来自不同事务
2. 操作同一个数据对象
3. 至少有一个是写操作

6.7.4 怎么判断冲突可串行化？

常用方法是画 优先图 / 前驱图

步骤：

1
2
3
1. 每个事务画成一个节点
2. 如果Ti的某个操作先于Tj的冲突操作，就画Ti -> Tj
3. 如果图中无环，则冲突可串行化，反之则不可冲突串行

例如：

1
2
3
4
R1(A)
W1(A)
R2(A)
W2(A)

T1对A的写在T2对A的读/写之前，所以画：

T1 -> T2

无环，所以等价于穿行顺序：

T1 再 T2

6.8 死锁DeadLock

死锁的定义：数据库并发控制中，死锁通常是因为多个事务相互等待对方释放锁。

最经典形式：

1
2
T1 持有 A,申请 B
T2 持有 B,申请 A

死锁的处理思路：

一般有两种：

1
2
预防死锁
检测并解除死锁

预防死锁：

提前规定规则，让死锁根本不容易发生。

常见思路：

1
2
一次封锁法：事务开始前一次性申请所有需要的锁
顺序封锁法：所有事务都按统一顺序申请锁

检测并解除死锁

系统允许死锁可能发生，但会定期检测

方法：

1
2
3
构造事务等待图
如果等待图有环，说明发生死锁
选择一个事务撤销，让他释放锁

6.8 故障恢复

三个关键词：

1
2
3
日志
UNDO
REDO

数据库恢复靠日志记录”事务做过什么“，系统崩溃后再根据日志判断。

1
2
已提交事务：REDO
未提交事务：UNDO