数据库四 事务管理机制

【实验名称】：

事务管理

【实验目标】：

1. 通过实验理解事务机制，包括事务的提交、回滚以及死锁；
2. 事务的并发控制；
3. 理解脏读

【实验具体要求】：

1. 1. 利用事务完成如下修改：将教师号“1”修改为“4”，同时修改所有使用该教师号作为外键关联的表中的该字段的值；(不能使用on update约束进行修改，需要先删除外键才能成功)
   2. 创建一个银行账户表，该表包含两列，第一列为账户id，第二列为账户金额。在表中创建两个账户，一个为存储账户，一个为支出账户。存储账户的id为1，金额为100，支出账户的id为2， 金额为50。以上创建的表适用于以下所有的事务实验；

（1）了解事务中回滚的概念并进行以下操作；

1. 1. 开启一个新事务，查询账户表中的所有数据，然后删除存储和支出账户，查询账户表中的所有数据，记录发生的现象并说明原因；
   2. 将开启的事务回滚，然后再查询账户表中的数据，记录发生的现象并说明原因。

   （2）了解事务提交的概念并进行以下操作；

1. 1. 开始一个新事务，删除存储账户，然后查询该账户的所有数据，记录发生的现象并说明原因；
   2. 提交该事务，然后尝试回滚刚提交的事务。观察表中的数据是否会发生变化，记录发生的现象并说明原因。

   （3）了解事务阻塞和死锁的概念，并进行以下操作；

1. 1. 创建一个新的账户表（同上）；
   2. 利用命令行窗口分别开启两个事务会话，并分别开启新的事务；
   3. 选择一个窗口将支出账户的金额更新为100，然后在第二个窗口当中将支出账户的金额更新为2400，观察这种更新是否会成功，并说明原因；
   4. 在第一个窗口当中将事务进行提交，在第二个窗口当中进行事务提交，查看支出账户金额
   5. 再次分别再每个窗口当中开启一个新的事务；
   6. 在第一个窗口当中将存储账户的金额更新为2400，在第二个窗口中将支出账户的金额更新为2000；
   7. 然后再在第二个窗口中将存储账户的金额更新为2400，并在第一个窗口中将支出账户的金额更新为2000。两个窗口中的事务会发生什么情况，记录该情况并说明原因；

1. 1. 演示脏读

（1）创建一个账户表，含有姓名，金额两列，插入两行， 张三 100， 李四 100

（2）分别在两个窗口中，设置隔离级别为读未提交

（3）A窗口假设是张三在操作，张三开启事务，将自己的金额减少了50，李四的金额添加50，让李四查看，B窗口的李四看到钱到账了，满心欢喜。

（4）A窗口的张三回滚操作，过了几天李四再次查看自己的账户，发现不对，钱没到账。

（5）若是将设置隔离级别为不可重复读，（3）中李四看到的是什么？

【实验步骤】：

1.利用事务完成如下修改：将教师号“1”修改为“4”，同时修改所有使用该教师号作为外键关联的表中的该字段的值；(不能使用on update约束进行修改，需要先删除外键才能成功)

2.创建一个银行账户表，该表包含两列，第一列为账户id，第二列为账户金额。在表中创建两个账户，一个为存储账户，一个为支出账户。存储账户的id为1，金额为100，支出账户的id为2， 金额为50。以上创建的表适用于以下所有的事务实验；

（1）了解事务中回滚的概念并进行以下操作；

1)  开启一个新事务，查询账户表中的所有数据，然后删除存储和支出账户，查询账户表中的所有数据，记录发生的现象并说明原因；

再次select,发现表没有了

2)  将开启的事务回滚，然后再查询账户表中的数据，记录发生的现象并说明原因。

发现两个账户并没有被删除

而当注释掉rollback后，发现账户被删除了

因为rollback回滚可以回到初始状态。

（2）了解事务提交的概念并进行以下操作；

1)  开始一个新事务，删除存储账户，然后查询该账户的所有数据，记录发生的现象并说明原因；

在事务内，事务外都进行查询，发现操作成功

因为成功提交了事务，没有进行回滚，成功删除存储账户。

2)  提交该事务，然后尝试回滚刚提交的事务。观察表中的数据是否会发生变化，记录发生的现象并说明原因。

