一条SQL的背后故事(三)

SQL是用来和数据库打交道，从数据库中得到我们想要的数据。但是有的时候查询结果却不一定尽如人意，本文将介绍事务并发处理可能存在的三种异常。

 这里需要先介绍一下事务，Mysql 5.5 版本之前默认的存储引擎是 MyISAM，之后修改成了InnoDB ，就是支持了事务（transaction）这一特性，事务具有原子性、一致性、隔离性和持久性四大特性。

        原子性：将一个事务中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

        一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设的约束、触发器和级联回滚等。

        隔离性：数据库允许多个并发事务同事对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致的数据的不一致。

        持久性：事务处理结束后，对数据的修改就是持久的，即便系统故障也不会丢失。

事务中常用的如下命令：

1. START TRANSACTION 或者 BEGIN，作用是显式开启一个事务。
2. COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。
3. ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意为回滚事务。意思是撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点。
4. SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
5. RELEASE SAVEPOINT：删除某个保存点。
6. SET TRANSACTION，设置事务的隔离级别。

脏读

SQL> BEGIN;
SQL> INSERT INTO student values(4, '张小飞');

这里尚使用 COMMIT 来提交这个事务，如果此时在学生表里查询到新转入的张小飞同学，就是脏读。

不可重复读

SQL> BEGIN;

SQL> SELECT name FROM heros_temp WHERE id = 4;

 班主任查询了一下新转入的同学，查询的结果是 “张小飞”

SQL> BEGIN;

SQL> UPDATE student SET name = '吕小布' WHERE id = 4;

 此时教导主任将学号4对应的张小飞 修改成了另一位同学 “吕小布”，修改的事务并没有提交

此时如果班主任再次查询一下新转入的同学，却变成了吕小布同学。这时候班主任就满头问号了，这就是不可重复读。

幻读

SQL> SELECT * FROM student;

班主任查询了学生表的所有同学，查询结果是：刘小备、关小羽，张小飞，吕小布

SQL> BEGIN;
SQL> INSERT INTO student values(5, '孙小权');

 班主任查询之后，教导主任添加新转入的孙小权同学，此时教导主任的添加操作并没有提交事务。

此时班主任再次查询学生表的时候，查询的结果就是：刘小备、关小羽，张小飞，吕小布、孙小权。

脏读、不可重复读和幻读这三种异常情况，是在 SQL-92 标准中定义的，同时 SQL-92 标准还定义了 4 种隔离级别来解决这些异常情况。

读未提交（READ UNCOMMITTED ）允许 脏读、不可重复读和幻读的异常存在。

读已提交（READ COMMITTED）禁止脏读、允许不可重复读和幻读的异常存在。

可重复读（REPEATABLE READ）禁止脏读、不可重复读 允许幻读的存在 

可串行化（SERIALIZABLE）禁止脏读、不可重复读和幻读的存在