第五章数据库设计和事务 ① 笔记

1.内容回顾

1.1. 课前测试
1.2.上节内容

2.本章重点

2.1.设计数据三大范式
2.2.表间关系
2.3.数据库事务

3.具体内容

3.1:数据库三大范式（设计规则）

范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。一个数据库表之间的所有字段之间的联系的合理性。
学生表，班级表
 范式是离散数学里的概念
 范式目标是在满足组织和存储的前提下使数据结构冗余最小化
 范式级别越高，表的级别就越标准
目前数据库应用到的范式有以下几层
 第一范式：1NF
 第二范式：2NF
 第三范式：3NF
除此之外还有BCNF范式,4NF,5NF
一个数据库表设计的是否合理，要从增删改查的角度去考虑，操作是否方便

 面试题：(重要)

第一范式(1NF)：

确保表中每一列数据的原子性，不可再分！
比如: 外国人的名字可以分为FirstName和LastName，所以设计表的时候要把名字分开

第二范式(2NF)：

在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。

第三范式设计表：

在满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。
当数据库到达第三范式的时候，基本上有关数据冗余，数据插入、删除、更新的异常问
题得到了解决，这也是一个”合法的”数据库最基本的要求，但是效率问题就另当别论
了，因为表越多，连接操作就越多，但是连接是一个比较耗资源的操作。

在设计数据库的时候，要在满足自己需求的前提下，尽可能的满足三大范式。

3.2 表间的关系

需求分析：
 一对一关系：一个学号对一个姓名
在设计数据库时如果是一对一关系，直接设计成一张表。（如果在字段非常多的情况下，可以做合理的分表）
 一对多（多对一）关系：一个老师多个班级，一个班级对多个学生，
一个订单包含多个商品。多个商品属于一个订单。
设计时主要是通过外键关联。
 多对多关系：学生对课程 ,用户购买商品
设计数据库时，多对多关系，需要一个中间表进行关联！
案例：
创建数据库表的前提是，要先理清楚表间关系。
权限系统设计
员工信息表(包含账号): 工号,姓名,…
部门表: 部门编号,名字…
职位表(是否与部门关联): 职位编号,名字,…
功能菜单表: 菜单编号,菜单名字,菜单级别,父级菜单,url…
职位菜单关联表: 职位编号,菜单编号

3.3 事务管理

3.3.1.为什么需要事务（一件完整的事情）

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。即一组sql中哪怕有一条失败也会失败
例子: 转账操作,一个账户减钱，一个账户加钱
默认情况下，mysql的事务是自动提交的，也就是执行了增删改语句之后，数据直接持久化到磁盘上，不能撤销。
但是如果改为手动事务之后，更新过的数据，再没有使用commit提交时，可以通rollback进行撤回。

3.3.2.使用事务解决问题

转账案例：张三给李四转账。

#创建银行帐号表
create table bank
(
	bid int primary key auto_increment,
	account varchar(20),
	money int
)

select * from bank;

insert into bank
(account,money)
values
('张三',10000),
('李四',10000)

事务处理1: 执行过程中遇到错误，回滚事务

#开启事务
start transaction;
update bank
set money = money - 3000
where account='张三'

select * from bank

#在向李四转钱之前，发生错误了，此时回滚事务
rollback;

事务处理2: 执行过程正常，提交事务

#开启事务
start transaction;

update bank
set money = money - 3000
where account='张三'

update bank
set money = money + 3000
where account= '李四'

select * from bank

#提交事务
commit;

3.3.3.数据库事务的原理

如果不写start transaction/begin;commit; 此时事务默认开启自动提交；
在数据库中，事务都是自动提交的。事务的自动提交就是执行语句完成之后就立刻持
久化到数据库中。
start transaction/begin;开始事务
rollback;回滚事务
commit;提交事务
当我们添加了start transaction/begin;和commit；后事务的提交就从自动变成手动。
因为中途出错，所以导致commit不执行，也就是说缓冲区中的数据没有持久化的数据库
中。

3.3.4.什么是数据库事务

从开启到提交为一个事务。
由此可见，一个事务对应一组业务。一个事务中间可以有一条sql,多条sql。
所以一个业务开始之前开启事务一个业务结束之后提交事务。如果中途出错，可以
回滚事务
我们这个转账案例：需要几个事务？
可以写成两个事务，但是不合适。因为我们的需求让张三减的同时让李四加钱。只能写
成一个事务。

3.3.5.事务的特征ACID 面试题(重要)

要求概念背会
一、事务的基本要素（ACID）
事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问
并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵
语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如
start transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。
在事务开始和结束之间做的所有的事情叫完整事务。
在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

1、原子性（Atomicity）：

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：

事务开始前和结束后，数据库的数据完整性约束没有被破坏，事务前后操作数据是一致的。比如A向B转账，不可能A扣了钱，B却没收到。能量守恒。

