选择TABLOCKX然后使用MERGE与使用带TABLOCKX的MERGE的区别。

Question

选择TABLOCKX然后使用MERGE与使用带TABLOCKX的MERGE的区别。

sql-servert-sqldeadlocksql-server-2017

3

我本以为以下查询会有相同的结果：

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

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

然而，当我从多个进程并行运行我的 MERGE 语句时，第一个进程会导致死锁，而第二个进程则可以正常运行。这里有什么我忽略的吗？

需要注意的是，它在事务内运行。

编辑

我创建了一个示例 DDL 和测试数据来重现这个问题：

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

同时运行以下两个事务时，每次都会产生死锁。

TRAN 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>

- Menno

2

嗨，Menno。请阅读此内容：使用谨慎SQL Server合并语句，特别是部分SQL Server合并并发问题。 - TT.

它们应该在锁定“myTable”方面表现相同。你能发布死锁XML吗？ - David Browne - Microsoft

1

HOLDLOCK, UPDLOCK 在第一个版本中应该能解决大部分问题，你可能不需要 TABLOCKX。请参见 https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/。 - Charlieface

@TT 谢谢分享，我之前不知道！但是，尽管像 @Charlieface 建议的那样在事务中运行并且使用 HOLDLOCK，但似乎无法直接在MERGE语句中使用锁提示使其正常工作。 - Menno

为了完整起见，您能否同时包含特定的SQL Server版本标签？ - TT.

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- David Browne - Microsoft · Accepted Answer

看起来不同之处在于MERGE with TABLOCKX最初会获取IX锁，而SELECT ... WITH TABLOCKX则不会。

我在SQL Server 2019 CU14上通过对lock:acquired事件进行分析验证了这一点，并且在较小的表中可以重现死锁。这是一个极短的时间窗口，在我的系统上较大的表没有充分的并发性。

这将创建一个很小的时间窗口，其中两个会话可以获取IX表锁，但都无法升级为X锁。

如果您想要序列化代码块，则sp_getapplock是最简单的方法。