PREPARATION
受験対策

Silverの例題解説「開発/SQL - トランザクション」

今回は、Silverの「開発/SQL - トランザクション」からの出題です。

例題

3.15

あるクライアントで次の一連のSQLを実行する。

BEGIN;
UPDATE table1 SET val1 = 100 WHERE id1 = 1;
UPDATE table1 SET val1 = 200 WHERE id1 = 2;
UPDATE table2 SET val2 = 300 WHERE id2 = 3;
COMMIT;

別のクライアントから、これとほぼ同時に実行したときに、デッドロックが発生する可能性のあるトランザクションはどれか。A~Eの選択肢から2つ選択せよ。

  1. BEGIN;
    INSERT INTO table2 (id2, val2) VALUES (4, 40);
    UPDATE table1 SET val1 = 1000 WHERE id1 = 1;
    COMMIT;

  2. BEGIN;
    UPDATE table1 SET val1 = 1000 WHERE id1 = 1;
    UPDATE table2 SET val2 = 3000 WHERE id2 = 3;
    COMMIT;
  3. BEGIN;
    UPDATE table2 SET val2 = 3000 WHERE id2 = 3;
    UPDATE table1 SET val1 = 1000 WHERE id1 = 1;
    COMMIT;
  4. BEGIN;
    UPDATE table1 SET val1 = 1000 WHERE id1 = 1;
    UPDATE table1 SET val1 = 2000 WHERE id1 = 2;
    COMMIT;
  5. BEGIN;
    UPDATE table1 SET val1 = 2000 WHERE id1 = 2;
    DELETE FROM table1 WHERE id1 = 1;
    COMMIT;

※この例題は実際のOSS-DB技術者認定試験とは異なります。
例題公開日:2012年4月5日

解答と解説

RDBMSではデータの更新に先立ち、更新対象データのロックを取得します。
更新対象データが、他のトランザクションにより既にロックされていたときは、処理が停止され、ロックが獲得できるまで待ち続けることになります。
ロックを保持しているトランザクションが、COMMITを実行してデータ更新を完了する、あるいはROLLBACKを実行してデータ更新をキャンセルすると、ロックが解放され、ロック待ちになっていたトランザクションが再開されます。
しかし、複数のトランザクションが複数のロックを獲得しようとした時、それぞれのトランザクションがロック待ちの状態になって処理が先に進められない、ということが発生する場合があります。これをデッドロックといいます。

この例題のトランザクション(以下、Xとします)は、table1のid1=1の行、id1=2の行、およびtable2のid2=3の行について、順次、ロックを獲得しようとします。

Aのトランザクションは、まずtable2にid2=4の行をINSERTし、次にtable1のid1=1の行をUPDATEします。INSERTでもロックは発生しますが、この行に関してはXとロックの競合はありません。table1の更新についてはXとロックが競合しますので、どちらか一方のトランザクションがロック待ちになって停止する可能性があります。しかし、他方のトランザクションはロック待ちにはなりませんから、そのまま処理が続行され、COMMITの時点でロックが解放されます。ここで、停止していた他方のトランザクションが再開されますから、結局、デッドロックは発生しません。

Bのトランザクションは、table1のid=1の行をUPDATEし、次にtable2のid2=3の行をUPDATEします。いずれもXでの更新対象なので、ロックが競合する可能性があります。Xの方が少しだけ先に開始されたとすると、まず最初にtable1のid1=1の行をXがロックします。次に起きることは、Xがtable1のid1=2の行、table2のid2=3の行を順次ロックすることか、あるいはBがtable1のid1=1の行をロックすることか、のいずれかですが、後者についてはすでにXによってロックされているのでBはロック待ちの状態になります。Xはロックの競合なしにそのまま処理を続行できて、最後のCOMMITでロックを解放します。Bはこの時点で処理が再開でき、2つの行を順次更新して終了します。Bが先に開始された場合も、Xがロック待ちになるだけで、デッドロックは発生しません。

Cのトランザクションは、処理内容はBと同じですが、行のUPDATE順が逆です。やはりXが先に開始されたとして、最初にtable1のid1=1の行がロックされます。次は、Xがtable12のid2=3の行をロックするか、Cがtable2のid2=3の行をロックするか、のいずれかです。Xの処理が先に実行されれば問題ありませんが、Cが先だとどうなるでしょうか。Xはロック待ちの状態になります。次にCはtable1のid1=1の行のロックを獲得しようとしますが、これはXによってロックされているためCもロック待ちの状態になります。つまり、XとCが互いのロックが解放されるのを待つ状態(デッドロック)になり、処理が先に進めなくなります。Cの方が先に開始されたとしても、同じようにデッドロックが発生する可能性があります。

Dのトランザクションは、table1のid1=1の行、id1=2の行を順次UPDATEします。いずれもXとロックが競合する可能性がありますが、Bと同じくデッドロックにはなりません。

Eのトランザクションは、table1のid1=2の行をUPDATE、id1=1の行をDELETEします。行のロックはUPDATEだけでなくDELETEでも発生しますので、Xがid1=1の行をロックし、Eがid1=2の行をロックした場合は、X、Eのいずれも、ロック待ちの状態になり、デッドロックが発生します。

従って正解はCとEです。

以上の例からわかるように、デッドロックはデータ更新の処理の順番を工夫することで回避できることがある、ということに注意すべきです。また、個々のトランザクションが短時間で終了すれば、デッドロックが発生する可能性は低くなります。

PostgreSQLではデッドロックを自動的に検出する仕組みがあり、検出された場合はトランザクションの一つを強制的にROLLBACKすることでこれを解決します。
ただし、どのトランザクションがROLLBACKされるかは予測できませんので注意しましょう。

今回の解説について、理解できないポイントがあればどんどん質問をお寄せ下さい。
採用になった方にはLPI-Japanオリジナルの記念品を贈呈します。

※試験問題に関わるお問い合わせにつきましては、LPI-Japan事務局ではお応えできませんのでご了解ください。

企業の基幹システムや業務システムでの
OSS-DBの採用がますます拡大している中、
昇格・昇給・就職・転職に必ず役立つ認定資格になります

OSS-DBの受験対策について

受験の学習をサポートする情報や対策に役立つ情報をご紹介

サンプル問題/例題解説

例題 のアーカイブを試験ごとにまとめています。OSS-DB技術者認定試験の学習にお役立てください

学習教材・教育機関のご紹介

OSS-DB認定教材や教育機関について詳しくご説明いたします。

無料技術解説セミナー

受験準備をされていらっしゃる方々を対象に、無料技術解説セミナーの日程をお知らせしています。

OSS-DB道場

受験準備をされていらっしゃる方々を対象に、無料技術解説セミナーの日程をお知らせしています。

LPI-Japan
Platinum Sponsors