データを効率的に処理するエッセンスを教えます
連載：SQL実践講座（28・最終回）

SQL Serverで「デッドロック」を回避する

篠原光太郎
2003/4/11

■ロックの仕組み（その2）−オブジェクトのロックタイプ

　前回「第27回 トランザクションの一貫性を保証するロック」は、トランザクションの4つの分離レベルの説明をしました。今回は、この分離レベルを実現するデータベースサーバのロック機構に触れてみましょう。実際にどのような仕組みでデータベースサーバがロック制御を行っているかを理解することで、トランザクション機能を利用した際に起こりやすい問題点を、あらかじめ予測できるようになると思います。

　今回は、初めに、ロックの状態を取得するシステムストアドプロシージャ「sp_lock」の定義を若干拡張した「sp_lock_detail」ストアドプロシージャを作成しましょう。

今回登場するトランザクションのコマンド群は、SQL Server 2000を題材に解説しています

■sp_lock_detailストアドプロシージャの作成

　ロックの状態を調べるために、SQL Server Enterprise Managerを利用する方法をこれまでの連載で紹介してきました。ただし、ロックの状態を確認するには、毎回「現在の利用状況」で「最新の状態に更新」を実施する必要があり、手間が掛かります。

画面1　Server Enterprise Mangerでのロック状態の確認（画面をクリックすると拡大表示します）

　これを回避するための便宜策として、sp_lockというシステムストアドプロシージャを利用する方法がありますが、データベース名とテーブル名がIDで表示されるため、判別がしにくいという欠点があります。

画面2　sp_lockの実行結果（画面をクリックすると拡大表示します）

　この判別のしにくさを回避するため、sp_lockシステムストアドプロシージャを拡張してみましょう。クエリアナライザより次のSQL文を実行して、sp_lock_detailストアドプロシージャを作成してみましょう。

【例題1】

1 CREATE PROCEDURE sp_lock_detail 2 　@spid1 int = NULL, 　　　/* server process id to check for locks */ 3 　@spid2 int = NULL 　　　 /* other process id to check for locks */ 4 AS 5 6 SET NOCOUNT ON 7 /* 8 **  Show the locks for both parameters. 9 */ 10 IF @spid1 IS NOT NULL 11 BEGIN 12 　SELECT CONVERT (SMALLINT, req_spid) AS spid, 13 　　　　　　db_name(rsc_dbid) AS dbid, 14 　　　　　　object_name(rsc_objid) AS ObjId, 15 　　　　　　rsc_indid AS IndId, 16 　　　　　　SUBSTRING (v.name, 1, 4) AS Type, 17 　　　　　　SUBSTRING (rsc_text, 1, 16) AS Resource, 18 　　　　　　SUBSTRING (u.name, 1, 8) AS Mode, 19 　　　　　　SUBSTRING (x.name, 1, 5) AS Status 20 21 　FROM master.dbo.syslockinfo, 22 　     master.dbo.spt_values v, 23 　     master.dbo.spt_values x, 24 　     master.dbo.spt_values u 25 26 　WHERE master.dbo.syslockinfo.rsc_type = v.number 27 　     　     and v.type = 'LR' 28 　     　     and master.dbo.syslockinfo.req_status = x.number 29 　     　     and x.type = 'LS' 30 　     　     and master.dbo.syslockinfo.req_mode + 1 = u.number 31 　     　     and u.type = 'L' 32 33 　     　     and req_spid in (@spid1, @spid2) 34 END 35 36 /* 37 ** No parameters, so show all the locks. 38 */ 39 ELSE 40 BEGIN 41 　SELECT CONVERT (SMALLINT, req_spid) AS spid, 42 　     db_name(rsc_dbid) AS dbid, 43 　     object_name(rsc_objid) AS ObjId, 44 　     rsc_indid AS IndId, 45 　     SUBSTRING (v.name, 1, 4) AS Type, 46 　     SUBSTRING (rsc_text, 1, 16) AS Resource, 47 　     SUBSTRING (u.name, 1, 8) AS Mode, 48 　     SUBSTRING (x.name, 1, 5) AS Status 49 50 　FROM master.dbo.syslockinfo, 51 　     .dbo.spt_values v, 52 　     .dbo.spt_values x, 53 　     .dbo.spt_values u 54 55 　WHERE master.dbo.syslockinfo.rsc_type = v.number 56          and v.type = 'LR' 57          and master.dbo.syslockinfo.req_status = x.number 58          and x.type = 'LS' 59          and master.dbo.syslockinfo.req_mode + 1 = u.number 60          and u.type = 'L' 61 　ORDER BY spid 62 END 63 64 RETURN (0) -- sp_lock

