我本以为以下查询会有相同的结果:
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 <> 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 <> 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>
SQL Server合并并发问题
。 - TT.HOLDLOCK, UPDLOCK
在第一个版本中应该能解决大部分问题,你可能不需要TABLOCKX
。请参见 https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/。 - CharliefaceHOLDLOCK
,但似乎无法直接在MERGE语句中使用锁提示使其正常工作。 - Menno