数据库系统之Oracle DBMS中的事务处理

事务范围(Transaction Scope)

事务在 connection, COMMIT或者ROLLBACK 语句之后第一个可执行的SQL语句开始。

事务伴随着COMMIT、ROLLBACK或DDL语句CREATE, DROP, ALTER结束。

事务也会以断开连接或流程失败后自动回滚语句结束。

隔离级别和读取一致性级别

Oracle DBMS实现了三个隔离级别

1. READ COMMITTED
   事务中可能出现下面两种现象：
   1. non-repeatable read phenomenon 不可重复读现象
   2. phantom phenomenon 幻影读现象
2. SERIALIZABLE
   事务中不会出现任何现象。
3. READ ONLY
   事务仅由读操作组成。

在READ COMMITTED隔离级别，查询(SELECT语句)读取的所有数据都来自单个时间点(语句级读一致性)。
在可序列化的隔离级别上，事务中的所有查询(SELECT语句)读取来自单个时间点的数据(事务级读取一致性)。

Rollback/undo segments

Rollback/undo segments由数据块组成，这些数据块包含未提交或最近提交的事务更改的旧数据值。
每当数据块中的一行被更改时，Rollback/undo段就会添加一个新版本的块以及一个时间戳。
数据块的新版本获得的时间戳比上一个版本更高。
一个数据块可能在一瞬间有许多不同的版本更新。
如下图显示了事务T的时间戳ti.

READ COMMITTED和 SERIALIZABLE的级别

在READ COMMITTED隔离级别，每个查询的执行都与它自己的物化视图时间（materialized view time）有关，因此允许对查询的多次执行进行不可重复读和幻影操作。

当事务有很小的可能发生冲突时，建议使用READ COMMITTED隔离级别。

如果在SERIALIZABLE隔离级别上运行的事务T尝试更新或删除由在可串行化事务T开始后commit的事务修改的数据，则系统将中止事务。

如果一个SERIALIZABLE的事务失败，那么我们可以做以下三点：

1. 将已执行的工作commit到该点。
2. 执行额外的(但不同的)语句。
3. 回滚整个事务。

READ COMMITTED隔离级别

在事务开始的头部加入下面代码：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

READ COMMITTED同时也是默认的隔离级别。
举个例子：

Transaction 1                  Transaction 2 
SELECT budget
FROM DEPARTMENT
WHERE name = 'SALES';
3000
                              UPDATE DEPARTMENT
                              SET budget = budget + 10
                              WHERE name = 'Sales';
UPDATE DEPARTMENT
SET budget = budget + 1000
WHERE name = 'SALES';
Wait
                             COMMIT;
SELECT budget
FROM DEPARTMENT
WHERE name = 'SALES'
4010

在READ COMMITTED级别，上面的并发事务处理不会破坏数据库。

Transaction 1                              Transaction 2 
UPDATE DEPARTMENT
SET budget = (SELECT budget
              FROM DEPARTMENT
               WHERE name = 'Sales')
WHERE name = 'Finance';
                                           UPDATE DEPARTMENT
                                           SET budget =500
                                           WHERE name = 'Sales';
                                           COMMIT;
UPDATE DEPARTMENT
SET budget = budget + (SELECT budget
                       FROM DEPARMENT
                       WHERE name = 'Sales')
WHERE name = 'Finance';
COMMIT;

在上面这里例子中，数据库就被T1破坏了，因为最后T1这样操作让Finance的budget变成Sales budget的两倍。

SERIALIZABLE 隔离级别

在SERIALIZABLE隔离级别上处理数据库事务的示例：

ransaction 1                     Transaction 2

                                UPDATE DEPARTMENT
                                SET budget = budget + 10
                                WHERE name = 'Sales'
UPDATE DEPARTMENT
SET budget = budget + 1000
WHERE name = 'Sales';

Wait
                              COMMIT;
ERROR at line 2:
ORA-08177: can't serialize
access for this transaction

程序报错，该处理导致其中一个事务失败。

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'
                              2000

在上面这个例子里，两个事务都处理成功了，T2也没有发生幻影读现象，为什么呢？
因为在SERIALIZABLE隔离级别，T1为Sales部门创建了这一行的新版本。T2不收到这新版本的影响。

Locking

Locking由系统自动执行。
我们也可以手动锁定数据项。
一共有两种锁：

1. shared (read locks)
2. exclusive (write locks)
   如果一个事务有exclusive row locks，那么它可以对这个事务中的所有行进行 inserted, updated 或deleted 操作。
   当事务提交或回滚时，系统释放事务获得的所有锁。
   系统在适当的情况下，自会动将较低限制的锁(lower restrictiveness)转换为较高限制的锁(higher restrictiveness )。

举个例子：
带有 FOR UPDATE 句子的 SELECT语句最初以共享模式锁定行，然后在执行 UPDATE时将锁升级为排他锁(exclusive locks)。

References

1. T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 22.5 Concurrency Control and Recovery in Oracle, Pearson Education Ltd, 2015.