数据库系统之ANSI SQL中的事务处理

脏读现象(Dirty read phenomenon)

A transaction reads uncommitted data created by a transaction that fails later on.
举个例子：

--Transaction 1          Transaction 2
                         SELECT budget
                         FROM DEPARTMENT
                         WHERE name = 'SALES';
                         2000
UPDATE DEPARTMENT
SET BUDGET = BUDGET + 1000
WHERE NAME = 'Sales';
                        SELECT budget
                        FROM DEPARTMENT
                        WHERE name = 'SALES';
                        3000
ROLLBACK;

不可重复读现象(Non-repeatable read phenomenon)

事务几次读取相同的数据项，然而每次读数据项都有不同的值。
举个例子：

--Transaction 1          Transaction 2
                         SELECT budget
                         FROM DEPARTMENT
                         WHERE name = 'SALES';
                         2000
UPDATE DEPARTMENT
SET BUDGET = BUDGET + 1000
WHERE NAME = 'Sales';
COMMIT;
                        SELECT budget
                        FROM DEPARTMENT
                        WHERE name = 'SALES';
                        3000

幻影读现象(Phantom phenomenon)

事务多次计算同一个表中的总行数，每次总行数都不相同。
举个例子：

--Transaction 1          Transaction 2
                         SELECT count(*)
                         FROM DEPARTMENT
                         20
DELETE DEPARTMENT
WHERE NAME = 'Sales';
COMMIT;
                        SELECT count(*)
                        FROM DEPARTMENT
                        19

我在 PL/SQL做了一个触发幻影读现象的项目练习，可以进行参考学习：
https://blog.youkuaiyun.com/Jifu_M/article/details/112494666

隔离级别(Isolation levels)

SQL为数据库事务提供了四个级别的隔离.
隔离级别等同于正确性级别.
以下是ANSI SQL中定义的4种隔离级别：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
隔离级别被所谓的术语现象所定义(phenomena)。
考虑以下现象：
Dirty read phenomenon
Non-repeatable read phenomenon
Phantom phenomenon
下图位隔离级别与现象的关系：

从图中可以清楚的看到4种隔离级别从弱到抢的变化。
在最高级别的SERIALIZABLE，所有的现象都不会再发生。

隔离级别的设置

在ANSI SQL中，我们应该使用以下代码设置隔离级别：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

References

1. Elmasri R. and Navathe S. B., Fundamentals of Database Systems, Chapter 20.6 Transaction Support in SQL, 7th ed., The Pearson Education Ltd, 2017.