反復可能読み取り以外の分離レベルで発生する可能性のある現象

反復可能読み取り以外の分離レベルでは、アプリケーションがデータを読み取っている間に、他のアプリケーションが更新を行うことができるので、データを読み取っているアプリケーションが取得する行数が予想されるよりも多い、または少ないことがあります。

発生する可能性のある現象は、幻像読み取り行、ダーティー読み取り、および反復不能読み取り と呼ばれます。

幻像読み取り行

SQL トランザクション T1 は、表で何らかの検索条件を満たす一連の行を読み取ります。 次に、SQL トランザクション T2 は、SQL トランザクション T1 が使用する検索条件も満たす、表内に 1 つ以上の新規行を生成する SQL ステートメントを実行します。 T1 が、その後同じ検索条件でオリジナルの読み込みを繰り返すと、T1 は異なる行のセットを受け取ります。

この現象は、非コミット読み取り (UR)、カーソル固定 (CS)、または読み取り固定 (RS) の分離で発生します。

幻像読み取り行が発生する一例として、表に複数列索引があり、以下のアクティビティーが並行して実行される場合が挙げられます。

  • 表の最初の索引付けされた列に基づいて行を選択する 1 つの照会を、1 つのトランザクションが実行する。 この照会では、索引スキャンが使用されます。
  • 別のトランザクションが、表内の 2 番目に索引付けされた列で値を更新する。 照会では、まだ対応する行が選択されていません。 この更新により、更新された行の索引キー値が、既に索引スキャンが通過しているポイントに移動されます。

この状況では、照会は更新済みの行を失います。

例: 表 EMP_INFO が以下のように定義されていると想定します。

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

索引 EMP_INFO_IX は、表 EMP_INFO の最初の 2 列で定義されています。

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 LUCCHESI VINCENZO SALESREP
A00 O'CONNELL SEAN CLERK
A00 ORLANDO GREG CLERK
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 が従業員 O'Connell から Connelly に姓を更新します。
    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 LUCCHESI
    GREG ORLANDO

    「Sean Connelly」の行は、幻像読み取り行です。 索引スキャンは、行の索引キー値の新しい位置を既に通過しているため、結果セットにその行は含まれていません。

  5. 索引キー値の順序に影響を与える更新を行わずに照会が再度実行されると、すべての行が返されます。

    FIRSTNME LASTNAME
    SEAN CONNELLY
    CHRISTINE HAAS
    DIAN HEMMINGER
    VINCENZO LUCCHESI
    GREG ORLANDO

ダーティー読み取り

SQL トランザクション T1 は行を変更します。 SQL トランザクション T2 は、T1 がコミット操作を実行する前にその行を読み取ります。 次に、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 CONNELLY
    CHRISTINE HAAS
    DIAN HEMMINGER
    VINCENZO LUCCHESI
    GREG ORLANDO
  3. トランザクション T1 が、次の更新ステートメントを元に戻すロールバック操作を実行します。
    UPDATE EMP_INFO SET LASTNAME='CONNELLY' WHERE LASTNAME='O''CONNELL';

    LASTNAME 値が 'CONNELLY' である行がなくなったため、前のステップの結果セットが有効ではなくなります。

反復不能読み取り

SQL トランザクション T1 が行を読み取ります。 次に、SQL トランザクション T2 は行を変更または削除し、コミット操作を実行します。 その後、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 のジョブを PRES から CEO に更新し、更新をコミットします。
    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 CEO