在不同隔离级别下可能发生的现象

在一定的隔离级别下，读取数据的程序可能比预期的多或少地检索行。

更改开始

在任何隔离级别下都可能出现的幻影行

SQL事务 T1 读取满足表中某些搜索条件的一组行。 SQL事务 T2 随后执行SQL语句，在表中生成一个或多个新行，这些新行也满足SQL事务 T1 使用的搜索条件。 如果 T1 在相同的搜索条件下重复读取原始数据，则 T1 会收到一组不同的行。

这种现象可能发生在未承诺读取（UR）、光标稳定性（CS）、读取稳定性（RS）或可重复读取（RR）隔离中。

当表格中存在多列索引，且以下活动同时发生时，就会出现幻行：

  • 一个事务执行一个查询，该查询根据表的第一列和第三列索引选择行。 查询使用索引扫描。
  • 另一笔交易更新了表中第二列索引的值。 查询尚未选择相应的行。 更新导致更新行的索引键值移动到索引扫描已经通过的点。
更改结束
更改开始

假设表EMP_INFO定义如下：

CREATE TABLE EMP_INFO (
 WORKDEPT CHAR(3) NOT NULL, 
 STATE CHAR(2),
 JOB CHAR(8), 
 LASTNAME VARCHAR(15), 
 FIRSTNME VARCHAR(12));

在表 EMP_INFO 的前三列中定义了索引 EMP_INFO_IX：

CREATE INDEX EMP_INFO_IX ON EMP_INFO(WORKDEPT, STATE, JOB);

表 EMP_INFO 包含如下行。 行以索引顺序显示。 这是使用索引扫描读取行的顺序。

WORKDEPT STATE JOB LASTNAME FIRSTNME
A00 加利福尼亚州 PRES HAAS CHRISTINE
A00 NY SALESREP HEMMINGER DIAN
A00 OH SALESREP 卢切斯 VINCENZO
A00 PA SALESREP O'CONNELL SEAN
事务 T1 执行以下查询，以获取部门 A00 中担任销售代表的员工列表：
SELECT FIRSTNME, LASTNAME FROM EMP_INFO WHERE WORKDEPT = 'A00' AND JOB='SALESREP';

执行事务 T1 的包与隔离可重复读取（RR）绑定。 查询使用索引 EMP_INFO_IX 来检索行。

会发生以下操作：

  1. 选择操作读取索引的第二个键值（' A00 '、'NY'、'SALESREP'），并从表的第二行中检索数据。 选择操作还会在该表行上锁定带有RR隔离的锁：
    WORKDEPT STATE JOB FIRSTNME LASTNAME
    A00 NY SALESREP DIAN HEMMINGER
  2. 同时，事务 T2 将姓氏列值为O'CONNELL的行的状态列值更新为AK：
    UPDATE EMP_INFO SET STATE ='AK' WHERE LASTNAME='O''CONNELL';
  3. 更新操作将索引 EMP_INFO_IX 中的键顺序更改为：
    WORKDEPT STATE JOB
    A00 AK SALESREP
    A00 加利福尼亚州 PRES
    A00 NY SALESREP
    A00 OH SALESREP
  4. 选择操作继续使用更新的索引顺序检索行：
    WORKDEPT STATE JOB
    A00 OH SALESREP

    姓氏列值为O'CONNELL的行是幻行。 结果集中不包含该行，因为索引扫描已经通过了该行索引键值的新位置。

  5. 如果再次执行查询，且没有影响索引键值顺序的更新，则将返回所有行。
更改结束
更改开始

在上面的示例中，更新操作将索引键移动到被选择操作锁定且具有RR隔离的第一行之前的位置。 因此，更新操作能够在选择操作仍在执行时成功运行。

为了避免幻行问题，选择操作需要使用不受更新操作影响的索引。

更改结束

未提交读取、光标稳定性或读取稳定性隔离时可能出现的幻像行

SQL事务 T1 读取满足表中某些搜索条件的一组行。 SQL事务 T2 随后执行SQL语句，在表中生成一个或多个新行，这些新行也满足SQL事务 T1 使用的搜索条件。 如果 T1 在相同的搜索条件下重复读取原始数据，则 T1 会收到一组不同的行。

这种现象可能发生在未承诺读取（UR）、光标稳定性（CS）或读取稳定性（RS）隔离中。

当表格中存在多列索引，且以下活动同时发生时，就会出现幻行：

  • 一次事务执行一个查询，该查询根据表中的第一个索引列选择行。 查询使用索引扫描。
  • 另一笔交易更新了表中第二列索引的值。 查询尚未选择相应的行。 更新导致更新行的索引键值移动到索引扫描已经通过的点。

在这种情况下，查询会错过更新的行。

示例 ：假设表 EMP_INFO 定义如下：

CREATE TABLE EMP_INFO (
 WORKDEPT CHAR(3) NOT NULL, 
 LASTNAME VARCHAR(15), 
 FIRSTNME VARCHAR(12), 
 JOB CHAR(8));

