数据库设计和事务

数据库设计和事务

---

1.数据库设计三大范式

范式：就是关系数据库规范程度的级别，表示一个数据库之间的所有字段之间的练习的合理性。

• 满足组织和存储的前提下使数据结构冗余最小化
• 范式级别越高，表的级别就越标准
  范式分类：
• 第一范式 1NF
• 第二范式 2NF
• 第三范式 3NF
  还有BCNF、4NF、5NF

第一范式确保每一列的原子性，不可再分。每一列都是最小单位，则满足第一范式

第二范式满足第一范式，并且确保每列都与主键相关，即满足第二范式
第二范式处理冗余数据的删除问题。当某张表的信息依赖于该表中其他的不是主键部分的列的时候，通常会违反第二范式

第三范式在满足第二范式的基础上，确保每列都与主键直接相关，而不是简介相关。

2.事务管理

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。 即一组sql中哪怕有一条失败也会失败

从开启到提交为一个事务。 由此可见，一个事务对应一组业务。一个事务中间可以有一条sql,多条sql。 所以 一个业务开始之前 开启事务 一个业务结束之后 提交事务。如果中途出错，可以回滚事务

#start transaction;开始事务
#rollback;遇到错误回滚
#commit;提交事务

如果不写start transaction/begin;commit; 此时事务默认开启自动提交；
事务的基本要素(ACID)：
**原子性：**事务开始后，要么全做要么全不做，不会执行到一半结束，如果执行期间出错会回滚到执行前的数据状态。
**一致性：**事务执行前后不会破坏数据的完整性约束，事务操作前后操作数据是一致的
**隔离性：**事务执行不能被其他事务干扰，一个事务执行期间操作的数据对其他数据是隔离的，并发执行的各个事物之间不能互相干扰。
**持久性：**一个事务一旦提交，它对数据库数据的改变就是永久性的。

2.1事务的并发问题

1. 脏读：事务A读取了事务 B更新的数据，事务B回滚操作，事务A读取的数据就跟数据库不一致
2. 不可重复读：事务A先查询一条数据，然后事务B修改这条数据，事务A再去查询，两次结果不一致
3. 幻读：事务A去查询一条不存在的数据，然后事务B去插入这条数据，事务A再去插入就会报错，对于事务A，第一次读的结果就像幻觉

2.2MySQL事务隔离级别

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

mysql> SELECT @@tx_isolation;--5.7版本
mysql> SELECT @@transaction_isolation; --8.0版本

#设置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(未提交读):

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读

#1.事务a，手动事务，更新数据
#2.事务b，在事务a没有提交的情况下,查询a更新的数据
#3.事务a, 回滚事务
#4.此时，事务b查询的结果和此时数据库中的数据是不一致的，称为脏读
#事务a #设置事务为未提交读
set session transaction isolation level read uncommitted; 
#开始手动事务
start transaction; 
#更新数据
update bank set money=500 where bid=1; 
#回滚事务
rollback;
#事务b #设置事务为未提交读
set session transaction isolation level read uncommitted; 
#开始手动事务
start transaction; 
#查询数据
SELECT money FROM bank WHERE bid = 1;
#回滚事务
rollback;

Read Committed（提交读）：
这种隔离级别 也导致所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果

#1.事务a,开启手动事务,先查询一次编号为1的班级数据
#2.事务b,开启手动事务，更新编号为1的班级名称,并提交了事务
#3.事务a,在同一个事务中再查询编号为1的班级数据
#4.此时a事务中的两次查询结果不一致，称为不可重复读
#事务a #设置事务为提交读
set session transaction isolation level read committed; 
#开始手动事务
start transaction; 
#第一次查询数据
SELECT money FROM bank WHERE bid = 1;
#第二次查询数据
SELECT money FROM bank WHERE bid = 1;
#提交事务
COMMIT;
#事务b
set session transaction isolation level read committed; 
#开始手动事务
start transaction; 
#更新数据
update bank set money=500 where bid=1; 
#提交事务
COMMIT;

Repeatable Read（可重复读）:
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）

#事务隔离级别:repeatable read(可重复读) 3 #可重复读解决了不可重复读问题，但是会导致幻读问题
#幻读问题的实现步骤：
#1.事务a,开启手动事务,查询某个班表不存在的班级,例如编号10的班级
#2.事务b,开启手动事务,直接插入编号为10的班级数据，并提交事务
#3.事务a,插入编号为10的班级数据
#4.此时会触发主键重复异常,对于事务a而言，上一次明明查询的10是不存在的
# 但是在插入的时候，却出现错误，就象发生了幻觉一样
#事务a #设置事务为可重复读
set session transaction isolation level REPEATABLE READ; 
#开始手动事务
start transaction; 
#查询不存在的数据
SELECT money FROM bank WHERE bid = 3;
#插入
INSERT bank VALUE (3,'irh',10000);
#提交事务
COMMIT;
#事务b
set session transaction isolation level REPEATABLE READ; 
#开始手动事务
start transaction; 
#更新数据
INSERT bank VALUE (3,'irh',10000);
#提交事务
COMMIT;

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

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

#事务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;

---