SQL Server - 使用聚集索引时如何存储数据页

Question

jericzech

Asked: 2019-06-06 13:26:35 +0800 CST2019-06-06 13:26:35 +0800 CST 2019-06-06 13:26:35 +0800 CST

调用相同过程的相同资源死锁

772

我想向社区寻求解决这种僵局情况的帮助：

<deadlock-list>
 <deadlock victim="process17392264e8">
  <process-list>
   <process id="process17392264e8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="4449" ownerId="394509589" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:41.320" XDES="0x18ed9516f0" lockMode="IX" schedulerid="4" kpid="8348" status="suspended" spid="143" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:41.310" lastbatchcompleted="2019-06-05T12:06:41.310" lastattention="1900-01-01T00:00:00.310" clientapp="Helios Orange" hostname="???" hostpid="508" loginname="???" isolationlevel="read committed (2)" xactid="394509589" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
    <executionStack>
     <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
         FROM TabDavka D 
           INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
         WHERE D.IDDilce=@IDKusovni     </frame>
     <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x010007000f5ba906f09b4b6f0700000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1     </frame>
    </executionStack>
    <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1
SELECT @ret    </inputbuf>
   </process>
   <process id="process15bf6ce8c8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="6020" ownerId="394493191" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:39.750" XDES="0x16fb190ea0" lockMode="IX" schedulerid="1" kpid="3940" status="suspended" spid="83" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:39.750" lastbatchcompleted="2019-06-05T12:06:39.750" lastattention="1900-01-01T00:00:00.750" clientapp="Helios Orange" hostname="???" hostpid="14924" loginname="???" isolationlevel="read committed (2)" xactid="394493191" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
    <executionStack>
     <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
         FROM TabDavka D 
           INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
         WHERE D.IDDilce=@IDKusovni     </frame>
     <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x0100070009fde604c031f1451a00000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1     </frame>
    </executionStack>
    <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1
SELECT @ret    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
    <owner-list>
     <owner id="process15bf6ce8c8" mode="S"/>
     <owner id="process15bf6ce8c8" mode="IX" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process17392264e8" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
    <owner-list>
     <owner id="process17392264e8" mode="S"/>
     <owner id="process17392264e8" mode="IX" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process15bf6ce8c8" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

它位于同一资源（表）上，并且是由从两个会话调用的同一过程引起的。

程序的简化流程是这样的：

SELECT @Variable = Column FROM Table WITH (HOLDLOCK)
…
UPDATE Table SET...

我将不胜感激任何建议如何防止这种死锁情况？

1 个回答

Voted

Solomon Rutzky · Answer 1 · 2019-06-06T21:12:59+08:00

问题是在事务期间在该资源上HOLDLOCK创建“共享”锁（锁）。Mode: S这不会阻止其他进程（例如在另一个会话中执行的同一进程）将自己的“共享”锁放在同一资源上。但是随后两个会话都到达了UPDATE试图将其锁转换（即升级）为“Intent eXclusive”（Mode: IX锁）的语句，由于另一个会话仍持有其“共享”锁，因此无法完成。

如果的目的HOLDLOCK是为该会话保留行（以便其他人无法修改它），那么您需要防止其他会话能够在同一资源上放置自己的“共享”锁（同时，当然）。

一种方法是更改HOLDLOCKto UPDLOCK（即“更新”锁）。这允许其他事务对同一资源（如HOLDLOCK）采取相同的锁定，但其中一个事务可以转换/升级为XLOCK然后将强制所有其他事务（仍然具有它们的UPDLOCK）等待直到事务结束（不像HOLDLOCK） . 此外，虽然锁仍然是“更新”锁（即在转换为“排他”锁之前），但其他事务/会话可以读取该资源（也像HOLDLOCK）。

如果至少有一行要更新并且锁转换为“排他”锁，并且您有其他进程需要访问此表而无需等待所有这些更新完成，那么您有几个选择：

使用WITH (NOLOCK)提示（读取已修改但尚未提交，因此可能会回滚行）
使用WITH (READPAST)提示（完全跳过行，就好像它不存在一样）
查看SNAPSHOT ISOLATION（读取当前/未修改的行）

以下测试应说明所有这些：

查询选项卡 A

将以下内容粘贴到 SSMS（即会话）中的一个查询选项卡中，并仅执行块引用中的语句（现在；即USE,CREATE TABLE和INSERT）

USE [tempdb];

/*
-- DROP TABLE dbo.test;

USE [tempdb];
CREATE TABLE dbo.test
(
  ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [col1] INT NOT NULL
);
INSERT INTO dbo.test ([col1]) VALUES (11), (13), (15);
*/

DECLARE @ID INT;

BEGIN TRAN;

SELECT @ID = [ID] FROM dbo.test WITH (ROWLOCK, UPDLOCK) WHERE [col1] = 13;
WAITFOR DELAY '00:00:10.000';

UPDATE dbo.test SET [col1] = 88 WHERE [ID] = @ID;
WAITFOR DELAY '00:00:5.000';

ROLLBACK TRAN;

查询选项卡 B

将以下内容粘贴到 SSMS 中的另一个查询选项卡（即另一个会话），但不要执行它（还）。

USE [tempdb];

DECLARE @ID INT;

BEGIN TRAN;

SELECT @ID = [ID] FROM dbo.test WITH (ROWLOCK, UPDLOCK) WHERE [col1] = 15;
WAITFOR DELAY '00:00:10.000';

UPDATE dbo.test SET [col1] = 999 WHERE [ID] = @ID;
WAITFOR DELAY '00:00:5.000';

ROLLBACK TRAN;

查询选项卡 C

将以下内容粘贴到 SSMS 中的另一个查询选项卡（即另一个会话），但不要执行它（还）。

USE [tempdb];

SELECT * FROM sys.dm_tran_locks --WHERE [request_session_id] IN (x, y);

SELECT 'readpast', * FROM dbo.test WITH (READPAST);

SELECT 'nolock', * FROM dbo.test WITH (NOLOCK);
GO -- separate next query as it will lock up once an UPDATE statement starts

SELECT 'no hint', * FROM dbo.test

主要测试

转到“查询选项卡 A”，点击F5。
等待一两秒钟，然后转到“查询选项卡 B”，点击F5。
转到“查询选项卡 C”，点击F5。
1. 对于第一个查询（即sys.dm_tran_locks），您应该会看到 6 行左右。如果您在系统中有一堆东西，请取消注释该WHERE子句并用“查询选项卡 A”和“查询选项卡 B”的会话 ID（即 SPID）替换x和。y
2. 您还应该为 3 个SELECT ... FROM dbo.test ...查询中的每一个返回 3 行。到目前为止，只使用了“更新”锁。
3. 等待一两秒钟，然后F5再次击中。这样做几次，直到最终查询没有立即返回。
4. 一旦“查询选项卡 A”进入UPDATE语句，几秒钟：
  1. 您只会从第一个SELECT ... FROM dbo.test ...查询中返回 2 行（即READPAST）
  2. 您将从下一个查询（即NOLOCK）中获取所有 3 行，但第 3 行将显示未提交的值。
  3. 最终查询根本不会回来（即没有提示），因为它现在被“eXclusive”锁阻塞（这就是为什么GO存在，就在这个查询之前，以便其他人可以在这个查询时返回他们的结果集被封锁）

调用相同过程的相同资源死锁

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

调用相同过程的相同资源死锁

1 个回答

相关问题