SE の雑記

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>
[sourcecode language='sql' ]
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

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

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

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

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

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

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

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

-- 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>
[sourcecode language='sql' ]
INSERT INTO [UserBalanceHistory](UserID,Amount) VALUES(@id,RAND())