3、隔离性（Isolation）：在并发场景下有不同的设置

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
两个事务之间是有隔离级别，隔离级别的不同会导致出现不同的问题。此时产生三种问题：脏读幻读不可重复读。

4、持久性（Durability）：

持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

3.4 事务的并发问题

3.4.1.事务并发问题

数据库事务无非就两种：查询数据的事务（select）、更新数据的事务（update,insert）。
在没有事务隔离控制的时候，多个事务在同一时刻对同一数据的操作可能就会影响到结
果，通常有四种情况：
 两个更新事务同时修改一条数据时，会出现数据丢失，绝对不允许出现
 一个更新事务更新一条数据时，另一个读取事务读取了还没提交的更新，这种情况下会出现读取到脏数据
 一个读取事务读取一条数据时，另一个更新事务修改了这条数据，这时就会出现不可重现的读取
 一个读取事务读取时，另一个插入事务（注意此处是插入）插入了一条新数据，这样就可能多读出一条数据，出现幻读
注意：前三种是对同一条数据的并发操作，对程序的结果可能产生致命影响，尤其是金融等实时性，准确性要求极高的系统，绝不容许这三中情况的出现。
相比第四种情况不会影响数据的真实性，在很多情况下是允许的，如社交论坛等实时性要求不高的系统！

事务的并发问题：

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数
据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数
据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
3、A事务查询了某个Id的记录，发现为空，准备插入该编号的记录。此时B事务先做了
插入，并做了提交，此时A事务插入同编号的记录时触发了主键重复的异常，对于A事务
而言，第一次读的结果就像发生了幻觉。
注意：不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁
住满足条件的行，解决幻读需要锁表

3.4.2.MySQL事务隔离级别(重要)(用于解决事务并发问题)

隔离级别要求会描述和代码实现过程及描述

对于MySQL的Innodb的默认事务隔离级别是重复读（repeatable read）。可以通过下
面的命令查看：

mysql> SELECT @@tx_isolation;--5.7版本
mysql> SELECT @@transaction_isolation; --8.0版本
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
#设置mysql的隔离级别：
#set session transaction isolation level 设置事务隔离级别

#设置read uncommitted级别：
set session transaction isolation level read uncommitted;

#设置read committed级别：
set session transaction isolation level read committed;

#设置repeatable read级别：
set session transaction isolation level repeatable read;

Read Uncommitted(未提交读):
在该隔离级别下，并发事务可以读取未提交的数据，可能导致脏读不可重复读幻读
等问题。
 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少
用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称
之为脏读

#事务隔离级别:未提交读(read uncommitted)
#未提交读级别会导致脏读的问题
#脏读问题的实现步骤(注意,事务a，事务b在不同的脚本中上运行)
#1.事务a，手动事务，更新数据
#2.事务b，在事务a没有提交的情况下,查询a更新的数据
#3.事务a, 回滚事务
#4.此时，事务b查询的结果和此时数据库中的数据是不一致的，称为脏读

#事务a
#设置事务为未提交读
set session transaction isolation level read uncommitted;
#开始手动事务
start transaction;
#更新数据
update classInfo
set className='111'
where classId=1;
#回滚事务
rollback;

#事务b:在另外一个脚本中运行
set session transaction isolation level read uncommitted;
select * from classInfo where classId=1

Read Committed（提交读）：
在该隔离级别下，并发事务只能读取提交过的数据，可以避免脏读问题，但是可能导致
不可重复读幻读等问题。
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单
定义：一个事务只能看见已经提交事务所做的改变。
 这种隔离级别也导致所谓的不可重复读（Nonrepeatable Read），因为同一事务的
其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结
果

#事务隔离级别:read committed(提交读)
#提交读会避免脏读的问题，但是会导致不可重复读问题
#不可重复读问题的实现步骤:
#1.事务a,开启手动事务,先查询一次编号为1的班级数据
#2.事务b,开启手动事务，更新编号为1的班级名称,并提交了事务
#3.事务a,在同一个事务中再查询编号为1的班级数据
#4.此时a事务中的两次查询结果不一致，称为不可重复读

#事务a
#设置事务隔离级别为提交读
set session transaction isolation level read committed;
#开启手动事务
start transaction;
#在事务中第一次查询数据
select * from classInfo where classId = 1;
#在事务中第二次查询数据
select * from classInfo where classId =1;
#提交事务
commit;

#事务b(在另外一个脚本中执行)
set session transaction isolation level read committed;
start transaction;
update classInfo
set className='111'
where classId = 1
commit;

/*
提交读避免了脏读的问题
#事务a
#设置事务为未提交读
#set session transaction isolation level read committed;
#开始手动事务
#start transaction;
#更新数据
update classInfo
set className='111'
where classId=1;
#回滚事务
rollback;

#事务b:在另外一个脚本中上运行
#set session transaction isolation level read committed;
#select * from classInfo where classId=1
*/

Repeatable Read（可重复读）:
在该隔离级别下，在同一事务中多次查询数据都能保证一致，可以避免不可重复读问
题，但是可能导致幻读等问题。
 这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，