在事务内查询，发现操作成功

在事务外再进行查询

发现并没有操作成功，因为发生了回滚，回到了初始状态。

（3）了解事务阻塞和死锁的概念，并进行以下操作；

1)  创建一个新的账户表（同上）；

2)  利用命令行窗口分别开启两个事务会话，并分别开启新的事务；

查看数据库，并选择student_system库

3)  选择一个窗口将支出账户的金额更新为100，然后在第二个窗口当中将支出账户的金额更新为2400，观察这种更新是否会成功，并说明原因；

窗口1

查看发现执行成功

窗口2

发现报错了，错误提示Lock wait timeout exceeded; try restarting transaction的异常，很明显，是因为这条语句被锁住了。

这时候需要对第一个窗口的事务提交，停止这个事务的运行，才能解开死锁。

窗口1

窗口2

发现可以执行成功了。

4)  在第一个窗口当中将事务进行提交，在第二个窗口当中进行事务提交，查看支出账户金额

窗口1

窗口2

发现最终结果为第二个窗口的更新结果，为2400

5)  再次分别再每个窗口当中开启一个新的事务；

6)  在第一个窗口当中将存储账户的金额更新为2400，在第二个窗口中将支出账户的金额更新为2000；

窗口1

发现窗口1更新失败，因为之前在第二问的时候，删掉了存储账户

现在插入存储账户的数据

再进行更新

窗口1

这时候进行查询，可以发现更新成功

窗口2

先进行查询，发现并没有成功更新窗口2中的数据

尝试进行更新id=2 的money

发现报错了，错误提示Lock wait timeout exceeded; try restarting transaction的异常，很明显，是因为这条语句被锁住了。

①第一次尝试：

这时候先对窗口1的事务commit，

然后再更新窗口二，运行成功。

7)  然后再在第二个窗口中将存储账户的金额更新为2400，并在第一个窗口中将支出账户的金额更新为2000。两个窗口中的事务会发生什么情况，记录该情况并说明原因；

出现死锁，错误提示Lock wait timeout exceeded; try restarting transaction的异常

3.3 演示脏读

（1）创建一个账户表，含有姓名，金额两列，插入两行， 张三 100， 李四 100

（2）分别在两个窗口中，设置隔离级别为读未提交

（3）A窗口假设是张三在操作，张三开启事务，将自己的金额减少了50，李四的金额添加50，让李四查看，B窗口的李四看到钱到账了，满心欢喜。

窗口1

窗口2

（4）A窗口的张三回滚操作，过了几天李四再次查看自己的账户，发现不对，钱没到账。

窗口1

窗口2

（5）若是将设置隔离级别为不可重复读，（3）中李四看到的是什么？

窗口1

窗口2

窗口1回滚

窗口2显示

【小结】：

一、命令行连接MYSQL

1、连接到本机上的MYSQL命令行

  首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql -u root -p，回车后提示你输密码.
mysql -u root -p

2、连接到远程主机上的MYSQL命令行

  假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
mysql -h110.110.110.110 -u root -p 123;

3、退出MYSQL命令： exit （回车）

二、SQL事务与锁

  事务：保持逻辑数据一致性与可恢复性，必不可少的利器。

  锁：多用户访问同一数据库资源时，对访问的先后次序权限管理的一种机制，没有他事务或许将会一塌糊涂，不能保证数据的安全正确读写。

  死锁：是数据库性能的重量级杀手之一，而死锁却是不同事务之间抢占数据资源造成的。

  事务中的语句要么全部执行，要么全部不执行。

1.开启事务

标记事务开始

BEGIN;

2.回滚

数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。

ROLLBACK;

3.提交

事务已经成功执行，数据已经处理妥当。

COMMIT;

三、sql设置事务隔离级别

SET TRANSACTION一共有以下几种级别：
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}

1.第一种情况：

READ COMMITTED

这句的作用是：

指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。

2.第二种情况：

READ UNCOMMITTED

这句的作用是：

执行脏读或0级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。

3.第三种情况：

REPEATABLE READ

这句的作用是：

锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。

4.第四种情况：

SERIALIZABLE

这句的作用是：

在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。

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