如何复制SCH_S，SCH_M死锁 [英] How to replicate a SCH_S, SCH_M deadlock

查看：109
本文介绍了如何复制SCH_S，SCH_M死锁的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个大型迁移脚本（大约2000行），由于某些元数据上的死锁而失败，并且根据我在其上捕获的一些xEvents数据，锁定类型为SCH_S和SCH_M。该脚本非常复杂，因此我尝试使用最少的脚本重新创建相同的场景，因此我可以对其进行进一步调查。我的方案与在网上发现的一些方案之间的区别是，迁移脚本是在单个进程中执行的，与许多在线示例使用一个窗口进行一些模式更改而另一个窗口进行查询相反。 / p>

以下脚本有点混乱，但这是我尝试重新创建该问题的尝试，但它不起作用。

  SET NOEXEC OFF; 
删除功能（如果存在）dbo.testfunc;如果存在dbo.test，则
删除表； 
 
-创建一些对象。 
开始交易； 
 CREATE TABLE test 
（
 testid INT 
）; 
 GO 
如果@@ error<> 0 
 AND @@ trancount> 0 
设置NOEXEC ON; 
 GO 
创建函数testfunc 
（）
以SCHEMABINDING 
返回INT 
作为
开始
返回
（ 
 SELECT 2 * SUM（testid）FROM dbo.test 
）; 
 END； 
 GO 
如果@@ error<> 0 
 AND @@ trancount> 0 
设置NOEXEC ON; 
 GO 
如果@@ error<> 0 
 AND @@ trancount> 0 
回滚交易； 
 ELSE IF @@ trancount> 0 
提交交易； 
 
 GO 
 
-尝试引起SCH_S x SCH_M死锁。 
开始交易； 
 GO 
如果@@ error<> 0 
 AND @@ trancount> 0 
设置NOEXEC ON; 
 GO 
更改功能dbo.testfunc 
（）
返回INT 
 AS 
开始
返回
（
选择2 * SUM（testid）FROM dbo.test 
）; 
 END； 
 GO 
执行sp_lock 
如果@@ error<> 0 
 AND @@ trancount> 0 
回滚交易； 
 GO 
 ALTER TABLE dbo.test ADD teststring VARCHAR（12）NULL; 
 GO 
如果@@ error<> 0 
 AND @@ trancount> 0 
设置NOEXEC ON; 
 GO 
选择顶部（1）
睾丸
 FROM测试； 
 GO 
执行sp_lock 
 go 
 ALTER FUNCTION testfunc 
（）
以SCHEMABINDING 
作为
返回INT 
开始
返回
（
从dbo.test中选择前1个testid，然后按testid DESC 
订购）； 
 END； 
 GO 
执行sp_lock 
如果@@ error<> 0 
 AND @@ trancount> 0 
设置NOEXEC ON; 
 GO 
 ALTER TABLE dbo.test ADD test_bit BIT NULL; 
 GO 
执行sp_lock 
如果@@ error<> 0 
 AND @@ trancount> 0 
回滚交易； 
 ELSE IF @@ trancount> 0 
提交交易； 
 GO

有人可以帮助我在单个过程中重新创建这种情况吗？



以下是死锁xml报告：

  <死锁> 
<受害者列表> 
< victimProcess id = process1fbf61b8ca8 /> 
< /受害者列表> 
< 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-查询 hostname = userhost hostpid = 11492 loginname = CORP\user孤立级别= serializable（4） xactid =  46926609 currentdb = 5 lockTimeout = 4294967295 clientoption1 = 671287392 clientoption2 = 390200> 
< executionStack> 
< frame procname = unknown line = 39 stmtstart =-1 sqlhandle = 0x0300050048c3ee382a43d70044ab000000000000000000000000000000000000000000000000000000000000000000000000000000> 
未知< / frame> 
< frame procname = adhoc line = 66 stmtstart =-1 sqlhandle = 0x010005009a7e7609c0be4a35fb01000000000000000000000000000000000000000000000000000000000000000000000000000000> 
创建函数[dbo]。[tvf_GetRawPOLineDataRelatedToOption] 
（
 @CommunityID INT，
 @FloorPlanID INT，
 @OptionID INT，
 @RelatedIncludedOptionIDToRemove INT 
）
返回@ReturnData表
（
 [POTemplateID] INT非空，
 [POTemplateItemID] INT非空，
 [POTemplateItemQuantityTypeID] INT非空， 
 [Quantity] DECIMAL（12，5）NULL，
 [FloorPlanQuantityTypeID] INT NULL，
 [DynamicQuantityPercentage] INT NULL，
 [QuantityForFlooringMaterialTypeID] INT NULL，
 [ HomesiteQuantityTypeID] INT空
