February 15, 2023 at 1:46 pm
Hi,
could someone go through the deadlock and process flow below and advise of what I may be able to do to "prevent" this deadlock from happening.
one potential change I can think of is to move the creation of the temporary table (tableAAA_PartitionID) to be outside the transaction.
sql server
Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) -- does need to be upgraded - pushing now to go to 2019
Mar 13 2020 14:53:45
Copyright (C) 2017 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
VM with 8 vCpu, 16GB Ram
table #temp_keys is a 7 column table with a mix of varchar and varbinary columns - biggest column is 100 bytes
clustered columnstore index created on that table.
create clustered columnstore on #temp_keys
exec split partition on tableAAA (if new partition - otherwise it reuses partition)
create temporary version of tableAAA (named tableAAA_PartitionID) (not in tempdb!!)
executes (this is the sp_executesql below) insert into the temporary table created above
insert into tableAAA_PartitionID select ..., sum(value) from #temp1 join table b, join table c, join #temp_keys ...
.. other code
user db where this code runs is in simple logging mode, with RCSI enabled, compatibility mode 140 (sql 2017)
this same error happens in more than one db - pretty similar code with very minor differences in some of the joined tables (1 or 2 columns differences only)
the tables involved in one db are
#temp_keys - 121 K rows
#temp_vals - 34 M rows
other table - 51 K rows
on another db (also giving same deadlock)
#temp_keys - 2 M rows
#temp_vals - 200 M rows
other table - 1 M rows
<deadlock>
<victim-list>
<victimProcess id="process2cbde047848" />
</victim-list>
<process-list>
<process id="process2cbde047848" taskpriority="0" logused="796" waitresource="OBJECT: 2:-1594416263:0 " waittime="95" ownerId="571727"
transactionname="AutoCreateQPStats" lasttranstarted="2023-02-14T16:06:17.787" XDES="0x2cbd1504420" lockMode="IS" schedulerid="6" kpid="1552" status="suspended" spid="75"
sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2023-02-14T16:05:39.350" lastbatchcompleted="2023-02-14T16:05:39.350"
lastattention="1900-01-01T00:00:00.350" clientapp="Core Microsoft SqlClient Data Provider" hostname="servername" hostpid="5524" loginname="domain\gmsa_account"
isolationlevel="read committed (2)" xactid="571727" currentdb="2" currentdbname="tempdb" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="5" stmtstart="378" stmtend="7586" sqlhandle="0x0200000084f75c3be2bf6161796d0685f1a26d95c79ed21e0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="5" stmtstart="378" stmtend="7586" sqlhandle="0x0100110001000000207fa026cb02000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
<frame procname="adhoc" line="5" stmtstart="378" stmtend="7586" sqlhandle="0x02000000517fd92fd0607f83002ad5e8e238dc107b3d95150000000000000000000000000000000000000000">
unknown </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="userdbname.dbo.procname" line="654" stmtstart="54234" stmtend="54756" sqlhandle="0x030011008b606438596827015baf000001000000000000000000000000000000000000000000000000000000">
exec sp_executesql
@query
, @queryParams
, @RegionalRunID = @RegionalRunID
, @ReportingYear = @ReportingYear
, @ReportingPeriod = @ReportingPeriod
, @PartitionID = @PartitionID </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 17 Object Id = 946102411] </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="0" objid="-1594416263" subresource="FULL" dbid="2"
objectname="tempdb.dbo.#temp_keys__________________________________________________________________________________________________________0000000000F5"
id="lock2cb82517300" mode="X" associatedObjectId="-1594416263">
<owner-list>
<owner id="process2cbde047848" mode="Sch-S" />
<owner id="process2cbde047848" mode="X" />
<owner id="process2cbde047848" mode="IS" requestType="convert" />
</owner-list>
<waiter-list>
<waiter id="process2cbde047848" mode="IS" requestType="convert" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
February 16, 2023 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply