選擇TABLOCKX然后MERGE與MERGEwithTABLOCKX-有解無憂

我原以為以下查詢會得到相同的結果：

MERGE [myTable] AS T WITH (TABLOCKX)
...

SELECT TOP 1 1 FROM [myTable] WITH (TABLOCKX);
MERGE [myTable] AS T
...

但是，當從多個行程運行我的 MERGE 陳述句并行時，第一個會導致死鎖，而第二個運行得很好。我在這里錯過了什么嗎？

我應該注意到它在事務中運行。

編輯

我創建了一個示例 DDL 和 testdata 來重新創建問題：

DROP TABLE IF EXISTS [dbo].[myReference]
GO

DROP TABLE IF EXISTS [dbo].[myTable]
GO

CREATE TABLE [dbo].[myTable](
    [Primary key] [int] IDENTITY(1,1) NOT NULL,
    [Dataset key] [int] NOT NULL,
    [Key] [int] NOT NULL,
 CONSTRAINT [PK myTable] PRIMARY KEY CLUSTERED ([Primary key] ASC)
)
GO

CREATE TABLE [dbo].[myReference](
    [Foreign key] INT NOT NULL,
 CONSTRAINT [FK myReference myTable] FOREIGN KEY ([Foreign key]) REFERENCES [dbo].[myTable] ([Primary key]) ON DELETE CASCADE
)
GO

DROP PROCEDURE IF EXISTS [dbo].[usp]
GO

CREATE PROCEDURE [dbo].[usp] @DatasetKey INT AS

WITH Val AS (
    SELECT *
    FROM ( VALUES 
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) s ([Value])
)

SELECT t1.[Value]
      (t2.[Value] - 1) * 10 
      (t3.[Value] - 1) * 100    
      (t4.[Value] - 1) * 1000   
      (t5.[Value] - 1) * 10000  
      (t6.[Value] - 1) * 100000
    AS [Key]
INTO #t
FROM Val t1
    cross apply Val t2
    cross apply Val t3
    cross apply Val t4
    cross apply Val t5
    cross apply Val t6
;

--SELECT TOP 1 1 FROM [dbo].[myTable] WITH (TABLOCKX);

MERGE [dbo].[myTable] WITH (TABLOCKX) AS T
USING #t AS S
ON T.[Dataset key] = @DatasetKey
    AND T.[Key] = S.[Key]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Dataset key], [Key]) VALUES (@DatasetKey, S.[Key])
WHEN NOT MATCHED BY SOURCE AND T.[Dataset key] = @DatasetKey THEN
    DELETE
;
GO

EXEC [dbo].[usp] 1
GO

EXEC [dbo].[usp] 2
GO

INSERT INTO [dbo].[myReference]
SELECT [Primary key]
FROM [dbo].[myTable]
GO

當同時運行以下兩個事務時，每次都會導致死鎖。

傳輸 1

BEGIN TRY;
    BEGIN TRANSACTION;

    exec [dbo].[usp] 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @XactState INT = XACT_STATE();

    IF @XactState <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

傳輸 2

BEGIN TRY;
    BEGIN TRANSACTION;

    exec [dbo].[usp] 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @XactState INT = XACT_STATE();

    IF @XactState <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

死鎖報告

<deadlock>
 <victim-list>
  <victimProcess id="process273cd96d468" />
 </victim-list>
 <process-list>
  <process id="process273cd96d468" taskpriority="0" logused="0" waitresource="OBJECT: 42:1282103608:0 " waittime="2356" ownerId="2255393274" transactionname="user_transaction" lasttranstarted="2021-11-26T16:11:20.080" XDES="0x296a4fb64d0" lockMode="X" schedulerid="4" kpid="19904" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-11-26T16:11:20.080" lastbatchcompleted="2021-11-26T16:11:20.077" lastattention="1900-01-01T00:00:00.077" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SV00415" hostpid="21276" loginname="VIECURI\mhoogeveen" isolationlevel="read committed (2)" xactid="2255393274" currentdb="42" currentdbname="Test20211126KanDaarnaWeg" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="Test20211126KanDaarnaWeg.dbo.usp" line="29" stmtstart="1050" stmtend="1626" sqlhandle="0x03002a00aaa1534ecba70a01ecad000001000000000000000000000000000000000000000000000000000000">
