SQLのトランザクション処理におけるデッドロックについて

今回は、以前に開発していたシステムで実際に起きたＳＱＬにおけるデッドロックについて、備忘録として書いていこうと思います。

まずはデッドロックについて少し前置きを・・・

例えば、ＤＢに対してトランザクション処理（一連の処理をまとめたもの）を行った際に、違うトランザクション処理と競合してしまい、処理結果が求めていたものと違う形になってしまったら、もちろん困りますよね？

もちろんこういった困ったことが起きないように、現在広く使われているＲＤＢＭＳでは、処理を行う対象のテーブルにロックをかけてから処理を行うことでこのような問題を防いでいます。

ただし！！

今回の問題は、このロックをかけることによって起きてしまいます。。

再び例え話なのですが、私がＤＢ上のあるテーブルに対してロックをかけようとした時に、既に違う誰かがロックをかけていたら、もちろんその人がロックを解くのを待つしかありませんね？

ただ、もしその既にロックをかけていた人も私がロックを解くのを待っていたとしたら？

お互いがお互いのロックが解かれるのを待っているのですから、一生待ち続けることになります。

そして、ロックをかけないと処理を行うことはできないため、この時点でトランザクション処理自体が先に進むことができなくなり、死んでしまいます。

つまり『デッドロック』です！

さて、そもそもお互いがお互いのロックを待つなんて不思議な状況は本当にあり得るのでしょうか？

まぁあり得なかったらこの記事はなんのための記事か分からないのですが・・・

全てのことの発端は、ロックには「排他ロック」と「共有ロック」という２種類のロックが存在することから始まります。

それぞれのロックについて簡単に説明すると・・・

【排他ロック】

専有ロックとも言われ、その名の通り対象のテーブルを専有してしまうロックです。

このロックがかかっている間は他の人はそのテーブルには手出しできません。

【共有ロック】

こちらも名前のまんまなのですが、他の人と共有できるロックになります。

私が共有ロックをかけている場合でも、違う人は同じテーブルに共有ロックをかけることができます。
ただし、排他ロックをかけることはできません。

はい。ざっくりではありますが、ここまででデッドロックとロックの種類に関する前置きは終了です。

それでは、今回起きた現象について、書いていこうと思います。

今回のシステムで実行していたトランザクション処理を言葉にすると

「テーブルにデータを登録した後に、不要になったデータを削除する」

という処理です。

例えば、テーブルに50件データを保持しておきたくて、データを新しく追加する度に古いデータから削除していく場合です。

今回はテスト用に適当なテーブルを用意して処理を行います。

  create table sample(

    id INT(11) NOT NULL AUTO_INCREMENT ,

    detail VARCHAR(10) ,

    PRIMARY KEY (id) ) ;

※「id」にはAUTO_INCREMENTを指定しているので、値を指定しない場合は前に挿入したレコードにの値に＋1した値を設定していきます。

それでは以下トランザクションのＳＱＬ文です。

【トランザクション１】

  begin ;

    /* インサート */

    insert into sample(detail) value ("test1") ;

    /* 削除対象「id」の値を変数に格納 */
    set @del_id1 = (select max(id) - 50 from sample) ;

    /* デリート */
    delete from sample where id < @del_id1 ;

  commit ;

【トランザクション２】

  begin ;

    /* インサート */

    insert into sample(detail) value ("test2") ;

    /* 削除対象「id」を変数に格納 */

    set @del_id2 = (select max(id) - 50 from sample) ;

    /* デリート */

    delete from sample where id < @del_id2 ;

  commit ;

※テーブルに50件以上データが入っていない場合は、削除処理は実行されますが、処理結果は0件です。

この２つのトランザクション処理について、【トランザクション１】のインサート文が実行されてから、デリート文が実行されるまでの間に、【トランザクション２】のインサート文が実行された場合、この２つのトランザクションの間でデッドロックが発生します。

さて、なぜデッドロックは発生したのでしょうか？

ここで大事になってくるのが、インサート文を実行することでテーブルにかけるロックは共有ロック、デリート文を実行することでテーブルにかけるロックは排他ロックだということです。

【トランザクション１】でインサート文を実行した時点で、【トランザクション１】はテーブルに共有ロックをかけます。

しかし、先程書いた通り、共有ロックがかかっている間でも、他の人も共有ロックを取得することができてしまうため、【トランザクション２】はインサート文を実行し、共有ロックを取得できます。

この状態で【トランザクション１】も【トランザクション２】もテーブルに対して共有ロックをかけている状態になります。

そして、それぞれのロックはコミットまたはロールバックされるまで解かれることはありません。

もうお分かりですよね？

つまり、【トランザクション１】はデリート文を実行するために排他ロックをかけようとした時に【トランザクション２】の共有ロックがはずれるのを待つことになり、【トランザクション２】もデリート文を実行するために排他ロックをかけようとした時に【トランザクション１】の共有ロックがはずれるのを待つことになるため、デッドロックが完成します。

言葉にすると上記の通りなのですが、図で説明すると以下の通りです。

ただ、少し考えれば分かることなのですが、対処法は簡単です。

『共有ロックをかける前に排他ロックをかけてあげる』だけです。

排他ロックを最初にかけてしまえば、もう一方のトランザクションはロックが解けてから排他ロックをかけて処理を行うため、デッドロックは発生しません。

つまり、デリート文を先に実行してあげる、もしくは排他ロックをトランザクションの一番最初に取得するだけで解決できます。

ちなみに以下のＳＱＬ文で簡単に排他ロックを取得できます。

  select *
  from [テーブル名]
  for update ;

そのため、トランザクションの一番最初で対象のテーブルに対して、上記SQL文で排他ロックを取得してあげれば、デッドロックは発生しません。

ちなみに、排他ロックについては、where句で行単位に取得することができたり、ロックをかけようとして、既に違う人がロックをかけていた場合の待ち時間を指定したりもできるので、興味がある方は是非調べてみて下さい！