数据库系统原理课程总结8——备份与日志初步、并发模拟实验

一、备份与日志初步实验

1) 了解你所使用的数据库平台的单表数据备份和整库备份方法，进行相应备份操作，并尝试利用备份数据在另一个机器上恢复数据，并在实验报告中描述上述过程。

答：首先，在MySQL中使用mysqldump将数据库的单表数据以sql文件的形式存储起来，指令的格式如下图，为用户名，密码，数据库，列表，存储文件的绝对路径，这里程序的warning是针对密码在指令中直接出现的提醒，不对结果产生影响：

这里需要说明的一点是不能够登录MySQL之后再使用mysqldump来进行数据的备份，因为这样执行程序会报错，上网查询了一下认为应该是版本的问题，MySQL和mysqldump之间如果存在版本不对应的话是无法在MySQL里面完成数据备份功能的。
在实行上面的语句之后，可以再对应路径下看到Sql文件：

这个文件可以通过文本编译器或者MySQL workbench打开：

通过导入这一文件可以恢复我们备份的数据：

这里我使用的是删除备份的列表来查看恢复功能：

这里已经完成了列表的删除，接下来导入文件，恢复备份数据：

再对列表进行搜索，发现spj列表已经恢复成功：

2) 掌握数据库日志的概念，并说明数据备份、日志与故障恢复之间的关系。

答：数据库日志概念：在数据库系统中，对数据的任何更新操作（如：增加、修改、删除），都要把相关操作的命令、执行时间、数据的更新等信息保存下来。这些被保存的信息就是数据库日志。也就是说，数据库日志是数据库系统中所有更新活动的操作序列。数据库日志是系统正常运行、保持数据一致性的重要手段。
mysql数据库日志分为四种：bin log、error log、general query log、slow query log

bin log：MySQL的bin log日志作用是用来记录mysql内部增删改等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库查询的语句如show，select开头的语句，不会被binlog日志记录。binlog日志只要用于数据库的增量恢复，以及主从复制。

error log：MySQL的错误日志（error log）记录MySQL服务进程mysqld在启动/关闭或运行过程中遇到的错误信息。

general query log：普通查询日志（general query log）：记录客户端连接信息和执行的SQL语句信息

slow query log：慢查询日志（slow query log）：记录执行时间超出指定值（long_query_time）的SQL语句。

数据备份、日志与故障恢复之间的关系：
随着制作备份的种类和频繁程度的不同，数据库发生故障后其恢复的可行性、难度与所花费的时间也不同。
数据库故障是指数据库运行过程中影响数据库正常使用的特殊事件。数据库故障有许多类型，最严重的是介质失败（如磁盘损坏），这种故障如不能恢复将导致数据库中数据的丢失。数据库故障类型有：语句失败、用户进程失败、实例失败、用户或应用错误操作、介质失败如硬盘失败，硬盘中的数据丢失等。由于故障类型的不同，恢复数据库的方法也不同。通过装载备份来恢复数据库既是常用的恢复手段，也是恢复介质失败故障的主要方法。数据库日志可以帮助我们定位mysql内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。

3. 查阅资料，在你所使用的数据库中找到能记录数据修改操作的日志文件，针对某个表执行插入或修改操作，请在相应日志文件中找对应的插入或修改操作日志记录，至少解释其中的一条日志数据样例。
   答：输入show variables like '%general%'语句，程序自动输出是否开启日志，并且给出日志文件相对路径，开始的时候，MySQL5.7是不打开日志的，可以通过MySQL中的在my.ini文件进行配置实现日志功能的管理：
   

   完成配置之后再执行上述语句，可以看到如下结果：这里因为日志文件和server在同一个目录里面，所以只有文件名称。
   

   通过相对路径找到了日志文件后执行语句并打开日志：
   

   日志中和这句程序语句相关的部分是如下图片中的内容（因为触发器的原因，所以有update语句）：
   

   日志解释：以倒数第二条记录为例，第一个参数表示时间，字母T代表的是时间元素的开始。. 后面的是完整的us数，Z表示 UTC（标准时间）如果后面加+、- 号是表示时区的变换如UTC+8就是北京时间。第二个参数ID表示日志号，该日志的日志号就是11.第三个参数表示的是命令类型，这里是Query类型。最后一个参数就是详细的命令内容。整个一行，记录了我在某一时刻输入的插入信息的指令。

二、 并发控制实验

1） 通过取消所用DBMS的查询分析器的自动提交功能，创建两个不同用户，分别登录查询分析器，同时打开两个客户端；

首先查看MySQL的自动提交功能情况，发现自动提交已打开：

关闭自动提交功能并且再次查看自动提交功能情况：

创建两个不同的用户：

打开两个客户端：

2） 通过SQL语言设计具体例子展示不同隔离级别的应用场景，验证各种隔离级别的并发控制效果，即是否存在并发操作带来的数据不一致问题，包括丢失修改、不可重复读和读“脏”数据等。

答：

首先声明一下MySQL中的各个隔离级别：

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

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

3.Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

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

对于并发操作导致的问题声明：

1.脏读：在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。这种读取到另一个事务未提交的数据的现象就是脏读。

2.不可重复读：事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读。

3.丢失修改：丢失修改分为两类，第一类事务A和事务B都对数据进行更新，但是事务A由于某种原因事务回滚了，把已经提交的事务B的更新数据给覆盖了。这种现象就是第一类更新丢失。第二类跟第一类更新丢失有点类似，也是两个事务同时对数据进行更新，但是事务A的更新把已提交的事务B的更新数据给覆盖了。这种现象就是第二类更新丢失。

4.幻读：事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新

