sql-server - 如何复制 SCH_S、SCH_M 死锁

Question

我有一个大型迁移脚本(大约 2000 行)由于某些元数据的死锁而失败，根据我在上面捕获的一些 xEvents 数据，锁定类型是 SCH_S 和 SCH_M。该脚本非常复杂，因此我尝试使用最少的脚本重新创建相同的场景，以便我可以进一步研究它。我的场景与我在网上找到的一些场景之间的一个区别是，我的迁移脚本是在单个进程中执行的，而不是许多在线示例使用一个窗口进行一些架构更改并使用另一个窗口进行查询。

下面的脚本有点乱，但我试图重现这个问题，但它不起作用。

SET NOEXEC OFF;
DROP FUNCTION IF EXISTS dbo.testfunc;
DROP TABLE IF EXISTS dbo.test;

-- Create some objects.
BEGIN TRANSACTION;
CREATE TABLE test
(
    testid INT
);
GO
IF @@error <> 0
   AND @@trancount > 0
    SET NOEXEC ON;
GO
CREATE FUNCTION testfunc
()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN
    (
        SELECT 2 * SUM(testid) FROM dbo.test
    );
END;
GO
IF @@error <> 0
   AND @@trancount > 0
    SET NOEXEC ON;
GO
IF @@error <> 0
   AND @@trancount > 0
    ROLLBACK TRANSACTION;
ELSE IF @@trancount > 0
    COMMIT TRANSACTION;

GO

-- Try to induce a SCH_S x SCH_M deadlock.
BEGIN TRANSACTION;
GO
IF @@error <> 0
   AND @@trancount > 0
    SET NOEXEC ON;
GO
ALTER FUNCTION dbo.testfunc
()
RETURNS INT
AS
BEGIN
    RETURN
    (
        SELECT 2 * SUM(testid) FROM dbo.test
    );
END;
GO
EXECUTE sp_lock
IF @@error <> 0
   AND @@trancount > 0
    ROLLBACK TRANSACTION;
GO
ALTER TABLE dbo.test ADD teststring VARCHAR(12) NULL;
GO
IF @@error <> 0
   AND @@trancount > 0
    SET NOEXEC ON;
GO
SELECT TOP (1)
       testid
FROM test;
GO
EXECUTE sp_lock
go
ALTER FUNCTION testfunc
()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN
    (
        SELECT TOP 1 testid FROM dbo.test ORDER BY testid DESC
    );
END;
GO
EXECUTE sp_lock
IF @@error <> 0
   AND @@trancount > 0
    SET NOEXEC ON;
GO
ALTER TABLE dbo.test ADD test_bit BIT NULL;
GO
EXECUTE sp_lock
IF @@error <> 0
   AND @@trancount > 0
    ROLLBACK TRANSACTION;
ELSE IF @@trancount > 0
    COMMIT TRANSACTION;
GO

有人可以帮我在单个进程中重新创建这种场景吗？

这是死锁xml报告:

<deadlock>
 <victim-list>
  <victimProcess id="process1fbf61b8ca8" />
 </victim-list>
 <process-list>
  <process id="process1fbf61b8ca8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 5 USER_TYPE(user_type_id = 264), lockPartitionId = 11" waittime="2517" ownerId="46927562" transactionname="@OptionIDs" lasttranstarted="2020-01-16T13:03:44.790" XDES="0x1fb0b790490" lockMode="Sch-S" schedulerid="12" kpid="20032" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-01-16T13:03:44.787" lastbatchcompleted="2020-01-16T13:03:44.783" lastattention="1900-01-01T00:00:00.783" clientapp="Microsoft SQL Server Management Studio - Query" hostname="userhost" hostpid="11492" loginname="CORP\user" isolationlevel="serializable (4)" xactid="46926609" currentdb="5" lockTimeout="4294967295" clientoption1="671287392" clientoption2="390200">
   <executionStack>
    <frame procname="unknown" line="39" stmtstart="-1" sqlhandle="0x0300050048c3ee382a43d70044ab000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="66" stmtstart="-1" sqlhandle="0x010005009a7e7609c0be4a35fb01000000000000000000000000000000000000000000000000000000000000">
