SQL 事务2
1. 前言
在上一小节中,我们介绍了事务的概念和基本使用,探讨了事务的四大特性,本小节我们将更加深入的学习事务。
在实际的生产环境中,偶尔会遇到大量并发访问的情况;大量的并发会导致数据的竞争,从而引起一系列的并发问题。
本小节,我们将一起学习 的 4
种事务隔离机制,以及与之对应的 3
种并发异常。
本小节测试数据如下,请先在中执行,本小节的所有操作若无特殊说明认在 中执行。
DROP TABLE IF EXISTS imooc_user;CREATE TABLE imooc_user( id int PRIMARY KEY, username varchar(), age int);INSERT INTO imooc_user(id,username,age)VALUES (,'peter',),(,'pedro',),(,'jerry',);
2. 并发异常
标准共定义了 3 种并发异常,这三种异常分别是脏读(Dirty Read)、不可重复读(Nnrepeatable Read)和幻读(Phantom Read)。
这 3 种异常比较抽象,我们直接以小例子来讲解。
某一天,小王正在访问,开启了事务,且向 imooc_user 表中插入名为 tom
的,如下:
BEGIN;INSERT INTO imooc_user(id,username,age) VALUES (,'tom',);
此时,小王还未提交事务,但小李却在此时访问了,并且了 imooc_user 表,如下:
SELECT * FROM imooc_user;
小李看到了如下结果:
+----+----------+-----+ | id | username | age | +----+----------+-----+ | 4 | tom | 27 | +----+----------+-----+
小王明明还没有提交事务,但小李却已经看到了小王操作的结果。试想一下,如果此时小王回滚了事务,那么对于小李来说,他就看到了的数据,我们称之为脏读
。
流程图如下:
第二天,小王又在访问了,他查看了 imooc_user 表中 id 为 1
的,如下:
SELECT * FROM imooc_user WHERE id = ;
小王看到的结果是这样的:
+----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | peter | 18 | +----+----------+-----+
此时,小李也来访问,他开启了事务,并且了 id 为 1
的,如下:
BEGIN;UPDATE imooc_user SET age = WHERE id = ;
这个时候,小王又查看了一次 id 为 1
的。
SELECT * FROM imooc_user WHERE id = ;
但是他看到的结果却是这样的:
+----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | peter | 100 | +----+----------+-----+
小王发现自己两次的数据不一样,可是小李的事务还未提交。像小王这样,两次结果不同的情况,我们称之为不可重复读
。
流程图如下:
第三天,小王开始了事务,并向 imooc_user 表中插入了名为 mary
的,如下:
BEGIN;INSERT INTO imooc_user(id,username,age) VALUES (,'mary',);
此时,搞事的小李也来访问,小李了一下 imooc_user 表,如下:
SELECT * FROM imooc_user;
小李看到了如下结果:
+----+----------+-----+ | id | username | age | +----+----------+-----+ | 1 | peter | 100 | | 2 | pedro | 24 | | 3 | jerry | 22 | | 4 | tom | 27 | | 5 | mary | 17 | +----+----------+-----+
小李此时已经看到了新增的 mary 了,但是小王后悔了,他不想创建 mary 了,于是小王回滚了事务:
ROLLBACK;
但小李却看到了 mary,他一度以为自己出现了幻觉,我们把这种情况称之为幻读
。
流程图如下:
我们总结一下这三种异常的特点:
脏读:读到了其它事务还未提交的数据;
不可重复读:两次读到了同一数据的不同结果;
幻读:读到了其它事务新增但未提交的数据,而且新增数据并未成功。
3. 隔离级别
介绍了常见的 3 种并发异常后,我们再来介绍 4 种隔离机制。
事务的四种隔离机制主要是为了上述的三种并发异常,它们之关系如下表所示:
上面的隔离级别由上往下,级别依次会提高,但消耗的也会依次提高。我们总结一下四种隔离级别:
读未提交:允许读未提交数据,可能会发生脏读、不可重复读和幻读异常;
读已提交:只能读已经提交的数据,避免了脏读,但可能会出现不可重复读和幻读;
可重复读:即能保证在事务中多次读取,数据一致,但可能会出现幻读;
可串行化:最高的隔离级别,串行的执行事务,可以避免 3 种异常,但耗损最高。
: Server 和 Oracle 的认隔离级别是读已提交
,而 的认隔离级别是可重复读
。
鱼和熊掌不可而得兼!因此 提供了 4 种事务隔离级别,在数据吞吐能力和数据安全中,你需要作出相应的选择。
通过如下语句你可以设置事务隔离级别:
SET SESSION TRANSACTION ISOLATION LEVEL [level];
其中 level
表示隔离级别,如:READ UNCOMMITTED。
4. 小结
事务隔离级别
以及并发异常
是面试中的重点,请熟练掌握并总结。
在实际开发中,事务
的使用十分频繁,当然你不会手写这些 语句,会有相应的框架来帮你处理,但你仍然需要弄懂它们的原理,当出现问题时,你才可以迅速。