关键更新死锁

2018-07-04 06:35:08

SQL Server 2014 Express。

我将我的问题模拟为以下内容：

CREATE TABLE [dbo].[foo](
    [fooid] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
    [fooval] [nvarchar](4),
    CONSTRAINT [foo_PK] PRIMARY KEY CLUSTERED 
    (
        [fooid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[foo] ([fooval]) VALUES (1) 
GO

INSERT INTO [dbo].[foo] ([fooval]) VALUES (2)
GO

CREATE TABLE [dbo].[bar](
    [barid] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
    [barval] [nvarchar](4),
    CONSTRAINT [bar_PK] PRIMARY KEY CLUSTERED 
    (
        [barid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[bar] ([barval]) VALUES (1)
GO

INSERT INTO [dbo].[bar] ([barval]) VALUES (2)
GO

所以我有两个简单的表，在fooid和barid上都有一个集群主键。

我在两个调试器中运行以下两个查询。

第一个查询：

BEGIN TRAN
UPDATE dbo.foo SET fooval = 1 WHERE fooid = 1

UPDATE dbo.bar SET barval = 1 WHERE barval = 1
COMMIT

第二个查询：

BEGIN TRAN
UPDATE dbo.bar SET barval = 2 WHERE barid = 2

UPDATE dbo.foo SET fooval = 2 WHERE fooval = 2
COMMIT

在调试时，我执行查询1的第一次更新，然后执行查询2的第一次更新，然后执行查询1的第二次更新，最后执行查询2的第二次更新。

这会导致死锁。我正在运行快照隔离级别读取已提交。

该图显示：

<deadlock-list>
 <deadlock victim="process2f3ed64e8">
  <process-list>
   <process id="process2f3ed64e8" taskpriority="0" logused="288" waitresource="KEY: 5:72057607973896192 (227b7397de24)" waittime="2067" ownerId="1978563" transactionname="user_transaction" lasttranstarted="2015-08-24T16:24:57.280" XDES="0x2e2ff23b0" lockMode="U" schedulerid="1" kpid="9892" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-08-24T16:24:56.997" lastbatchcompleted="2015-08-24T16:24:56.993" lastattention="1900-01-01T00:00:00.993" clientapp="Microsoft SQL Server Management Studio - Abfrage" hostname="VSL53439" hostpid="9124" loginname="x" isolationlevel="read committed (2)" xactid="1978563" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="6" stmtstart="38" stmtend="146" sqlhandle="0x02000000118b7210fc35334336b07155dea42e1470abe8dd0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="6" stmtstart="336" stmtend="426" sqlhandle="0x02000000bf0a381fd6fec29b6ed330f87409b4e8c47d26f10000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
BEGIN TRAN
UPDATE dbo.bar SET barval = 2 WHERE barid = 2

UPDATE dbo.foo SET fooval = 2 WHERE fooval = 2
COMMIT    </inputbuf>
   </process>
   <process id="process2e01b5088" taskpriority="0" logused="432" waitresource="KEY: 5:72057607973830656 (c939eba47c7b)" waittime="2970" ownerId="1978502" transactionname="user_transaction" lasttranstarted="2015-08-24T16:24:54.100" XDES="0x2df783000" lockMode="U" schedulerid="5" kpid="1928" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-08-24T16:24:53.730" lastbatchcompleted="2015-08-24T16:24:53.730" lastattention="1900-01-01T00:00:00.730" clientapp="Microsoft SQL Server Management Studio - Abfrage" hostname="VSL53439" hostpid="4348" loginname="x" isolationlevel="read committed (2)" xactid="1978502" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="6" stmtstart="38" stmtend="146" sqlhandle="0x02000000f8c0c134764c79fe77f7cda514cc62eaf1a50cc80000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="6" stmtstart="336" stmtend="426" sqlhandle="0x020000005c75f728d068a9d6386669fb7b8e315b3e484d640000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
BEGIN TRAN
UPDATE dbo.foo SET fooval = 1 WHERE fooid = 1

UPDATE dbo.bar SET barval = 1 WHERE barval = 1
COMMIT    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057607973896192" dbid="5" objectname="dbdevelop.dbo.foo" indexname="foo_PK" id="lock2ea279880" mode="X" associatedObjectId="72057607973896192">
    <owner-list>
     <owner id="process2e01b5088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2f3ed64e8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057607973830656" dbid="5" objectname="dbdevelop.dbo.bar" indexname="bar_PK" id="lock2eb0e6500" mode="X" associatedObjectId="72057607973830656">
    <owner-list>
     <owner id="process2f3ed64e8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2e01b5088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

当我看看锁获取时，我看到以下锁已经完成

获得 - IX - OBJECT

获得 - IX - PAGE

获得 - X - KEY

获得 - X - EXTENT

发布 - X - EXTENT

收购 - U - EXTENT

获得 - X - PAGE

发布 - U - EXTENT

发布 - X - PAGE

发布 - 0 - KEY

发布 - 0 - PAGE

所以，一切都会被释放，除了从一开始的OBJECT，这似乎是主要的关键指标。我想它会一直保存下来，直到交易完成并且不立即发布。这似乎导致了僵局。

你能否回答我以下问题：

我是否正确地认为聚集的主键索引锁定将保存到提交？

我是否正确，这将阻止所有其他并发更新尝试等待？

如果是这样，为什么整个索引在使用where子句中的给定主键进行更新时会被锁定？这意味着主键where子句上的每个更新都会锁定事务的整个表。我无法相信这一点。

是在fooval和barval上添加索引的最佳解决方案？

请问一个SQL服务器的行为与sql server express有什么不同？

跨越更新是死锁的秘诀。不管索引，索引类型等等。始终尝试以相同的顺序更新表。话虽如此，无论索引如何，如果数据位于同一页面上，那么您将遇到锁定方案，并且由于您正在以纵横交错的方式进行更新，因此将会选择其中一个命令作为死锁。

1.是
2.Yes
3.这个问题很复杂，在互联网上有很好的解释，但是你应该理解的是，不管索引如何，锁定都会发生，并且经常发生，但是僵局是由于战略不佳造成的。
4.Irrelevant
5.有些事情，但不是这种情况。

链接地址: http://www.djcxy.com/p/95447.html

上一篇: Deadlock on key update

下一篇: One Sql Server Deadlock issue: exclusive lock wait on update lock