数据库篇-关系型数据库-MySql-MySql的事务、锁、隔离级别、四范式（三范式）

本文链接：https://blog.youkuaiyun.com/qq_44296841/article/details/143216433

数据库篇-关系型数据库-MySql-MySql的事务、锁、隔离级别、四范式（三范式）

事务

事务的概念

数据库中的事务是指对数据库执行一批操作，在同一个事务当中，这些操作最终要么全部执行成功，要么全部失败，不会存在部分成功的情况。

事务是一个原子操作。是一个最小执行单元。可以甶一个或多个SQL语句组成
在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

事务的特性（ACID)重点

原子性(Atomicity)
事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的。

一致性(Consistency)
一个事务必须使数据库从一个一致性状态变换到另一个一致性状态。

首先回顾一下一致性的定义。所谓一致性，指的是数据处于一种有意义的状态，这种状态是语义上的而不是语法上的。最常见的例子是转帐。例如从帐户A转一笔钱到帐户B上，如果帐户A上的钱减少了，而帐户B上的钱却没有增加，那么我们认为此时数据处于不一致的状态。

从这段话的理解来看，所谓一致性，即，从实际的业务逻辑上来说，最终结果是对的、是跟程序员的所期望的结果完全符合的

隔离性(Isolation)
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

这里先提一下事务的隔离级别：
读未提交：read uncommitted
读已提交：read committed
可重复读：repeatable read
串行化：serializable
持久性(Durability)
一个事务一旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘，修改是永久性的。

隐式事务和显式事务

mysql中事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交或回滚事务。

是否开启隐式事务是由变量autocommit控制的。

所以事务分为隐式事务和显式事务。

隐式事务

事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。

查看变量autocommit是否开启了自动提交

autocommit为ON表示开启了自动提交。

显式事务

2种方式手动控制事务：

方式1：

//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;


-- 回滚事务操作
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

把autocommit还原回去：
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

方式2：

start transaction;//开启事务
//执行事务操作
commit|rollback;

提交事务操作，如下：
mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   2 |
|   3 |
+------+
3 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)

-- 回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   2 |
|   3 |
+------+
3 rows in set (0.00 sec)

savepoint关键字；将一大批操作分为几个部分，然后指定回滚某个部分

我们可以将一大批操作分为几个部分，然后指定回滚某个部分。可以使用savepoin来实现，效果如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint part1;//设置一个保存点
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
Query OK, 0 rows affected (0.00 sec)

mysql> commit;//提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

从上面可以看出，执行了2次插入操作，最后只插入了1条数据。

savepoint需要结合rollback to sp1一起使用，可以将保存点sp1到rollback to之间的操作回滚掉。

只读事务

表示在事务中执行的是一些只读操作，如查询，但是不会做insert、update、delete操作，数据库内部对只读事务可能会有一些性能上的优化。

用法如下：

start transaction read only;
1
示例：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   1 |
+------+
2 rows in set (0.00 sec)

mysql> delete from test1;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   1 |
+------+
2 rows in set (0.00 sec)

只读事务中执行delete会报错。

事务的隔离级别

事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。（或者说为了解决并发控制可能产生的异常问题，数据库定义了四种事务的隔离级别）

隔离级别分为4种：

读未提交：READ-UNCOMMITTED
读已提交：READ-COMMITTED
可重复读：REPEATABLE-READ
串行：SERIALIZABLE
上面4中隔离级别越来越强，会导致数据库的并发性也越来越低。

查看隔离级别

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name     | Value      |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)

设置隔离级别

分2步骤，修改文件、重启mysql，如下：

修改mysql中的my.init文件，我们将隔离级别设置为：READ-UNCOMMITTED，如下：

隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行

transaction-isolation=READ-UNCOMMITTED

以管理员身份打开cmd窗口，重启mysql，如下：

C:\Windows\system32>net stop mysql
mysql 服务正在停止…
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

各种隔离级别中会出现的问题

隔离级别	脏读可能性	不可重复读可能性	幻读可能性
READ-UNCOMMITTED	有	有	有
READ-COMMITTED	无	有	有
REPEATABLE-READ	无	无	有
SERIALIZABLE	无	无	无

总结

读未提交( Read Uncommitted )

读未提交是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

读已提交( Read Committed )

在 Read Committed 隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

可重复读( Repeatable Read )

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

可串行化( Serializable )

Serializable 是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。
==虽然 Serializable 隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。==如果没有特别重要的情景，一般都不会使用Serializable隔离级别。
默认隔离级别：如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用 InnoDB，默认的隔离级别是Repeatable Read。

关于隔离级别的选择

需要对各种隔离级别产生的现象非常了解，然后选择的时候才能游刃有余
隔离级别越高，并发性也低，比如最高级别SERIALIZABLE会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低。
具体选择哪种需要结合具体的业务来选择。
读已提交（READ-COMMITTED）通常用的比较多。

锁

需要的时候再补充，主要是内容有点多，复杂

四范式（三范式）

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式）。

数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求，满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。

一般来说，在关系型数据库设计中，最高也就遵循到BCNF，普遍还是3NF。但也不绝对，有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。

第一范式（1NF）

第一范式主要是确保数据表中每个字段的值必须具有原于性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。

我们在设计某个字段的时候，对于字段X来说，不能把字段X拆分成字段X-1和字段X-2。

总结

1NF告诉我们字段属性需要是原子性的，数据不可再分

第二范式（2NF）

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。

例如：比赛表 play_game ，里面有球员编号，球员姓名，球员年龄，比赛编号，比赛时间和比赛场地等属性，这里候选键和主键都是球员编号，比赛编号，可以通过候选键或者主键决定关系：；

（球员编号，比赛编号）-》（姓名，年龄，比赛时间，比赛场地，得分）

但是这里产生了非完全依赖问题：

（球员编号）-》（姓名，年龄）

（比赛编号）-》（比赛时间，比赛场地，得分）

非完全依赖候选键产生的问题：

数据冗余：如果一个球员可以参加m场比赛，那么球员的姓名和年龄就重复了m-1次。一个比赛也可能会有n个球员参加，比赛的时间和地点就重复了n-1次。
插入异常：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
删除异常：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

第三范式（3NF）

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。（即，不能存存非主属性A依赖于非主属性B，非主属性B依赖于主键C的情况，即存在“A→B→C”的决定关系）通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

这里的主键可以拓展为候选键。
不满足第三范式，容易产生大量数据冗余