　うまくsp_lock_detailストアドプロシージャが作成できたかを確認するため、クエリアナライザからsp_lock_detailを実行してみましょう。

【例題2】

1 sp_lock_detail

画面3　sp_lock_detailの実行結果（画面をクリックすると拡大表示します）

　例題1で作成したストアドプロシージャの定義は、SQL Serverにおけるsp_lockの定義をほぼそのまま利用しています。このストアドプロシージャは、Server Process IDをパラメータに取る場合と取らない場合で大きく2つのブロックに分かれています。前半の10行目から34行目はパラメータを取る場合、40行目から62行目はパラメータを取らない場合です。それぞれのブロックの中は、masterデータベースのsyslockinfoというロックを管理しているシステムテーブルの情報を基に、いくつかのキーワードをspt_valuesというシステムテーブルを基にルックアップしています。非常に単純なストアドプロシージャです。

　利用上1点だけ注意しなくてはならないのが、上記の例題2の実行結果はロックオブジェクトが2行返されたはずですが、実際にロックが掛かっているのは1行目のみです。試しに、次の例題3を実行するとsyslockinfoテーブルには1行しか入っていないことが分かります。

【例題3】

1 select * from master.dbo.syslockinfo

画面4　syslockinfoの内容（画面をクリックすると拡大表示します）

　これは、masterデータベースのFK_Orders_Employeesに対してSQLの実行中に自動的にロックが掛かるためです。この行のみは無視するようにしてください。

■ロックのタイプ

　では、本題に入りましょう。まず、SELECT文において、ロックのタイプを強制的に変更するオプションを使用して、どの範囲にロックが掛かるかを確認していきましょう。では次の例題4を実行してみましょう。

【例題4】

1 BEGIN TRANSACTION 2 SELECT * FROM Customers WHERE Country = 'UK' 3 EXEC sp_lock_detail 4 ROLLBACK

画面5　SELECTのデフォルトの状態のロック（画面をクリックすると拡大表示します）

　クエリアナライザの結果ウィンドウには2つの結果セットが順に表示されています。スクロールバーで下の方をたどるとsp_lock_detailの結果を参照することが可能です。ここで確認していただきたいのは、sp_lock_detailの結果の1行目です（2行目は無視しましょう）。Northwindデータベースに対して、Modeが「S」と表示されています。これは、共有ロックを意味します。ロックのモードの記号の意味は、下記のとおりです。

記号 モード 意味 S 共有 共有ロックを意味します。ほかのトランザクションは、共有ロックが掛かっているオブジェクトの読み込みはできますが、更新はできません X 排他 排他ロックを意味します。ほかのトランザクションは、読み取りも更新もできません U 更新 更新ロックを意味します。更新ロックを掛けた後、実際に更新を行うまでは共有ロックになり、更新を行った後は排他ロックに変換されます。後で説明するデッドロックの防止に役立ちます IS インテント共有 インテント共有は、このロックが掛かっているオブジェクトよりも下位の階層の一部で、共有ロックが掛かっていることを示します。例えば、テーブルに対するインテントロックが掛かっている場合は、ページや行などの単位でロックが掛かっていることを示します。ほかのトランザクションがテーブルロックなどを掛けようとしたときに、１つ１つのオブジェクトがロック状態かどうかを確認する必要がなくなるため、パフォーマンスが向上します IX インテント排他 インテント共有と同様、下位の階層の一部に対して排他ロックが掛かっていることを示します SIX インテント排他付き共有 インテント共有のオプションで、下位の階層の一部に対して更新を掛ける予定があることを示します

表1　ロックモード記号

　また、Type列には「DB」と表示されており、共有ロックがデータベースに対して掛けられていることが分かります。Type列に表示されるほかのタイプは、下記のとおりです。

記号 対象 説明 RID RID 行識別子。テーブルの 1行をロックするために使用 KEY キー インデックス内のキーの範囲。直列化可能トランザクションで行ロックを行う場合に使用 PAG ページ 8Kbyteのデータページまたはインデックスページ EXT エクステント 8つのデータページまたはインデックスページの連続的な集合 TAB テーブル すべてのデータとインデックスを含むテーブル全体 DB DB データベース全体

