对表执行选择/更新操作的 SQL 死锁 [英] SQL deadlock with select/update operations on a table
问题描述
我已将 column(prod_id) 数据类型nvarchar(25)"更改为varchar(50)"，然后并行 DML 操作因死锁而失败.
I have changed the column(prod_id) datatype "nvarchar(25)" to "varchar(50)" then parallel DML operations are failing with deadlock.
涉及死锁的表——tbl_Ref_Attr_Prod_Team"表上的索引
The table involved in deadlock - "tbl_Ref_Attr_Prod_Team " Indexes on table
这里是选择/更新死锁xml..
Here is the select/update deadlock xml..
<deadlock>
<victim-list>
<victimProcess id="process3980de4558" />
</victim-list>
<process-list>
<process id="process3980de4558" taskpriority="0" logused="0" waitresource="PAGE: 7:1:1660625 " waittime="966" ownerId="635070687" transactionname="SELECT" lasttranstarted="2017-11-14T04:09:08.120" XDES="0x19f7d80040" lockMode="IS" schedulerid="6" kpid="23696" status="suspended" spid="121" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2017-11-14T02:12:22.280" lastbatchcompleted="2017-11-14T02:12:22.280" lastattention="1900-01-01T00:00:00.280" clientapp=".Net SqlClient Data Provider" hostname="GADC-WMGXSQLP01" hostpid="16128" isolationlevel="read committed (2)" xactid="635070687" currentdb="7" lockTimeout="4294967295" clientoption1="671088928" clientoption2="119832">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="112" sqlhandle="0x020000008925110aeb7cb1c1c073dab88fd24fca1951ea9d0000000000000000000000000000000000000000">
select @existing = team_it_cube_attr_05 from tbl_Ref_Attr_Prod_Team where prod_id = @rec_key </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="WMGDS_Ref.dbo.sp_datman_get_attr_value" line="30" stmtstart="2266" stmtend="2438" sqlhandle="0x03000700b694bf1125f34901f9a1000001000000000000000000000000000000000000000000000000000000">
EXECUTE @return_code = sp_executesql @SQL, @ParamDefinition, @item_key, @value OUTPUT </frame>
<frame procname="WMGDS_Ref.dbo.sp_datman_validate_category_role" line="146" stmtstart="12864" stmtend="13140" sqlhandle="0x030007005220010817f24901f9a1000001000000000000000000000000000000000000000000000000000000">
exec sp_datman_get_attr_value @attr_id, @item_key, @value OUTPUT
--if attr is required, make sure it has been supplied </frame>
<frame procname="WMGDS_Ref.dbo.sp_datman_validate_category" line="12" stmtstart="892" sqlhandle="0x030007008b44f50851f24901f9a1000001000000000000000000000000000000000000000000000000000000">
exec sp_datman_validate_category_role @geo_id, @category_id, 30, @unvalidated_records_only </frame>
<frame procname="WMGDS_Ref.dbo.sp_datman_validate_country_segmentation" line="33" stmtstart="1894" stmtend="2110" sqlhandle="0x030007005bcf6449a3f34901f9a1000001000000000000000000000000000000000000000000000000000000">
exec sp_datman_validate_category @geo_id, @category_id, @unvalidated_records_only
--fetch next record </frame>
<frame procname="adhoc" line="1" sqlhandle="0x010005005563e60c10dab88f0b00000000000000000000000000000000000000000000000000000000000000">
[WMGDS_Ref].[dbo].[sp_datman_validate_country_segmentation] '124' </frame>
<frame procname="WGDS_Master.dbo.spWGDSFileProcessor_CustomAction_WDIM_Export_Prod_WM" line="95" stmtstart="7378" stmtend="7420" sqlhandle="0x03000500d1db545fa59ca800d8a3000001000000000000000000000000000000000000000000000000000000">
EXEC(@SQL) </frame>
<frame procname="WGDS_Master.dbo.spWGDSFileProcessor_FinalizeLoad" line="30" stmtstart="2380" stmtend="2460" sqlhandle="0x03000500822a3374004032001ca8000001000000000000000000000000000000000000000000000000000000">
EXEC @SQL @LoadID </frame>
<frame procname="adhoc" line="1" sqlhandle="0x010005009ab97c38301aa2840a00000000000000000000000000000000000000000000000000000000000000">
EXEC spWGDSFileProcessor_FinalizeLoad 9389 </frame>
</executionStack>
<inputbuf>
EXEC spWGDSFileProcessor_FinalizeLoad 9389 </inputbuf>
</process>
<process id="process386ed48188" taskpriority="0" logused="38816080" waitresource="PAGE: 7:1:1593417 " waittime="822" ownerId="635069358" transactionname="test" lasttranstarted="2017-11-14T04:09:05.813" XDES="0x7109e3a10" lockMode="X" schedulerid="5" kpid="20156" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-14T04:09:05.043" lastbatchcompleted="2017-11-14T04:05:04.870" lastattention="1900-01-01T00:00:00.870" clientapp="Microsoft SQL Server Management Studio - Query" hostname="BDC-WMGXSQLD01" hostpid="68396" loginname="AP\mandalapu.s.1" isolationlevel="read committed (2)" xactid="635069358" currentdb="7" lockTimeout="4294967295" clientoption1="671091040" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="5" stmtstart="86" sqlhandle="0x02000000b91ca9120ff55e660725b95775c8d18d8bff60d30000000000000000000000000000000000000000">
UPDATE
D
SET
D.team_rss_attr_01 = LEFT(S.mkt_prodchar_13,25)
,D.team_rss_attr_02 = LEFT(S.mkt_prodchar_14,25)
,D.team_rss_attr_03 = LEFT(S.mkt_prodchar_15,25)
,D.team_rss_attr_04 = LEFT(S.mkt_prodchar_16,25)
,D.team_rss_attr_05 = LEFT(S.mkt_prodchar_17,25)
,D.team_rss_attr_08 = LEFT(S.mkt_prodchar_19,25)
FROM
tbl_Ref_Prod P
INNER JOIN tblWGDS_Ref_Prod_Market S ON S.prod_key=P.prod_key
INNER JOIN tbl_Ref_Attr_Prod_Team D ON D.prod_key=P.prod_key
WHERE
P.geo_id='840'
AND P.prod_type_id=1 </frame>
</executionStack>
<inputbuf>
select @@SPID
begin transaction test
UPDATE
D
SET
D.team_rss_attr_01 = LEFT(S.mkt_prodchar_13,25)
,D.team_rss_attr_02 = LEFT(S.mkt_prodchar_14,25)
,D.team_rss_attr_03 = LEFT(S.mkt_prodchar_15,25)
,D.team_rss_attr_04 = LEFT(S.mkt_prodchar_16,25)
,D.team_rss_attr_05 = LEFT(S.mkt_prodchar_17,25)
,D.team_rss_attr_08 = LEFT(S.mkt_prodchar_19,25)
FROM
tbl_Ref_Prod P
INNER JOIN tblWGDS_Ref_Prod_Market S ON S.prod_key=P.prod_key
INNER JOIN tbl_Ref_Attr_Prod_Team D ON D.prod_key=P.prod_key
WHERE
P.geo_id='840'
AND P.prod_type_id=1 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1660625" dbid="7" subresource="FULL" objectname="WMGDS_Ref.dbo.tbl_Ref_Attr_Prod_Team" id="lock16e2f2b280" mode="X" associatedObjectId="72057594191085568">
<owner-list>
<owner id="process386ed48188" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process3980de4558" mode="IS" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1593417" dbid="7" subresource="FULL" objectname="WMGDS_Ref.dbo.tbl_Ref_Attr_Prod_Team" id="lock18db89be00" mode="U" associatedObjectId="72057594191085568">
<owner-list>
<owner id="process3980de4558" mode="IS" />
</owner-list>
<waiter-list>
<waiter id="process386ed48188" mode="X" requestType="convert" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
我不熟悉 SQL 死锁，所以任何帮助将不胜感激....
I'm not familiar with SQL deadlocks, so any help would be appreciated....
推荐答案
导致死锁的两个查询是下面的SELECT(process id="process3980de4558"):
The two queries causing the deadlock are the SELECT below (process id="process3980de4558"):
select @existing = team_it_cube_attr_05 from tbl_Ref_Attr_Prod_Team where prod_id = @rec_key
以及下面的 UPDATE 查询 (process id="process386ed48188"):
And the UPDATE query below (process id="process386ed48188"):
UPDATE D
SET D.team_rss_attr_01 = LEFT(S.mkt_prodchar_13,25)...
<resource-list> 部分指出 SELECT 查询在页面上拥有一个排他 (X) 锁并试图获取一个意图共享 (IS) 在读取数据时锁定另一个页面.UPDATE 查询已经拥有一个 IS 锁，并试图在页面上获取 X 锁以执行更新.
The <resource-list> section notes the SELECT query owned an exclusive (X) lock on a page and was trying to acquire an intent-shared (IS) lock on another page while it was reading data. The UPDATE query already owned an IS lock and was tryign to acquire an X lock on a page to perform the update.
给定对这个表的连接:
...from tbl_Ref_Attr_Prod_Team where prod_id = @rec_key...
...INNER JOIN tbl_Ref_Attr_Prod_Team D ON D.prod_key=P.prod_key...
SELECT 查询已经拥有一个排它锁.这可能意味着它是在先前查询中已经执行了 UPDATE 的更大事务的一部分.将维护来自先前查询的锁以在事务期间保持数据完整性(取决于 事务隔离级别).
The SELECT query already owns an exclusive lock. This probably means that it is part of a larger transaction that has already performed an UPDATE in a prior query. Locks from prior queries will be maintained to preserve data integrity during the transaction (depending on the transaction isolation level).
UPDATE 查询需要读取表 tbl_Ref_Attr_Prod_team.它在读取数据时获取页和行上的意图共享锁.当 UPDATE 查询找到匹配的行时，它会尝试将 IS 锁转换为 X 锁.IS 锁与 X 锁不兼容. 因为 SELECT 查询已经在这些页面中的一个或多个页面上拥有 IS 锁，所以查询之间会相互死锁.
The UPDATE query needs to read the table tbl_Ref_Attr_Prod_team. It acquires intent-shared locks on pages and rows while reading data. When the UPDATE query finds the matching rows, it will attempt to convert the IS locks into X locks. IS locks are not compatible with X locks. Because the SELECT query already has an IS lock on one or more of those pages, the queries deadlock with each other.
一个可能的原因是缺少 tbl_Ref_Attr_Prod_team.prod_key 上的索引.如果此列没有索引，UPDATE 查询将扫描 tbl_Ref_Attr_Prod_team 表中的所有行.
One possible cause would be missing indexes on tbl_Ref_Attr_Prod_team.prod_key. Without an index on this column, the UPDATE query will be scanning all of the rows in the table tbl_Ref_Attr_Prod_team.
即使 prod_key 上存在索引，如果表中有少量行，SQL Server 可能会认为如果查询扫描整个表而不是查找指数.在死锁发生时记录查询计划可以验证这个理论.
Even if an index exists on prod_key, if there are a small number of rows in the table, SQL Server may decide that performance would be better if the query scanned the entire table instead of seeking the index. Recording the query plan when the deadlock occurs would verify this theory.
在暂存新数据库时，我们经常会遇到小表死锁.最初，表很小，表扫描会导致各种死锁.之后，当表更大时，计算出的扫描表的成本超过了寻找索引的成本，不再发生死锁.在行数永远很少的测试环境中，我们使用 FORESEEK 和 WITH INDEX 提示来强制索引查找而不是扫描.我们期待能够通过 SQL Server 2016 的查询存储功能强制执行查询计划.
We encounter small table deadlocks regularly when staging new databases. Initially, the tables are tiny, and table scans cause all sorts of deadlocks. Later, when the tables are larger, the calculated cost of scanning the table exceeds the cost of seeking the index, and the deadlocks no longer occur. In test environments where the number of rows is forever small, we have resorted to using FORESEEK and WITH INDEX hints to force index seeks instead of scans. We are looking forward to being able to force query plans via the query store feature of SQL Server 2016.
这篇关于对表执行选择/更新操作的 SQL 死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
