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账户中的添加操作才能执行成功,并输出 如下语句: