SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

デッドロックのサンプルクエリ

with 2 comments

デッドロックのテストをする際のサンプルクエリなどを。

BOL では、デッドロック を確認するとよいかと思います。
第 3 章 トランザクション分離レベルの選択とデッドロックの問題~ SQL Server 2000 における Web アプリケーション開発 ~  も参考になります。

今回は以下のデッドロックを発生させるためのサンプルクエリとなっています。

  • サイクルデッドロック
  • 変換デッドロック
  • インデックス間デッドロック

テスト用のテーブルのクエリは以下になります。

CREATE TABLE [dbo].[LockTest](
    [Col1] [int] NOT NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
CONSTRAINT [PK_LockTest] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
))
GO
CREATE NONCLUSTERED INDEX [IX_LockTest_Col3] ON [dbo].[LockTest]
(
    [Col3] ASC
)
GO
SET NOCOUNT ON
GO
DECLARE @i int, @cnt int = 1
SELECT @i = ISNULL(MAX(Col1), 0) + 1 FROM LockTest
WHILE (@cnt <= 20000)
BEGIN
    INSERT INTO LockTest VALUES(@i, @i + 1, @i + 2, @i + 3, @i + 4)
    SET @i += 1
    SET @cnt += 1
END
&#91;/sourcecode&#93;
</pre>
</div>
<p>&#160;</p>
<h3><font style="font-weight: bold"><u>■サイクルデッドロック</u></font></h3>
<hr />サイクルデッドロックは以下のようなデッドロックとなります。<a href="http://blog.engineer-memo.com/wp-content/uploads/2013/07/image87.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 5px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://blog.engineer-memo.com/wp-content/uploads/2013/07/image_thumb87.png" width="656" height="134" /></a>
<p>更新がたすき掛けで行われ、どちらのトランザクションからも互いの更新要求が発生してしまい、互いで取得している排他ロック (X) が原因でデッドロックとなるものとなります。</p>
<p>セッション A からは以下のクエリを実行します。</p>
<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:0c350eac-244e-4abe-8dc5-00140868b4cf" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    UPDATE LockTest SET Col2 = 3 WHERE Col1 = 1
    UPDATE LockTest SET Col2 = 4 WHERE Col1 = 2
    COMMIT TRAN
END

 

セッション B から以下のクエリを実行します。

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    UPDATE LockTest SET Col2 = 4 WHERE Col1 = 2
    UPDATE LockTest SET Col2 = 3 WHERE Col1 = 1
    COMMIT TRAN
END

 

回避をするためにはセッション B のクエリを以下のように変更し、レコードの変更順序を統一します。

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    UPDATE LockTest SET Col2 = 3 WHERE Col1 = 1
    UPDATE LockTest SET Col2 = 4 WHERE Col1 = 2
    COMMIT TRAN
END

 

■変換デッドロック


変換デッドロックは最初はロックが競合しない共有ロックで互いのセッションからレコードを参照していたものを更新しようとした場合に発生します。

image

共有ロック同士は競合しませんので、どちらのトランザクションからも同一のデータを参照することができます。

この状態で各トランザクションが SELECT をしても共有ロックを開放しないようなトランザクションの設定としていた場合 (ロックヒントやトランザクション分離レベルで設定) に SELECT していたレコードを更新しようとすると、どちらも自トランザクション外の共有ロック (S) で排他ロック (X) が競合しデッドロックが発生します。

SQL Server のデフォルトのトランザクション分離レベルは READ COMMITTED になります。

READ_COMMITTED_SNAPSHOT が OFF に設定されている場合 (既定)、データベース エンジンでは共有ロックが使用され、現在のトランザクションでの読み取り操作中に他のトランザクションによって行が変更されるのを防ぐことができます。  また、ステートメントが他のトランザクションで変更された行を読み取ろうとしても、そのトランザクションが完了するまでステートメントはブロックされます。 いつ解放されるかは、共有ロックの種類によって決まります。 行ロックは、次の行が処理される前に解放されます。 ページ ロックは次のページの読み取り時に解放され、テーブル ロックはステートメントの終了時に解放されます。

トランザクション内でも行ロックの取得は短期で解放されますので、今回のテスト用のクエリでは HOLDLOCK を使用して取得したロックを確保したままにしています。

セッション A からは以下のクエリを実行します。

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    DECLARE @i int
    SELECT @i = COUNT(*) FROM LockTEST WITH(HOLDLOCK)WHERE Col1 = 1
    UPDATE LockTest SET Col2 = 3 WHERE Col1 = 1
    COMMIT TRAN
END

セッション B からは以下のクエリを実行します。

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    DECLARE @i int
    SELECT @i = COUNT(*) FROM LockTEST WITH(HOLDLOCK)WHERE Col1 = 1
    UPDATE LockTest SET Col2 = 3 WHERE Col1 = 1
    COMMIT TRAN
END

これを回避するためには共有ロックの解放は即時に行う (できるだけ短時間で解放する) するか以下のように更新対象の行に対してのロックは更新ロック (U : UPDLOCK) を使用するようにします。

更新ロック同士は競合しますので、SELECT をしたタイミングで同じレコードに対してのロックが取得できないようになります。

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    DECLARE @i int
    SELECT @i = COUNT(*) FROM LockTEST WITH(UPDLOCK, HOLDLOCK)WHERE Col1 = 1
    UPDATE LockTest SET Col2 = 3 WHERE Col1 = 1
    COMMIT TRAN
END

 

■インデックス間デッドロック


最後は複数のインデックスを設定している場合に発生するインデックス間のデッドロックになります。

image

セッション A はクラスター化インデックスを使用して検索し、非クラスター化インデックスを更新しています。

# クラスター化インデックスの排他ロック (X) と非クラスター化インデックスの排他ロック (X)

セッション B は非クラスター化インデックスで検索し、クラスター化インデックスの項目を取得しています。

# クラスター化インデックスの共有ロック (S) と日クラスター化インデックスの共有ロック (S)

この処理が流れているとインデックス間のデッドロックが発生することがあります。

再現用のクエリとしては以下のクエリになります。

セッション A では以下のクエリを実行します。

こちらはクラスター化インデックスで検索をし、非クラスター化インデックスを更新しています。

この時にクラスター化インデックスと非クラスター化インデックスの排他ロック (X) が取得されています。

# 同じ値で更新をすると再現できないので特定の範囲のハッシュ値で更新されるようにしています。

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    DECLARE @i int
    UPDATE LockTest
    SET Col3 =abs(convert(bigint,convert(varbinary,NEWID()))) % 5 WHERE Col1 = 1
    COMMIT TRAN
END

セッション B のクエリは非クラスター化インデックスで検索をし、クラスター化インデックスの項目を取得しているものになります。

# カバードインデックスになっていない状態です。

こちらはクラスター化インデックスと非クラスター化インデックスに共有ロック (S) を取得します。

SET NOCOUNT ON
GO
WHILE (0=0)
BEGIN
    BEGIN TRAN
    DECLARE @i int
    SELECT @i = COUNT(Col2) FROM LockTEST WHERE Col3 = 4
    COMMIT TRAN
END

 

これらの処理が競合している場合に、使用しているインデックスによってデッドロックが発生することがあります。

この場合は非クラスター化インデックスだけで検索が完結するようにする (カバードインデックスや付加列インデックス) 、READ COMMITTED SNAPSHOT を使用する、検索時にロックを取得しない (NOLOCK) というような対応を検討していく必要があるのかと。

# 複数のインデックスが使われた場合のデッドロックは調査が大変だと思いますが…。

■トリガー設定をすることによるデッドロック

SQL 2008 R2: Trigger causing a deadlock? で記載されていますが、トリガーが設定されたことでデッドロックが発生するケースもあります。

再現のクエリについては以下のようになるかと。

-- http://stackoverflow.com/questions/6282501/sql-2008-r2-trigger-causing-a-deadlock
DROP TABLE IF EXISTS idtable
GO
CREATE TABLE idtable (id int)
GO
DECLARE @id int = 1
BEGIN TRAN
WHILE (@id <= 100)
BEGIN
	INSERT INTO idtable VALUES(@id)
	SET @id += 1
END
COMMIT TRAN
DROP TABLE IF EXISTS UserBalanceHistory
GO
DROP TABLE IF EXISTS &#91;User&#93;
GO
CREATE TABLE &#91;User&#93;
(
    ID INT IDENTITY,
    Balance MONEY,
    CONSTRAINT PK_User PRIMARY KEY (ID)
);
CREATE TABLE UserBalanceHistory
(
    ID INT IDENTITY,
    UserID INT NOT NULL,
    Amount MONEY NOT NULL,
    CONSTRAINT PK_UserBalanceHistory PRIMARY KEY (ID),
    CONSTRAINT FK_UserBalanceHistory_User FOREIGN KEY (UserID) REFERENCES &#91;User&#93; (ID)
);
GO
CREATE NONCLUSTERED INDEX IX_UserBalanceHistory_1 ON UserBalanceHistory (UserID) INCLUDE (Amount);
GO
DROP TRIGGER IF EXISTS TR_UserBalanceHistory_1
GO
CREATE TRIGGER TR_UserBalanceHistory_1 ON UserBalanceHistory AFTER INSERT, UPDATE, DELETE AS
BEGIN
    DECLARE @UserID INT;
    SELECT TOP 1 @UserID = u.UserID
    FROM
    (
            SELECT UserID FROM inserted
        UNION
            SELECT UserID FROM deleted
    ) u;
    EXEC dbo.UpdateUserBalance @UserID;
END;
GO
DROP PROCEDURE IF EXISTS UpdateUserBalance
GO
CREATE PROCEDURE UpdateUserBalance
    @UserID INT
AS
BEGIN
    DECLARE @Balance MONEY;
    SET @Balance = (SELECT SUM(Amount) FROM UserBalanceHistory WHERE UserID = @UserID);
    UPDATE &#91;User&#93;
    SET Balance = ISNULL(@Balance, 0)
    WHERE ID = @UserID;
END;
GO
SELECT * FROM &#91;User&#93;
SELECT * FROM &#91;UserBalanceHistory&#93;
GO
UPDATE &#91;User&#93; SET Balance=1 WHERE id = 1
SET NOCOUNT ON
GO
DECLARE @cnt int = 1
BEGIN TRAN
WHILE(@cnt <= 10000)
BEGIN
	INSERT INTO &#91;User&#93;(Balance) VALUES(RAND())
	INSERT INTO &#91;UserBalanceHistory&#93;(UserID,Amount) VALUES(@cnt, RAND())
	SET @cnt += 1
END
COMMIT TRAN
&#91;/sourcecode&#93;
</pre>
</div>
<p>&#160;</p>
<p>これでベースの環境を作ったうえで、以下のクエリを実行すると、トリガー設定によるデッドロックが発生するかと。
  <br /># SQLQueryStress 等で、@id に idtable の id 列の値を設定してクエリを実行します。</p>
<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:b516c411-6a6d-426d-8bb0-383082f6f29a" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>

INSERT INTO [UserBalanceHistory](UserID,Amount) VALUES(@id,RAND())

 

トリガー内の処理でデッドロックが発生した場合は、以下のようなデッドロックのグラフが取得できるかと。

# 出力直後の状態は図形が重なっていたりしますので、ドラッグアンドドロップで整形しています。

image

 

クエリに造詣が深い方ですとこの辺のノウハウをいろいろとお持ちだと思うのですが、私は基本パターンの把握ぐらいしかできていないのでもう少し勉強しないといけないですね。

Written by Masayuki.Ozawa

7月 15th, 2013 at 3:35 pm

Posted in SQL Server

Tagged with

2 Responses to 'デッドロックのサンプルクエリ'

Subscribe to comments with RSS or TrackBack to 'デッドロックのサンプルクエリ'.

  1. いつも参考にさせていただいております。
    「変換デッドロック」のサンプルで、
    セッションAのSELECTを共有ロック
    セッションBのSELECTを更新ロック
    とした場合、デッドロックが発生しますが、Profiler の Graph を見ると、Owner Mode が両セッションともに U と表示されます。
    デッドロックが発生している以上、片方が共有ロックで間違いないはずなのですが、Profilerの表示がおかしいのかな?と、いまいち腑に落ちません。
    もし何かご存じでしたら、ご教示いただけると有難いです。
    versionは2008SP2です。
    宜しくお願い申し上げます。

    yobi

    31 7月 15 at 15:05

  2. デッドロックグラフを XML で表示したものと、以下のような図でイメージがわかないでしょうか??
    変換デッドロックの場合、keylock の id (Owner の表示に使用されている情報) が同じになっているのでこのような見え方になるかと。
    # サイクルデッドロックの場合は異なる id となっているかと。
    ロック系を言葉で説明するのが難しく省エネな説明で申し訳ありませんm(_ _)m

    <resource-list>
    <keylock hobtid=”72057594049462272″ dbid=”8″ objectname=”DemoDB.dbo.LockTest” indexname=”PK_LockTest” id=”lock61d9048800″ mode=”U” associatedObjectId=”72057594049462272″>
    <owner-list>
    <owner id=”process619d047848″ mode=”U”/>
    <owner id=”process619d047848″ mode=”X” requestType=”convert”/>
    </owner-list>
    <waiter-list>
    <waiter id=”process61d5c464e8″ mode=”X” requestType=”convert”/>
    </waiter-list>
    </keylock>
    <keylock hobtid=”72057594049462272″ dbid=”8″ objectname=”DemoDB.dbo.LockTest” indexname=”PK_LockTest” id=”lock61d9048800″ mode=”U” associatedObjectId=”72057594049462272″>
    <owner-list>
    <owner id=”process61d5c464e8″ mode=”S”/>
    <owner id=”process61d5c464e8″ mode=”X” requestType=”convert”/>
    </owner-list>
    <waiter-list>
    <waiter id=”process619d047848″ mode=”X” requestType=”convert”/>
    </waiter-list>
    </keylock>
    </resource-list>

    masayuki.ozawa

    31 7月 15 at 16:25

Leave a Reply