CREATE FUNCTION [dbo].[tvf_GetRawPOLineDataRelatedToOption]
(
    @CommunityID INT,
    @FloorPlanID INT,
    @OptionID INT,
    @RelatedIncludedOptionIDToRemove INT
)
RETURNS @ReturnData TABLE
(
    [POTemplateID] INT NOT NULL,
    [POTemplateItemID] INT NOT NULL,
    [POTemplateItemQuantityTypeID] INT NOT NULL,
    [Quantity] DECIMAL(12, 5) NULL,
    [FloorPlanQuantityTypeID] INT NULL,
    [DynamicQuantityPercentage] INT NULL,
    [QuantityForFlooringMaterialTypeID] INT NULL,
    [HomesiteQuantityTypeID] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @DependencyData dbo.DependencyDataKeyValueTable;
    DECLARE @POTemplateDependencyFilterData dbo.DependencyFilterKeyValueTable;
    DECLARE @POTemplateItemDependencyFilterData dbo.DependencyFilterKeyValueTable;
    DECLARE @POTemplateIDs dbo.IndexedIntTable;

    /*
     *  Get the default dependency data for this floor plan in this community.
     */
    INSERT INTO @DependencyData
    SELECT [Key],
           [Value]
    FROM dbo.    </frame>
   </executionStack>
   <inputbuf>
CREATE FUNCTION [dbo].[tvf_GetRawPOLineDataRelatedToOption]
(
    @CommunityID INT,
    @FloorPlanID INT,
    @OptionID INT,
    @RelatedIncludedOptionIDToRemove INT
)
RETURNS @ReturnData TABLE
(
    [POTemplateID] INT NOT NULL,
    [POTemplateItemID] INT NOT NULL,
    [POTemplateItemQuantityTypeID] INT NOT NULL,
    [Quantity] DECIMAL(12, 5) NULL,
    [FloorPlanQuantityTypeID] INT NULL,
    [DynamicQuantityPercentage] INT NULL,
    [QuantityForFlooringMaterialTypeID] INT NULL,
    [HomesiteQuantityTypeID] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @DependencyData dbo.DependencyDataKeyValueTable;
    DECLARE @POTemplateDependencyFilterData dbo.DependencyFilterKeyValueTable;
    DECLARE @POTemplateItemDependencyFilterData dbo.DependencyFilterKeyValueTable;
    DECLARE @POTemplateIDs dbo.IndexedIntTable;

    /*
     *  Get the default dependency data for this floor plan in this community.
     */
    INSERT INTO @DependencyData
    SELECT [Key],
           [Value]
    FROM dbo   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <metadatalock subresource="USER_TYPE" classid="user_type_id = 264" dbid="5" lockPartition="11" id="lock1fb8a56df00" mode="Sch-M">
   <owner-list>
    <owner id="process1fbf61b8ca8" mode="Sch-M" />
    <owner id="process1fbf61b8ca8" mode="Sch-S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process1fbf61b8ca8" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </metadatalock>
 </resource-list>
</deadlock>

Answer 1

Can someone help me recreate this kind of scenario in single process?

这个问题有一个简单的demo here .

BEGIN TRAN

go

CREATE TYPE dbo.OptionIDs AS TABLE( OptionID INT PRIMARY KEY )

go

DECLARE @OptionIDs dbo.OptionIDs;

go

ROLLBACK 

涉及单个进程和单个资源的死锁肯定是不寻常的。

资源是 metadatalock subresource="USER_TYPE" classid="user_type_id = 264"
该 session 已持有 SCH-M元数据锁定用户定义的类型(可能是您之前在脚本中创建的)，这会阻止尝试获取 SCH-S稍后通过同一个 session 锁定同一个对象。

死锁图中显示的事务名称是 @OptionIDs - 这是一个 内部 写入 tempdb 的系统事务创建与 DECLARE @OptionIDs对应的表类型的实例时的事务日志以上。这是一个独立于周围用户事务的事务，这就是 session 意外无法获得 SCH-S 的原因。尽管已经持有 SCH-M锁。

这是 Aaron Bertrand 的博客 here