）
具有模式绑定
 AS 
开始
声明@DependencyData dbo.DependencyDataKeyValueTable; 
 DECLARE @POTemplateDependencyFilterData dbo.DependencyFilterKeyValueTable; 
 DECLARE @POTemplateItemDependencyFilterData dbo.DependencyFilterKeyValueTable; 
 DECLARE @POTemplateIDs dbo.IndexedIntTable; 
 
 / * 
 *获取此社区中此平面图的默认依赖关系数据。 
 * / 
插入@DependencyData 
选择[键]，
 [值] 
从dbo。 < / frame> 
< / executionStack> 
< inputbuf> 
创建函数[dbo]。[tvf_GetRawPOLineDataRelatedToOption] 
（
 @CommunityID INT，
 @FloorPlanID INT，
 @OptionID INT，
 @RelatedIncludedOptionIDToRemove INT 
）
返回@ReturnData表
（
 [POTemplateID] INT非空，
 [POTemplateItemID] INT非空，
 [POTemplateItemQuantityTypeID] INT非空， 
 [Quantity] DECIMAL（12，5）NULL，
 [FloorPlanQuantityTypeID] INT NULL，
 [DynamicQuantityPercentage] INT NULL，
 [QuantityForFlooringMaterialTypeID] INT NULL，
 [ HomesiteQuantityTypeID] INT空
）
具有模式绑定
 AS 
开始
声明@DependencyData dbo.DependencyDataKeyValueTable; 
 DECLARE @POTemplateDependencyFilterData dbo.DependencyFilterKeyValueTable; 
 DECLARE @POTemplateItemDependencyFilterData dbo.DependencyFilterKeyValueTable; 
 DECLARE @POTemplateIDs dbo.IndexedIntTable; 
 
 / * 
 *获取此社区中此平面图的默认依赖关系数据。 
 * / 
插入@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 /> 
<所有者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>

解决方案


有人可以帮助我在单个过程中重新创建这种情况吗？


此问题的简单演示此处。

  BEGIN TRAN 
 
 go 
 
创建类型dbo.OptionIDs as TABLE（OptionID INT PRIMARY KEY）
 
 go 
 
声明@OptionIDs dbo.OptionIDs; 
 
去
 
回滚



资源为 metadatalock subresource = USER_TYPE classid = user_type_id = 264，这种情况非常罕见。



会话已在用户定义类型上持有 SCH-M 元数据锁（可能是您在脚本的前面创建的），这阻止了稍后在同一会话中尝试对同一对象获得 SCH-S 锁的尝试。



死锁图中显示的交易名称为 @OptionIDs -这是内部创建与 DECLARE @OptionIDs 对应的表类型的实例时写入 tempdb 事务日志的系统事务以上。这是与周围的用户事务分开的事务，这就是为什么会话尽管已持有 SCH-却意外地无法获得 SCH-S 锁的原因。 M 锁。



这是Aaron Bertrand发表的博客此处


I have a large migration script (about 2000 lines) that fails because of a deadlock on some metadata, and the lock types are SCH_S and SCH_M according to some xEvents data I've captured on it. The script is quite complicated, so I'm trying to recreate the same scenario using minimal scripting, so I can investigate it further. One difference between my scenario and some of the ones I've found online is that my migration script is being executed in a single process, as opposed to many examples online which use one window to make some schema changes and another window to query.

The following script is a little messy, but is my attempt at recreating the issue, but it doesn't work.

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

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

Here is the deadlock xml report:

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

解决方案

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

There is a simple demo of this issue here.

BEGIN TRAN

go

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

go

DECLARE @OptionIDs dbo.OptionIDs;

go

ROLLBACK

It is certainly unusual to have a deadlock involving a single process and single resource.

The resource is metadatalock subresource="USER_TYPE" classid="user_type_id = 264"

The session already holds a SCH-M metadata lock on a user defined type (probably as you created it earlier in the script) and this blocks an attempt to get a SCH-S lock on the same object by the same session later.

The transaction name shown in the deadlock graph is @OptionIDs - this is an internal system transaction that writes to the tempdb transaction log when creating the instance of the table type corresponding with the DECLARE @OptionIDs above. This is a separate transaction from the surrounding user transaction which is why the session is unexpectedly unable to get a SCH-S lock despite already holding the SCH-M lock.

This is blogged about by Aaron Bertrand here

这篇关于如何复制SCH_S，SCH_M死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