SQL Server 事务（ID）死锁

Question

在通过私有软件安装创建新数据库时，我遇到了无法解决的问题。

跟踪死锁的XML文件链接在此处查看

我能够追踪到死锁的原因，是因为我试图更改数据库所有者。

语句：EXEC [ISC_RAS_CD_APP].dbo.sp_changedbowner @loginame = N'sa', @map = false

<deadlock-list>
    <deadlock
        victim="process4efa404e8">
        <process-list>
            <process
                id="process4efa404e8"
                taskpriority="0"
                logused="0"
                waitresource="KEY: 1:281474978545664 (11ea04af99f6)"
                waittime="4947"
                ownerId="1284191"
                transactionname="HkHostCkptEnableDisable"
                lasttranstarted="2017-02-23T12:51:54.617"
                XDES="0x4ff1e5be0"
                lockMode="S"
                schedulerid="4"
                kpid="10252"
                status="suspended"
                spid="62"
                sbid="0"
                ecid="0"
                priority="0"
                trancount="1"
                lastbatchstarted="2017-02-23T12:51:54.610"
                lastbatchcompleted="2017-02-23T12:51:54.610"
                lastattention="2017-02-23T12:51:54.580"
                clientapp="SQL Management"
                hostname="IDQSRV01"
                hostpid="8940"
                loginname="HMS\OrenG"
                isolationlevel="read committed (2)"
                xactid="1284156"
                currentdb="12"
                lockTimeout="4294967295"
                clientoption1="673185824"
                clientoption2="128056">
                <executionStack>
                    <frame
                        procname="mssqlsystemresource.sys.sp_changedbowner"
                        line="26"
                        stmtstart="1656"
                        stmtend="1686"
                        sqlhandle="0x0300ff7f12d71ceed5d2350180a4000001000000000000000000000000000000000000000000000000000000">
checkpoint     </frame>
                    <frame
                        procname="adhoc"
                        line="1"
                        sqlhandle="0x01000c0069b98f048084f3000500000000000000000000000000000000000000000000000000000000000000">
EXEC [ISC_RAS_CD_APP].dbo.sp_changedbowner @loginame = N'sa', @map = false     </frame>
                </executionStack>
                <inputbuf>
EXEC [ISC_RAS_CD_APP].dbo.sp_changedbowner @loginame = N'sa', @map = false    </inputbuf>
            </process>
        </process-list>
        <resource-list>
            <keylock
                hobtid="281474978545664"
                dbid="1"
                objectname="master.sys.sysdbreg"
                indexname="clst"
                id="lock5006efc00"
                mode="X"
                associatedObjectId="281474978545664">
                <owner-list>
                    <owner
                        id="process4efa404e8"
                        mode="X" />
                    <owner
                        id="process4efa404e8"
                        mode="S"
                        requestType="wait" />
                </owner-list>
                <waiter-list>
                    <waiter
                        id="process4efa404e8"
                        mode="S"
                        requestType="wait" />
                </waiter-list>
            </keylock>
        </resource-list>
    </deadlock>
</deadlock-list>

“sa”是我在安装新服务器时创建的默认用户。

我的任务优先级设置为0，但每次它都会给我一个不同的任务ID，所以我不确定是否可以更改它。

我查看了在线上的每一个答案，但没有什么能帮到我，有人知道我该怎么做才能解决这个问题吗？

如果需要，可以提供更多信息。

谢谢！

- Orenger

---

你需要添加死锁图。 - TheGameiswar

@MartinSmith 我该怎么做？ - Orenger

在分析器中右键单击，我认为选项称为提取事件信息以将其保存为XML文件 - 然后您可以将其粘贴到您的问题中。 - Martin Smith

@MartinSmith 现在已经添加了 XML 文件。谢谢。 - Orenger

Answer 1

这是一个奇怪的图形。会话因为等待自己拥有的资源而死锁。

您提供了Profiler跟踪，而不仅仅是死锁图。

基于此，我可以在2014年重现问题，但在2012或2016上无法重现。

以下是在我测试过的所有2014实例（使用以下版本构建）上复制该问题的代码：

• (SP1-CU9-GDR) (KB3194722) - 12.0.4487.0 (X64)
• (SP2) (KB3171021) - 12.0.5000.0 (X64))
• Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) - 12.0.5540.0 (X64)

 

IF db_id('FOO') IS NOT NULL
BEGIN
print 'dropping db'
use master
alter database [FOO] set single_user with rollback immediate
drop database [FOO]
END

go

CREATE DATABASE [FOO]
go

BEGIN TRANSACTION
use [FOO]
EXEC [FOO].dbo.sp_changedbowner @loginame = N'sa', @map = false
COMMIT

我假设死锁图中的HkHostCkptEnableDisable（事务名称）中的Hk指的是“Hekaton”，因此这可能是在2014年支持内存 OLTP 的代码更改引入的问题。

如果我去掉显式事务，则问题会消失。因此，一种方法是这样做以释放正在争用的锁。

或者，您可以遵循sp_changedbowner的停用通知中的建议。

此功能将在未来的 Microsoft SQL Server 版本中删除。避免在新开发工作中使用此功能，并计划修改当前使用此功能的应用程序。改用 ALTER AUTHORIZATION。

sp_changedbowner已经调用了它，但添加了一个额外的checkpoint，导致出现问题（如果我使用下面的代码并取消注释检查点行，也会出现死锁）。

BEGIN TRANSACTION
alter authorization on database::[FOO] to [sa]
--checkpoint
COMMIT

检查点似乎会读取在同一会话中由alter authorization独占锁定的master.sys.sysdbreg中相同的行（sid列将更新为属于此数据库的行的0x01），并且检查点事务无法获得授予用户事务的锁。

- Martin Smith