会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom
Read）

#事务隔离级别:repeatable read(可重复读)
#可重复读解决了不可重复读问题，但是会导致幻读问题
#幻读问题的实现步骤：
#1.事务a,开启手动事务,查询某个班表不存在的班级,例如编号10的班级
#2.事务b,开启手动事务,直接插入编号为10的班级数据，并提交事务
#3.事务a,插入编号为10的班级数据
#4.此时会触发主键重复异常,对于事务a而言，上一次明明查询的10是不存在的
# 但是在插入的时候，却出现错误，就象发生了幻觉一样

#事务a
#设置事务隔离级别为可重复读
set session transaction isolation level repeatable read;
#开启手动事务
start transaction;
#查询编号为10的班级
select * from classInfo where classId=10;
#插入班级编号为10的班级
insert into classInfo
(classid,className)
values
(10,'AAA10');
#提交事务
commit;

#事务b(在另外一个脚本中上运行)
set transaction isolation level repeatable read;
#开启手动事务
start transaction;
#插入编号为10的班级
insert into classInfo
(classId,className)
values
(10,'AAA10');
#提交事务
commit;

/*
解决了不可重复读的问题，在两次读的事务中，两次查询的结果都是一样的
#事务a
#设置事务隔离级别为提交读
set session transaction isolation level repeatable read;
#开启手动事务
start transaction;
#在事务中第一次查询数据
select * from classInfo where classId = 1;
#在事务中第二次查询数据
select * from classInfo where classId =1;
#提交事务
commit;

#事务b(在另外一个脚本中执行)
set session transaction isolation level repeatable read;
start transaction;
update classInfo
set className='111'
where classId = 1
commit;
*/

Serializable（可串行化）:
 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读
问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量
的超时现象和锁竞争。

#事务隔离级别:序列化(serializable)
#序列化可以解决事务并发的所有问题，但是事务要排队一个一个执行，
#导致系统的效率非常低

#序列化级别解决幻读问题

#事务a
#设置事务隔离级别为可重复读
set session transaction isolation level serializable;
#开启手动事务
start transaction;
#查询编号为10的班级
select * from classInfo where classId=10;
#插入班级编号为10的班级
insert into classInfo
(classid,className)
values
(10,'AAA10');
#提交事务
commit;

#事务b(在另外一个脚本中上运行)
set transaction isolation level serializable;
#插入编号为10的班级
insert into classInfo
(classId,className)
values
(10,'AAA10');
#提交事务
commit;

3.4.3.案例说明

脏读：

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
场景：公司发工资了，领导把5000元打到Tom的账号上，但是该事务并未提交，而
Tom正好去查看账户，发现工资已经到账，账户多了5000元，非常高兴，可是不幸的
是，领导发现发给Tom的工资金额不对，是2000元，于是迅速回滚了事务，修改金额
后，将事务提交，Tom再次查看账户时发现账户只多了2000元，Tom空欢喜一场，从
此郁郁寡欢，走上了不归路……

不可重复读：

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并
提交，导致事务A多次读取同一数据时，结果不一致。
场景：Tom拿着工资卡去消费，酒足饭饱后在收银台买单，服务员告诉他本次消费
1000元，Tom将银行卡给服务员，服务员将银行卡插入POS机，POS机读到卡里余额
为3000元，就在Tom磨磨蹭蹭输入密码时，他老婆以迅雷不及掩耳盗铃之势把Tom工
资卡的3000元转到自己账户并提交了事务，当Tom输完密码并点击“确认”按钮后，
POS机检查到Tom的工资卡已经没有钱，扣款失败，Tom十分纳闷，明明卡里有钱，于
是怀疑POS有鬼，和收银小姐姐大打出手，300回合之后终因伤势过重而住进ICU，
Tom从此郁郁寡欢，从此走上了不归路…
分析：上述情况即为不可重复读，两个并发的事务，“事务A：POS机扣款”、“事务B：
Tom的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新数据并提交了事务，
而事务A再次读取该数据扣款

幻读：

已知有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数
据，导致A再次读取同一个表, 就会多出几行，简单地说，一个事务中先后读取一个范围
的记录，但每次读取的纪录数不同，称之为幻象读
场景：Tom跟朋友聚餐完毕后开启事务付账，先付了80元烟钱，Tom的老婆正好在
家里查账发现Tom仅仅有80元的消费记录，非常吃惊，心想“老公真是太节俭了，嫁给
他真好！”，而Tom此时再次支付饭钱1000元，即新增了一条1000元的消费记录并提交
了事务，沉浸在幸福中的老婆不敢相信自己的眼睛，又查询了Tom当天工资卡消费明
细，一探究竟，可查出的结果竟然发现又多了一笔1000元的消费，Tom的老婆瞬间怒气
冲天，外卖订购了一个大号的榴莲，傍晚降临，Tom生活在了水深火热之中，只感到膝
盖针扎的痛…
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增
或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表