如何复制 SCH_S、SCH_M 死锁

我有一个大型迁移脚本（大约 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>

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

这个问题有一个简单的演示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"

本届会议已经召开SCH-M用户定义类型上的元数据锁定（可能是您在脚本前面创建的），这会阻止尝试获取SCH-S稍后由同一个会话锁定同一个对象。

死锁图中显示的事务名称是@OptionIDs- 这是一internal写入的系统事务tempdb创建对应表类型实例时的事务日志DECLARE @OptionIDs多于。这是与周围用户事务分开的事务，这就是会话意外无法获取SCH-S尽管已经持有锁SCH-M lock.

这是 Aaron Bertrand 的博客here