事务

1.事务简介

 在数据库中，事务是一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务(例如：银行账户转账业务，该业务就是一个最小的工作单元)，一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成，事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句。

转账操作理解事务

关于银行账户转账操作，账户转账是一个完整的业务（一个账户扣款，一个账户加钱），最小的单元，不可再分，也就是说银行账户转账是一个事务。

以下是银行账户表bank_account(账号、余额)，进行转账操作

| account_num

account_balance
1	800

| | 2 | 300

**转账操作：账户1向账户2转200元

update bank_account set account_balance=600 where account_num=1;
update bank_account set account_balance=500 where account_num=2;

以上两台DML语句必须同时成功或者同时失败，（不可能一个账户扣款成功，而另一个账户没有加钱）最小单元不可再分，当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存中完成的；当第二条DML语句执行成功后，和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，则清空所有的历史操作记录，要完成以上的功能必须借助事务。

2.事务四大特征

一般来说，事务是必须满足4个条件ACID

1. 原子性(A)：事务是最小单位，不能再分

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

2. 一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)

3. 隔离性(I)：事务A和事务B之间具有隔离性（A的成功失败不会影响B，同理B也不会影响A）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比u人：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）

4. 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3.关于事务的一些术语

事务相关的两条重要的SQL语句(TCL)

commit;  --提交
rollback; --回滚

4.事务开启的标志？事务结束的标志？

5.事物与数据库底层数据

在事物进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事物结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据。

6.在MySQL中，事务提交与回滚

在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事务，并且提交了事务

开启手动提交事务

how variables like 'autocommit';
# 开启自动提交
set @@autocommit=1;
# 关闭自动提交
set @@autocommit=0;

提交操作(事务成功)

回滚操作(事务失败)

7.事务特性—隔离性

隔离性有隔离级别(4个)

读未提交：read uncommitted
读已提交：read committed
可重复读：repeatable read
串行化：serializable

数据库事务隔离级别— 脏读、幻读、不可重复读

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

1、 read uncommitted

事物A和事物B，事物A未提交的数据，事物B可以读取到
- 这里读取到的数据叫做“脏数据”
- 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

2、read committed

事物A和事物B，事物A提交的数据，事物B才能读取到
- 这种隔离级别高于读未提交
- 换句话说，对方事物提交之后的数据，我当前事物才能读取到
- 这种级别可以避免“脏数据” - 这种隔离级别会导致“不可重复读取”
- Oracle默认隔离级别

3、repeatable read

事务A和事务B，事务A提交之后的数据，事务B读取不到
- 事务B是可重复读取数据 - 这种隔离级别高于读已提交
- 换句话说，对方提交之后的数据，我还是读取不到
- 这种隔离级别可以避免“不可重复读取”，达到可重复读取
- 比如1点和2点读到数据是同一个
- MySQL默认级别
- 虽然可以达到可重复读取，但是会导致“幻像读”

4、serializable

事务A和事务B，事务A在操作数据库时，事务B只能排队等待
- 这种隔离级别很少使用，吞吐量太低，用户体验差
- 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

设置事务隔离级别

方式一:
可以在my.ini（windows）或my.cnf（linux）文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。

– READ-UNCOMMITTED
– READ-COMMITTED
– REPEATABLE-READ
– SERIALIZABLE
•   例如：
[mysqld]
transaction-isolation=READ-COMMITTED

方式二:
通过命令动态设置隔离级别
隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。
其语法模式为：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
        其中的<isolation-level>可以是：
    –   READ UNCOMMITTED
    –   READ COMMITTED
    –   REPEATABLE READ
    –   SERIALIZABLE
    •   例如：
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别的作用范围及设置

事务隔离级别的作用范围分为两种：
–   全局级：对所有的会话有效 
–   会话级：只对当前的会话有效
--例如，设置会话级隔离级别为READ COMMITTED ：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
或：
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
--设置全局级隔离级别为READ COMMITTED ：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

查看隔离级别


事务隔离级别的作用范围分为两种：
–   全局级：对所有的会话有效 
–   会话级：只对当前的会话有效 
1.查看当前会话隔离级别
select @@tx_isolation;
2.查看系统当前隔离级别
select @@global.tx_isolation;

脏读、幻读、不可重复读

1.脏读：（读取未提交数据）

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

2.不可重复读:（前后多次读取，数据内容不一致）

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。

3.幻读:（前后多次读取，数据总量不一致）

事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测获取到的数据如同鬼影一般。

例子1：

例子2：

在事务1中，查询User表id为1的是用户否存在，如果不存在则插入一条id为1的数据。
```
select * from User where id = 1;
```
在事务1查询结束后，事务2往User表中插入了一条id为1的数据。
```
insert into `User`(`id`, `name`) values (1, 'Joonwhee');
```
此时，由于事务1查询到id为1的用户不存在，因此插入1条id为1的数据。

insert into ` User`(`id`, `name`) values (1, 'Chillax');

但是由于事务2已经插入了1条id为1的数据，因此此时会报主键冲突，对于事务1 的业务来说是执行失败的，这里事务1 就是发生了幻读，因为事务1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。这里要灵活的理解读取的意思，第一次select是读取，第二次的insert其实也属于隐式的读取，只不过是在mysql的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

4.不可重复读和幻读到底有什么区别呢？

（1）不可重复读是读取了其他事务更改的数据，针对update操作
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
（2）幻读是读取了其他事务新增的数据，针对insert与delete操作
解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过幻读是重点在插入和删除，不可重复读重点在修改

数据库设计

为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

数据冗余,存储空间浪费
数据更新和插入的异常
程序性能差

良好的数据库设计 :

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

收集信息
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
标识实体[Entity]
- 标识数据库要管理的关键对象或实体,实体一般是名词
- 标识每个实体需要存储的详细信息[Attribute]
- 标识实体之间的关系[Relationship]

三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

信息重复
更新异常
插入异常
- 无法正确表示信息
删除异常
- 丢失有效信息

三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
而通常我们用的最多的就是第一范式（1NF）、第二范式（2NF）、第三范式（3NF），也就是本文要讲的“三大范式”。

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

举例说明：
mysql第六节 - 图8

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

mysql第六节 - 图9

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
举例说明：
mysql第六节 - 图10

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，
但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，
这样就不满足第二范式的要求，调整如下，需分成两个表：