SQL deadlock with select/update operations on a table

Question

I have changed the column(prod_id) datatype "nvarchar(25)" to "varchar(50)" then parallel DML operations are failing with deadlock.

The table involved in deadlock - "tbl_Ref_Attr_Prod_Team " Indexes on table

Indexes on table

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>

I'm not familiar with SQL deadlocks, so any help would be appreciated....

Answer 1

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

And the UPDATE query below (process id="process386ed48188"):

UPDATE D
SET D.team_rss_attr_01 = LEFT(S.mkt_prodchar_13,25)...

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.

Given the join against this table:

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

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

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.

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.

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.

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.