MERGE [dbo].[myTable] WITH (TABLOCKX) AS T
USING #t AS S
ON T.[Dataset key] = @DatasetKey
    AND T.[Key] = S.[Key]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Dataset key], [Key]) VALUES (@DatasetKey, S.[Key])
WHEN NOT MATCHED BY SOURCE AND T.[Dataset key] = @DatasetKey THEN
    DELETE    </frame>
    <frame procname="adhoc" line="4" stmtstart="72" stmtend="106" sqlhandle="0x020000009f228824b51645ad1d06b456eabe7b2b24f2e8fe0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
BEGIN TRY;
    BEGIN TRANSACTION;

    exec [dbo].[usp] 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @XactState INT = XACT_STATE();

    IF @XactState &lt;&gt; 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;   </inputbuf>
  </process>
  <process id="process273c6c4b088" taskpriority="0" logused="0" waitresource="OBJECT: 42:1250103494:0 " waittime="2994" ownerId="2255393256" transactionname="user_transaction" lasttranstarted="2021-11-26T16:11:19.633" XDES="0x27385bfd080" lockMode="X" schedulerid="1" kpid="8752" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-11-26T16:11:19.633" lastbatchcompleted="2021-11-26T16:11:19.630" lastattention="1900-01-01T00:00:00.630" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SV00415" hostpid="21276" loginname="VIECURI\mhoogeveen" isolationlevel="read committed (2)" xactid="2255393256" currentdb="42" currentdbname="Test20211126KanDaarnaWeg" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="Test20211126KanDaarnaWeg.dbo.usp" line="29" stmtstart="1050" stmtend="1626" sqlhandle="0x03002a00aaa1534ecba70a01ecad000001000000000000000000000000000000000000000000000000000000">
MERGE [dbo].[myTable] WITH (TABLOCKX) AS T
USING #t AS S
ON T.[Dataset key] = @DatasetKey
    AND T.[Key] = S.[Key]
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Dataset key], [Key]) VALUES (@DatasetKey, S.[Key])
WHEN NOT MATCHED BY SOURCE AND T.[Dataset key] = @DatasetKey THEN
    DELETE    </frame>
    <frame procname="adhoc" line="4" stmtstart="72" stmtend="106" sqlhandle="0x020000002457720d4bb1099d3682fee9760829cab4bbc2be0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
BEGIN TRY;
    BEGIN TRANSACTION;

    exec [dbo].[usp] 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @XactState INT = XACT_STATE();

    IF @XactState &lt;&gt; 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="1282103608" subresource="FULL" dbid="42" objectname="Test20211126KanDaarnaWeg.dbo.myReference" id="lock29a7a3f4780" mode="IX" associatedObjectId="1282103608">
   <owner-list>
    <owner id="process273c6c4b088" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process273cd96d468" mode="X" requestType="convert" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="1250103494" subresource="FULL" dbid="42" objectname="Test20211126KanDaarnaWeg.dbo.myTable" id="lock28a11c39800" mode="X" associatedObjectId="1250103494">
   <owner-list>
    <owner id="process273cd96d468" mode="X" />
    <owner id="process273cd96d468" mode="X" />
    <owner id="process273cd96d468" mode="X" />
    <owner id="process273cd96d468" mode="X" />
    <owner id="process273cd96d468" mode="X" />
    <owner id="process273cd96d468" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process273c6c4b088" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>

uj5u.com熱心網友回復：

看起來不同的是，MERGE with TABLOCKX最初確實需要一個 IX 鎖，而SELECT ... WITH TABLOCKX沒有。

我lock:acquired在 SQL Server 2019 CU 14 上驗證了此事件分析，并且使用較小的表能夠重現死鎖。這是一個極短的視窗，可能會發生這種情況，并且較大的表在我的系統上不允許足夠的并發性。

這會創建一個小視窗，其中兩個會話可以獲取 IX 表鎖，但都不能升級為 X 鎖。

如果要序列化一段代碼，sp_getapplock是最簡單的方法。

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/368490.html

標籤：sql-server 查询语句僵局 sql-server-2017

上一篇：為什么當我在IA32_LSTARMSR上為SYSCALL使用內核除錯器在Windows64位上設定斷點時會出現DoubleFault？（KVASCODE部分）

下一篇：SQLServer-阻止用戶在沒有主帳戶的情況下創建輔助帳戶