表2　Type列（そのほかのタイプ）

　では、次の例題で、ロックモードを強制的に変更してみましょう。

【例題5】

1 BEGIN TRANSACTION 2 SELECT * FROM Customers WITH (HOLDLOCK) WHERE Country = 'UK' 3 EXEC sp_lock_detail 4 ROLLBACK

画面6　SELECTのHOLDLOCKオプションのロック（画面をクリックすると拡大表示します）

　 FROM句で指定するテーブルに「WITH句」を利用しロックオプションを指定します。今回は、WITH (HOLDLOCK)というオプションを指定して、Customersテーブルに対してHOLDLOCKを掛ける指定をしました。HOLDLOCKは、前回解説したSERIALIZABLEトランザクション分離レベルと同様な効果を発揮します。

　SERIALIZABLEトランザクション分離レベルは一番分離レベルが高いトランザクションタイプで、ほかの同じオブジェクトを利用するトランザクションが並行して実行されるのを防止します。このため、HOLDLOCKにおけるロックがSELECT文に対して指定されると、トランザクションが完了するまで指定されたオブジェクトのロックが持続します。

　上記の例題4においては、Customersテーブルのインテント共有ロック、6つのキーにおけるレンジ共有ロックが掛けられていることが分かります。HOLDLOCKのほかに指定可能なトランザクション分離レベルと共通のロックオプションは、以下のとおりです。

ロックのオプション 説明 NOLOCKREADUNCOMMITED SELECT ステートメントにだけ適用される、ロックを無視するオプションです。指定したテーブルには、共有ロックを掛けず、ほかのトランザクションが排他ロックを掛けている場合も関係なく読み込みを実行します。 よって、コミットされていないトランザクションを読み込んだり、読み取りの途中でページがロールバックされたりする可能性があります。つまり、ダーティリードが可能なモードです READCOMMITTED READ COMMITTED の分離レベルで動作するロックオプションです。SQL Server のデフォルトのロックレベルです REPEATABLEREAD REPEATABLE READ の分離レベルで動作するロックオプションです SERIALIZABLE、HOLDLOCK ロックをすぐに解除するのではなく、トランザクションの完了時まで保持するロックオプションです。HOLDLOCKとSERIALIZABLEは同じです

表3　トランザクション分離レベルと共通のロックオプション

　次に、ロックを掛ける範囲を指定してみましょう。次の例題では、テーブルに対してHOLDLOCKを指定してみます。

【例題6】

1 BEGIN TRANSACTION 2 SELECT * FROM Customers WITH (TABLOCK, HOLDLOCK) 3 WHERE Country = 'UK' 4 EXEC sp_lock_detail 5 ROLLBACK

画面7　SELECTのテーブルレベルHOLDLOCKオプションのロック（画面をクリックすると拡大表示します）

　先ほどの例題5においては、Customersテーブルに対してはインテント共有ロックのみが掛けられていましたが、今度は、Customersテーブルに対して共有ロックが掛けられていることが分かります。Customersテーブルに対しては、ほかのトランザクションから排他ロックが掛けられなるので、更新できない状態となります。

　例題6においては、WITH(TABLOCK, HOLDLOCK)というWITH句をCustomersテーブルに対して付与しました。TABLOCKというオプションが、テーブルに対するロックを指定することを意味します。ロックの範囲を指定するオプションと、ロックのタイプを指定するオプションは、上記のWITH句のように複数指定することが可能です。

　テーブル以外のロック範囲を指定するオプションは、下記のとおりです。

ロックのオプション 説明 ROWLOCK 行単位のロック PAGLOCK ページ単位のロック TABLOCK テーブル単位のロック

表4　テーブル以外のロック範囲指定

　上記以外の指定可能なロックのオプションは、下記のとおりです。

ロックのオプション 説明 READPAST ロックされた行をスキップします。SELECT ステートメントにだけ適用可能です UPDLOCK テーブルの読み出しの間、共有ロックの代わりに更新ロックを使用します。更新ロックは、データを読んだ後、トランザクションが終了するまでの間、ほかのユーザーがデータを参照することは可能ですが、更新することはできません。通常の更新系のトランザクションの場合は排他ロックが掛けられるため、ほかのトランザクションからは読み取ることもできなくなります（NOLOCKの場合以外）。参照ロックと排他ロックを上手に使い分けることで、同時実行性能を上げることが可能なオプションです XLOCK 排他ロックを強制的に使用します。PAGLOCKおよびTABLOCKと併せて指定できます TABLOCKX テーブル単位の排他ロック。WITH（TABLOCK, XLOCK）と同じ

表5　そのほかのロックオプション

　例題5や例題6のWITH（．．．）の部分を上記のオプションに変更すれば、いろいろなロックオプションの動作を検証することが可能です。ぜひ試してみてください。

■ロックの互換性

　さて、先ほど「Customersテーブルに共有ロックがされていると、ほかのトランザクションは排他ロックが掛けられない」という話をしましたが、ほかのロックの場合はどのようになるのでしょうか。次のマトリクスがすべてのパターンです。横列がすでに掛けられているモード、縦列がこれから掛けようとするモードです。

　テーブルや行に対してインテント共有が設定されている場合は、排他ロックを取得することはできませんが、ほかのロックの取得は可能です。同様に、共有ロックが設定されている場合は、インテントを含む排他系のロックの取得ができません。更新ロックが掛けられている場合には、共有系のロックのみ取得することが可能です。インテント排他の場合は、インテント共有のみ取得が可能です。排他ロックが掛かっている場合は、いかなるロックも取得ができません。

すでに掛けられているモード 要求するモード IS S U IX SIX × インテント共有(IS) ○ ○ ○ ○ ○ × 共有 (S) ○ ○ ○ × × × 更新 (U) ○ ○ × × × × インテント排他 (IX) ○ × × ○ × × インテント排他付き共有 (SIX) ○ × × × × × 排他 (X) × × × × × ×

表6　モードによってロックがかけられる

■デッドロック

　ロック制御において、もう1つ考えておかなくてはならないのが、デッドロックです。デッドロックとは、2つのトランザクションが互いのロックを取り合う、という状況です。例えば、顧客マスタメンテナンスのトランザクションが、顧客マスタと会社マスタをこの順番で同時に更新しようとします。

　一方、受注システムのトランザクションも、同じ会社マスタと顧客マスタをこの順番で更新しようとします。通常、これらが同時に実行されることはないため問題になりませんが、たまたま、この2つのトランザクションが同時に実行を開始すると、次のような状況に陥ります。

1. 顧客マスタメンテナンストランザクションは、顧客マスタをロックする。
2. 受注システムトランザクションは、会社マスタをロックする。
3. 顧客マスタメンテナンストランザクションは、会社マスタをロックしようとするが、受注システムトランザクションによってロックされているため、ロックが解除されるまで待つ。
4. 受注システムトランザクションは、顧客マスタをロックしようとするが、顧客マスタメンテナンストランザクションによりロックされているため、ロックが解除されるまで待つ。

　この状態になると、上記のステップ3と4で、それぞれ相手のトランザクションが終了するのを待ち合うため、いつになってもトランザクションが終了しません。これを、デッドロックの状態と呼びます。データベースサーバにとっては、これは致命的で回避不能な状態であるため、データベースサーバはデッドロックを検知して、自動的にロールバック、もしくはリトライをするための仕組みを持っています。

　SQL Serverは、一定時間ロック解除を待っているトランザクションを検知し、デッドロックに陥っていないか否かをチェックする方法で、デッドロックを回避します。ただし、デッドロックは上記の例のように、同じオブジェクトを違う順番でロックを掛ける場合に発生します。このため、可能な限り設計時点でオブジェクトに対してロックを掛ける順番を決めておくことで、デッドロックは容易に回避が可能となります。

■最後に

　 約3年間にわたる長期・不定期の連載となってしまいましたが、ようやくSQLを一通りカバーすることができました。今回にて、本連載は終了させていただきます。ご愛読、本当にありがとうございました。データストアの世界は、今後、よりXMLとの親和性が高くなり、オブジェクトのストアが容易になる方向性にあると思います。

　そして、SQLもまた、XQueryというXML対応のクエリに時代の方向性は向かっています。これはこれで、非常に面白い時代になってきたものだと、胸に期待を踊らせています。

　ただし、オブジェクト指向が当たり前になったいまの時代になって初めて分かることは、根底に流れているものは同じだということです。そして、RDBMSとデータ指向という考え方が、どれだけ人間の感覚とマッチしていたかということを、痛切に感じます。単なるSQLという1つの言語としてとらえるのではなく、その根底に流れているものを、少しでも感じていただけたとしたら非常に光栄です。

ご愛読ありがとうございました

「Master of IP Network総合インデックス」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）