在表 EMP_INFO 的前两列中定义了索引 EMP_INFO_IX：

CREATE INDEX EMP_INFO_IX ON EMP_INFO(WORKDEPT, LASTNAME);

表 EMP_INFO 包含如下行。 行以索引顺序显示。 这是使用索引扫描读取行的顺序。

WORKDEPT LASTNAME FIRSTNME JOB
A00 HAAS CHRISTINE PRES
A00 HEMMINGER DIAN SALESREP
A00 卢切斯 VINCENZO SALESREP
A00 O'CONNELL SEAN 职员
A00 奥兰多 格雷格 职员
B01 THOMPSON MICHAEL MANAGER
C01 KWAN SALLY MANAGER
C01 NATZ Kim ANALYST
C01 NICHOLLS HEATHER ANALYST
C01 QUINTANA DOLORES ANALYST
…      

执行事务的包 T1 和 T2 与RS隔离绑定。

事务 T1 通过执行以下查询，检索部门 A00 的员工列表：
SELECT FIRSTNME, LASTNAME FROM EMP_INFO WHERE WORKDEPT = 'A00';
查询使用索引 EMP_INFO_IX 来检索行。

会发生以下操作：

  1. 选择操作读取索引的第一个键值（' A00 '、'HAAS'），并检索第一行：
    FIRSTNME LASTNAME
    CHRISTINE HAAS
  2. 与此同时，事务处理系统 T2 将员工奥康奈尔的姓氏更新为康纳利：
    UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';

    更新操作还使用索引 EMP_INFO_IX。

  3. 更新操作将索引 EMP_INFO_IX 中的键顺序更改为：
    ('A00','CONNELLY')
('A00','HAAS')
('A00','HEMMINGER')
('A00','LUCCHESI')
('A00','ORLANDO')
…
  4. 选择操作继续使用更新的索引顺序检索行：
    FIRSTNME LASTNAME
    DIAN HEMMINGER
    VINCENZO 卢切斯
    格雷格 奥兰多

    肖恩·康纳利的这一排是幽灵排。 结果集中不包含该行，因为索引扫描已经通过了该行索引键值的新位置。

  5. 如果再次执行查询，且没有影响索引键值顺序的更新，则将返回所有行：

    FIRSTNME LASTNAME
    SEAN 康纳利
    CHRISTINE HAAS
    DIAN HEMMINGER
    VINCENZO 卢切斯
    格雷格 奥兰多

脏读与未承诺的读隔离

SQL事务 T1 修改了一行。 在 T1 执行提交操作之前，SQL事务 T2 读取该行。 如果 T1 执行回滚操作，则 T2 将读取从未提交的一行，因此可以认为该行从未存在过。

这种现象可能发生在未承诺读取（UR）隔离中。

示例 ：假设表 EMP_INFO 定义如前例所示，且包含相同的数据。

执行事务的包 T1 和 T2 与UR隔离绑定。

会发生以下操作：

  1. 交易 T1 将员工 O'Connell 的姓氏更新为 Connelly：
    UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';
  2. 事务 T2 执行以下查询：
    SELECT FIRSTNME, LASTNAME FROM EMP_INFO WHERE WORKDEPT = 'A00';

    返回以下几行：

    FIRSTNME LASTNAME
    SEAN 康纳利
    CHRISTINE HAAS
    DIAN HEMMINGER
    VINCENZO 卢切斯
    格雷格 奥兰多
  3. 事务 T1 执行回滚操作，将更新语句恢复为：
    UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';

    上一步的结果不再有效，因为不再有姓氏为“CONNELLY”的行。

行不能以未承诺的读取或光标稳定性隔离进行重读

SQL事务 T1 读取一行。 SQL事务 T2 随后修改或删除该行并执行提交操作。 如果 T1 试图重新读取该行，则 T1 可能会收到修改后的值，或者发现该行已被删除。

这种现象可能发生在未提交读取（UR）或光标稳定性（CS）隔离中。

示例 ：假设表 EMP_INFO 定义如第一个示例所示，且包含相同的数据。

执行事务的包 T1 和 T2 与CS隔离绑定。

会发生以下操作：

  1. 事务 T1 执行以下查询：
    SELECT FIRSTNME, LASTNAME, JOB FROM EMP_INFO WHERE LASTNAME = 'HAAS';

    返回以下行：

    FIRSTNME LASTNAME JOB
    CHRISTINE HAAS PRES
  2. 事务 T2 将Christine Haas的工作从总裁更新为首席执行官，并提交更新：
    UPDATE EMP_INFO SET JOB='CEO' WHERE LASTNAME='HAAS' AND FIRSTNME='CHRISTINE';
COMMIT;
  3. 事务 T1 再次执行以下查询：
    SELECT FIRSTNME, LASTNAME, JOB FROM EMP_INFO WHERE LASTNAME = 'HAAS';

    返回的下一行与之前通过同一查询返回的行不同：

    FIRSTNME LASTNAME JOB
    CHRISTINE HAAS 首席执行官