实验任务：通过SQL语言设计具体例子展示不同隔离级别的应用场景，验证各种隔离级别的并发控制效果：
首先对目前的隔离级别进行查看，这里因为使用的是MySQL5.7版本，所以使用的是select @@tx_isolation，如果版本在8.0以上的话，就需要使用select @@transaction_isolation：

可以看到，这里MySQL中默认的隔离级别是第三级别的可重复读。
接下来，对于用户的隔离级别进行修改并且尝试对不同隔离级别下的并发错误进行查看：
修改隔离级别的语句为：

set session transaction isolation level read uncommitted;

在执行完语句之后，再次进行隔离级别的查看，发现已经成功修改。
接下来，对各个隔离级别进行应用实验：

1.Read Uncommitted（读取未提交内容）：

这里在准备进行数据的查询和修改的时候出现了错误：

这说明在生成用户之后再一些权限上存在错误，所以使用root登录数据库进行如下修改：

User_2同理。
重新进行上述步骤，设置好两个用户的隔离级别之后进行并发问题查看：

User_1中修改信息

User_2中获得修改过的信息

User_1回滚，user_2出现脏读
考虑到如果出现脏读，那么不可重复读也是一定会发生的，如下图所示，右边的用户2中两次查询出现了数据不一致的情况：

丢失修改，可以看到，用户2的修改指令必须要等待到用户1的进程结束之后才可以执行的，所以读未提交可以解决丢失修改：

幻读，在用户1的进程中，用户2向数据库插入了一条数据，用户1进程里的两次查询得到结果不同，说明读未提交不能解决幻读问题：

2.Read Committed（读取提交内容）
首先进行隔离级别的配置：

下图是通过读已提交隔离级别进行的查询实验：在实验过程中，我们在用户1处进行了修改之后再用户2处进行查询，查询到的结果是没有发生修改的初始值，在用户1的操作commit了之后，用户2再次进行查询，这一次的结果才是修改后的数据，这样就成功的避免了脏读，但是同时我们也会发现前后两次查询的数据不一样，所以我们可以得出的结论是第二级别的隔离级别读已提交可以避免脏读但是不可以避免不可重复读。

这里对于读已提交隔离级别进行丢失修改的实验查看，首先通过用户1对数据库进行修改，在commit之前通过用户2也对这一个数据进行修改，同时实验发现，用户2的修改语句会一直等待用户1的commit而不执行，直到超过MySQL中设定的运行时间后报错。这样的设定可以避免程序的第一类丢失。

读已提交同样不能解决幻读问题，在用户2插入并且提交后用户1进行查询可以看到数据的修改：

3.Repeatable Read（可重读）
首先还是进行隔离级别的配置：

在可重复读这一隔离级别中，可以看到的是脏读和不可重复读两个并发错误都被避免了，在用户2commit之前，用户2从数据库中调用的都是一开始数据库的初始数据，只有两个进程都结束了，再进行数据查询的时候才会看到数据的修改。所以在这一隔离级别中，脏读和不可重复读都不会发生。

对于丢失修改并发错误的避免，可重复读和读已提交两个隔离级别擦爱用了相同的方法，也就是只有在用户1的修改结束并且提交commit了之后，用户2的修改才会生效，否则会一直停留知道超时报错。
下图中的用户2的界面中可以看到修改语句用户接近8s就是因为需要在用户1处运行commit之后这一修改语句才会运行：

同时，进行幻读并行错误的检查，这里我们发现，在用户1的进程中用户2进行了insert插入，但是用户1的显示依旧没有变化，看起来似乎可重复读的隔离级别可以处理幻读错误：

但是这就带来了一个问题，最后的隔离级别可串行化又是为了什么呢？所以通过上网学习，我了解到，可重复读中其实只是在读数据的时候解决了幻读问题，而在其他情况下依旧会出现幻读错误，如下图所示就是可重复读下的标准幻读错误：

可以看到，用户1在对ebook进行更新操作的时候，程序有一个很明显的停顿，只有用户2完成commit之后用户1的修改语句才会继续进行，同时，我们的修改结果参考之前的查询，应该只有一条数据被修改，但是最终的结果却是两条数据被修改，其中包括一条用户2输入的语句，这就说明其实在用户1的进程中间其实已经将这一数据存入数据库了，只是读数据的时候没有显示。这其实也已经属于幻读错误了。

4.Serializable（可串行化）
还是先进行隔离级别的配置：

首先还是进行脏读和不可重复读的检测，这里不同于之前三种，在用户1没有提交commit之前，用户2的查询请求也被停止了，直到用户1提交commit之后用户2的进程才正常的运行。所以也可以看到用户2的查询语句的运行时间会很长。这样的机制同时处理了脏读和不可重复读。

接下来是丢失修改，这里的操作和之前可以处理丢失修改的几类隔离级别一样，都是让一个停止，等待另一个，通过观察运行时间也可以得到类似的结论：

最后是幻读：
可串行化彻底的解决了幻读的各种错误，我们可以再下图中看到，在可串行化中，用户2对于数据的插入是需要等待用户1提交commit之后再进行的，而同样的，用户1在更新完成之后的后续查询操作则是需要等待用户2进程的结束。通过双向的钳制确保幻读问题的充分解决：

在用户1中我们看到用户2插入的数据没有被用户1进程中的更新语句影响，说明不只是在读数据的时候解决了幻读，在用户1的进程结束之后，用户2的插入数据才进入的数据库：

最后的总结：
SQL 标准定义了四种隔离级别，MySQL 全都支持。但是随着隔离强度逐渐增强，性能也在逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。
四种隔离级别对应并发错误的情况如下表：