February 11, 2020 at 11:04 pm
Hello,
Can someone help me with the deadlock issue that we are currently facing at our SQL Server 2014 server?
Some background,
1. Table NEXT_FIELD_KEY hold the "field name" and next running key (12 digit numeric running number) of every other table in our database. Any table's primary key (PK) (running #) value created based on this NEXT_FIELDtable
2. Victim - sp_CreateFile - a FILE table record creation (many rows - 1000+)
3. Succeeded - sp_CreateAudit - AUDIT table record creation (only one or 2 rows max) when updating delivery details SP.
I did tried most of the basic things such as index statistics, query optimization including UPDLOCK on NEXT_FIELD_KEY table.
There is still no luck to avoid the deadlock!
Please shed some lights when you have sometime.
Thanks in advance.
deadlock log
--------------
<deadlock>
<victim-list>
<victimProcess id="process87a061848" />
</victim-list>
<process-list>
<process id="process87a061848" taskpriority="0" logused="2640" waitresource="KEY: 7:72057594043957248 (f52ca08771a2)" waittime="6684" ownerId="14246349" transactionname="user_transaction" lasttranstarted="2020-02-11T14:15:41.247" XDES="0x86e184d90" lockMode="U" schedulerid="10" kpid="10352" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-02-11T14:15:41.260" lastbatchcompleted="2020-02-11T14:15:41.257" lastattention="1900-01-01T00:00:00.257" clientapp=".Net SqlClient Data Provider" hostname="LMS-SVR" hostpid="8876" loginname="lmsuser" isolationlevel="read committed (2)" xactid="14246349" currentdb="7" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="LMS.dbo.sp_GetNextKey" line="38" stmtstart="3054" stmtend="3238" sqlhandle="0x03000700a457fe4cecf299005dab000001000000000000000000000000000000000000000000000000000000">
IF NOT EXISTS ( SELECT field_name FROM dbo.NEXT_FIELD_KEY (UPDLOCK) WHERE field_name = @field_name </frame>
<frame procname="LMS.dbo.sp_CreateFile" line="76" stmtstart="4650" stmtend="4878" sqlhandle="0x0300070096bcf848b7f599005dab000001000000000000000000000000000000000000000000000000000000">
EXECUTE dbo.sp_GetNextKey
@field_name = 'file_key',
@next_field_value = @file_key OUTPUT </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1224260758] </inputbuf>
</process>
<process id="process87a857468" taskpriority="0" logused="24512" waitresource="KEY: 7:72057594043957248 (be5ac98c0072)" waittime="4391" ownerId="14245804" transactionname="user_transaction" lasttranstarted="2020-02-11T14:15:40.420" XDES="0x5ff3b0d90" lockMode="U" schedulerid="21" kpid="10392" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-02-11T14:15:43.537" lastbatchcompleted="2020-02-11T14:15:40.437" lastattention="2020-02-11T14:15:38.603" clientapp=".Net SqlClient Data Provider" hostname="LMS-SVR" hostpid="8876" loginname="lmsuser" isolationlevel="read committed (2)" xactid="14245804" currentdb="7" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="LMS.dbo.sp_GetNextKey" line="38" stmtstart="3054" stmtend="3238" sqlhandle="0x03000700a457fe4cecf299005dab000001000000000000000000000000000000000000000000000000000000">
IF NOT EXISTS ( SELECT field_name FROM dbo.NEXT_FIELD_KEY (UPDLOCK) WHERE field_name = @field_name </frame>
<frame procname="LMS.dbo.sp_CreateAudit" line="268" stmtstart="28502" stmtend="28740" sqlhandle="0x03000700615bc646a7009a005dab000001000000000000000000000000000000000000000000000000000000">
EXECUTE dbo.sp_GetNextKey
@field_name = 'audit_key',
@next_field_value = @audit_key OUTPUT </frame>
<frame procname="dbo.sp_UpdateDeliveryDetails" line="758" stmtstart="63134" stmtend="64104" sqlhandle="0x03000700a4c94635dbfb99005dab000001000000000000000000000000000000000000000000000000000000">
EXECUTE @total_errors = dbo.sp_CreateAudit
@audit_key = @audit_key,
@user_id_key = @update_user_id_key,
@audit_dte = @update_dte,
@audit_action = @CREATE_CUST,
@audit_description = @audit_description,
@create_err_table = 0 </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 893831588] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594043957248" dbid="7" objectname="LMS.dbo.NEXT_FIELD_KEY" indexname="NEXT_FIELD_PK" id="lock60a78ea00" mode="X" associatedObjectId="72057594043957248">
<owner-list>
<owner id="process87a857468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process87a061848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594043957248" dbid="7" objectname="LMS.dbo.NEXT_FIELD_KEY" indexname="NEXT_FIELD_PK" id="lock65eb47c80" mode="X" associatedObjectId="72057594043957248">
<owner-list>
<owner id="process87a061848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process87a857468" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Thanks very much for your help in advance.
February 12, 2020 at 12:43 pm
Not enough to go on.
First, fundamentally a deadlock is a performance problem. If all the locking occurs so fast that there's no interference, you will never see deadlocks. So, I know you said you've tuned the queries, but then mentioned the UPDLOCK hint as "optimization." I would suggest reassessing the query tuning.
Second, deadlocks are all about the deadly embrace. I need something you have and I you need something I have. This comes about in queries not because of the single statement we're looking at here in the deadlock graph, but the queries before and after it (except in the case of parallelism deadlocks, which you do not have here). So, we can't tell you what's causing the problem when all we can see is the single offending statement. You need to identify the resources before and after that are the problem. Make sure all your code in all your procedures, accesses tables (indexes, whatever), in the same order. If you have lots of conditional statements and stuff that can disrupt the flow, look to that to see if there's something taking locks out before hand.
Generally, tuning the queries and ensuring process order are the best fixes for deadlocks. Implementing locking hints, or any query hints for that matter, are a distant second. Fix the code. It's a code issue. Trying to slap hints in as a shortcut generally won't work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2020 at 8:45 pm
...
1. Table NEXT_FIELD_KEY hold the "field name" and next running key (12 digit numeric running number) of every other table in our database. Any table's primary key (PK) (running #) value created based on this NEXT_FIELDtable
...
I believe this design is going to have locking and contention issues no mater how much you tune the specific queries involved in the deadlock. Can I ask why the process of getting the PK value was defined like this instead of using identity property or a sequence?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply