第13次作业-事务管理

 

1，在数据库中使用事务时，必须先开启事务，开启事务语句如下 

 

2，上述语句就用了开启事务,事务开启之后就可以执行 SQL.语句,SQL 语句执行成功 后,需要使用相应语的提交事务,提交事务的语句具休如下。

 

 3，需要注意的是,在 MySQL中直接书写的 SQL 语句都是自动提交的,而事务中的操 作语句都需要使用 COMMIT 语句手动提交,另有事务提交后其中的操作才会生效。 如果不想提交当前事务还可以使用相关语高取消事务(也称回滚),具体语句如下: 

4， 通过上述的讲解,读者对事务有了一个简单的了解,为了让读者更好地学习事务,接 下来通过一个转账的案例来演示如何使用事务。在演示之前,首先需要创建一个名称为 chapter06 的数据库,并且在 chapter06 中创建一个 account 表,插入相应的数据,SQL语 句具体如下：

 

  

5，为了验证数据是否添加成功，可以使用select语句查询account表中的数据，查询结果如下：

 

6，从上述结果可以看出数据添加成功了,接下来使用事务来演示如何实现转账功能。首先开启一个事务,然后通过 UPDATE 语句将 a 账户的 100 元钱转给 b 账户,最后提交事务,具体语句如下:

 

7，上述语句执行成功后，可以使用select语句来查询account表中的余额，查询结果如下：

 

8，为了说明事务的提交方式为手动提交,接下来,在 6. 1.1 节的基础上进行操作,这时 的 a 账户有 900 元钱,b 账户有 1100 元钱,开启一个事务,使用 UPDATE 语句实现由 b 账户向 a 账户转 100 元钱的转账功能,具体语句如下:

 

 

9，上述语句执行成功后,可以使用 SELECT 语句来查询 account 表中的余额,查询结 果如下： 

 

10， 从上述结果可以看出,在事务中实现了转账功能。此时,退出数据库然后重新登录, 并查询数据库中各账户的余额信息,查询结果如下: 

 

  

11，从上述结果可以看出,事务中的转账操作没有成功,这是因为在事务中转账成功后 还没有提交事务就退出数据库了,由于事务中的语句不能自动提交,因此当前的操作貌 被自动取消了。接下来再次执行上述语句,然后使用 commit 语句来提交事务,具体语句 如下：

 

12， 上述语句执行成功后,退出数据库然后再重新登录,使用 SELECT 语句查询数据库 中各账户的余额信息,查询结果如下: 

 

13，为了演示事务的回滚操作,在6.1.2节的基础上进行操作,这时的a账户有 1000 元。 “力的回滚进行详细的讲解。 6账户有 1000 元,开启一个事务,通过 update 语句将 a 账户的 100 元钱转给 b账户,具体 语句如下：

 

14，上述语句执行成功后，使用select语句查询a和b账户的金额，查询结果如下：

 

15，从上述结果可以看出，a账户成功给b账户转账100元钱，如果此时a账户不想给b账户转账了，由于事务还没提交，就可以将事务回滚，具体语句如下：

 

16，rollback语句执行成功后，再次使用select语句查询数据库，查询结果如下：

 

 

 17，1）设置 b 账户中事务的隔离级别 大家都知道 MySQL 的默认隔离级别是 REPEATABLE READ(可重复读),该级别是 可以避免脏读的,因此需要将 b 账户中事务的隔离级别设置为 READ UNCOMMITTED(读 未提交),具体语句如下:

 

 

18，上述语句中,SESSION 表示当前会话,TRANSACTION 就表示事务,ISOLATION 表示隔离,LEVEL 表示级别,READ UNCOMMITTED 表示当前的隔离级别,该语句执 行成功后,使用 SELECT 语句查询事务的隔离级别,结果如下:

 

从上述结果可以看出,b 账户事务的隔离级别已经被修改为 READ UNCOMMITTED, 接下来就可以演示脏读的情况。

19，2）演示脏读 b账户：为了证明出现脏读的情况,首先在 b账户中开启一个事务,并在该事务中查 询当前账户的余额信息,查询结果如下:

 

 20，a账户：在a账户中开启一个事务，并在当前窗口中执行转账功能，具体语句如下：

 

21，需要注意的是,此时不要提交事务,如果提交事务就无法演示出现脏读的情况。 b账户:a账户执行完转账语句后,b账户查询当前账户,此时的查询结果如下：

 

从查询结果可以看出,a 账户已经成功给 b 账户转账了 100 元钱,这是由于 b 账户的 事务隔离级别较低,因此读取了 a账户中还没有提交的内容,出现了脏读的情况,这时b 误以为 a 账户已经转账成功了,便会给 a发货,当 b 发货后 a如果不提交事务将事务回 滚,此时 b就会受到损失。上述情况演示完,最后还需将 a账户中的事务回滚,将 b账户 中的事务提交。

22，3）设置 b 账户中事务的隔离级别 为了防止脏读发生,可以将 b账户中事务的隔离级别设置为 READ COMMITED (读提交),该级别可以避免脏读,具体语句如下: 

 

上述语句执行成功后,b账户的隔离级别已经被设置为 READ COMMITTED。

23，4）验证是否出现脏读 b 账户：为了说明没有出现脏读的情况,首先要在 b 账户中开启一个事务,并在该事 务中查询各账户的余额信息,查询结果如下:

 

24，a 账户:在 a账户中重新开启一个事务,实现转账功能,具体语句如下:

 

25， b 账户:当 a 账户转账成功后,可以在 b 账户中再次查询各账户的余额信息,查询结 果如下：   

 

通过对比两次查询结果可以发现,b账户在同一个事务中的查询结果是一致的,并没 有查询到 a 账户中未提交的内容,因此可以说明 READ COMMITTED 隔离级别可以避 免脏读。最后分别将 a账户中的事务和 b账户中的事务回滚。

26，1）演示不可重复读 b账户：首先在1账户中开启一个事务,然后在当前事务中查询各账户的余额信息, 查询结果如下：

 

27， a 账户:在 a账户中不用开启事务,直接使用 UPDATE 语句执行更新操作即可,具 体语句如下：

 

28，由于 a账户只需要执行修改的操作,不需要保证同步性,因此直接执行 SQL 语句就 可以,执行结果如下所示:

 

29， 使用 SELECT 语句查询 a 账户的余额信息,查询结果如下: 

 

 30，b账户：当·账户中的更新操作执行成功后,在 b账户中再次查询各账户的余额,查 询结果如下：

对比 b 账户两次查询结果可以发现,两次查询结果是不一致的,实际上这种操作是 没错的,但是如果在银行统计报表时,这种情况是不符合需求的,因为我们并不希望在一 个事务中看到的查询结果不一致,这就是不可重复读。上述情况演示成功后,还是要将 b 账户中的事务提交。

 

31，2）设置 b 账户中事务的隔离级别 b 账户:为了防止重复读的情况出现,可以将该事务的隔离级别设置为 REPEATABLE READ(可重复读),具体语句如下:

32，上述语句执行成功后,b 账户事务的隔离级别被设置为 REPEATABLE READ。 3）验证是否出现不可重复读 b 账户:在 b账户中,重新开启一个事务,然后使用 SELECT 语句查询当前账户的 余额,查询结果如下:

 33，a账户：在 a 账户中不开启事务,直接使用 UPDATE 语句执行更新操作,具体 如下：

 

34，使用 SELECT 语句查询各账户的余额信息,查询结果如下:

 

35，b 账户:当 a 账户中的 UPDATE 语句执行成功后,b 账户在当前事务中,再次查询 各账户的余额信息,查询结果如下:

 对比 b账户两次的查询结果可以发现,查询的结果是一致的,并没有出现不同的数 据,因此,可以说明事务的隔离级别为 REPEATABLE READ 时,可以避免重复读的情 况。演示完成后,将 b 账户中的事务提交。

 

36，1）设置 b 账户的隔离级别 b账户：由于前面将事务的隔离级别设置为 REPEATABLE READ(可重复读),这 种隔离级别可以避免幻读的出现,因此需要将事务的隔离级别设置得更低,下面将事务 的隔离级别设置为 READ COMMITTED,具体语句如下:

 

 上述语句执行成功后,b账户事务的隔离级别为 READ COMMITTED。

37，2）演示幻读 询结果如下： b 账户:首先在b账户中开启一个事务,然后在当前事务中查询账户的余额信息,查询结果如下：

 

38， a 账户:在对 a账户进行添加操作之前,使用 SELECT 语句查看当前 a账户中的信 息,执行语句如下所示:

 

 39，接下来对 a账户进行添加操作,a账户不用开启事务,直接执行添加操作即可,具体 语句如下：

 

40， b账户:当 a账户添加记录成功后,可以在 b账户中再次查询账户的余额信息,查询 结果如下：

通过对比b账户设置read committed隔离级别前后，发现第二次查询数据时比第一次查询时多了一条记录,这种情况并不是错误的,但可能不符合实际需求。上述情况演示完成后,将 b账户中的事务提交。

 

41，3）重新设置 b 账户的隔离级别 b账户:为了防止出现幻读,可以将 h 账户的隔离级别设置为 REPEATABLE READ,具体语句如下:

 

 42，上述语句执行成功后,事务的隔离级别被设置为 REPEATABLE READ。 4）验证是否出现幻读 b 账户:在 b账户中重新开启一个事务,并在该事务中查询当前账户的余额信息,查 询结果如下：

 

43，a 账户：在对 a 账户进行添加操作之前,使用 SELECT 语句查看当前 a 账户中的信 息,执行语句如下所示:

44， 接下来对a账户进行添加操作,在a账户中不开启事务,直接执行添加操作,具体语句如下：

 

 45，b 账户：当a账户的添加操作执行成功后,再次查询当前账户的余额信息,查询结果 如下：

 

46，对比b账户的两次查询结果可以看出,在同一个事务中两次的查询结果是一致的, 并没有出现重复读取的情况,因此可以说明当前事务的隔离级别可以避免幻读,最后还 需要使用 COMMIT 语句提交当前事务,提交后的账户查询结果如下所示:

 

4.可串行化 可串行化(SERIALIZABLE)SERIALIZABLE是事务的最高隔离级别,它在每个读的数据行上加上 锁,使之不可能相互冲突,因此会导致大量的超时现象,接下来通过具体的案例来演示, 具体步骤如下。

47，1）设置 b 账户中事务的隔离级别 b 账户：首先将 b 账户的隔离级别设置为 SERIALIZABLE,具体语句如下：

 

48，上述语句执行成功后,当前事务的隔离级别被成功设置为 SERIALIZABLE。 b账户:首先在 b 账户中开启事务,然后使用 SEL.ECT语句查询各个账户的余额信 2)演示可串行化 息,查询结果如下:

 

 

 49，a账户：开启一个事务，并在该事务中执行插入操作，执行结果如下：

 

 

 从上述执行结果可以看出,当b账户正在事务中查询余额信息时,a 账户中的操作是 不能立即执行的。

50，3）提交事务 b账户:当 b账户查询完余额信息后,立即提交当前事务,具体语句如下: comvir; a 账户:当b账户中的事务提交成功后,a账户中的添加操作才能执行成功,并输出 如下语句: