question

Harish6047 avatar image
Harish6047 asked

DeadLock Issue

We are using SQL Server 2012. Database have Isolation level as Read Committed Snapshot(which is row level locking). Still getting deadlock on table.We even try to disable escalation of locks at a table using following command but deadlocks persisted. ALTER TABLE Table_name SET (LOCK_ESCALATION DISABLE) Please suggest
dbadeadlocksqlserver2012dba-developer
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please post the deadlock XML statement
1 Like 1 ·
Harish6047 avatar image
Harish6047 answered
{deadlock> {victim-list> {victimProcess id="process133f03d498" /> {/victim-list> {process-list> {process id="process133f03d498" taskpriority="0" logused="3308" waitresource="RID: 11:1:819250:71" waittime="2377" ownerId="29578701" transactionname="implicit_transaction" lasttranstarted="2017-11-09T09:29:43.397" XDES="0x10b0b716a8" lockMode="U" schedulerid="6" kpid="7332" status="suspended" spid="210" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-09T09:29:45.977" lastbatchcompleted="2017-11-09T09:29:45.397" lastattention="1900-01-01T00:00:00.397" clientapp="jTDS" hostname="HOSTNAME" hostpid="123" loginname="LOGINNAME" isolationlevel="read committed (2)" xactid="29578701" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> {executionStack> {frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000ee409c1fd278abb7f476707399656cbedec9d1960000000000000000000000000000000000000000"> update [TABLENAME] set [COLUMNNAME]=[COLUMNNAME]-1 where [PRIMARYKEY]= @P0 {/frame> {/executionStack> {inputbuf> (@P0 nvarchar(4000))update [TABLENAME] set [COLUMNNAME]=[COLUMNNAME]-1 where [PRIMARYKEY]= @P0 {/inputbuf> {/process> {process id="processc70140188" taskpriority="0" logused="85392" waitresource="RID: 11:1:819250:30" waittime="2196" ownerId="29574187" transactionname="implicit_transaction" lasttranstarted="2017-11-09T09:29:16.427" XDES="0x1150f5d6a8" lockMode="U" schedulerid="4" kpid="384" status="suspended" spid="141" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-11-09T09:29:46.157" lastbatchcompleted="2017-11-09T09:29:45.470" lastattention="1900-01-01T00:00:00.470" clientapp="jTDS" hostname="HOSTNAME" hostpid="123" loginname="LOGINNAME" isolationlevel="read committed (2)" xactid="29574187" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> {executionStack> {frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000001c3c261d27cfa98e13a9cb06f6c30e40cfcaa4a50000000000000000000000000000000000000000"> update [TABLENAME] set [COLUMNNAME]=[COLUMNNAME]-1 where [PRIMARYKEY]= @P0 {/frame> {/executionStack> {inputbuf> (@P0 nvarchar(4000))update [TABLENAME] set [COLUMNNAME]=[COLUMNNAME]-1 where [PRIMARYKEY]= @P0 {/inputbuf> {/process> {/process-list> {resource-list> {ridlock fileid="1" pageid="819250" dbid="11" objectname="TABLENAME" id="lock1236714900" mode="X" associatedObjectId="72057599198953472"> {owner-list> {owner id="processc70140188" mode="X" /> {/owner-list> {waiter-list> {waiter id="process133f03d498" mode="U" requestType="wait" /> {/waiter-list> {/ridlock> {ridlock fileid="1" pageid="819250" dbid="11" objectname="TABLENAME" id="lockdacb14f00" mode="X" associatedObjectId="72057599198953472"> {owner-list> {owner id="process133f03d498" mode="X" /> {/owner-list> {waiter-list> {waiter id="processc70140188" mode="U" requestType="wait" /> {/waiter-list> {/ridlock> {/resource-list> {/deadlock>
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Those locks are row locks, so please but lock escalation back on as it has no effect with what your seeing. The issue will be down to the implicit transactions that your doing and not committing quick enough
0 Likes 0 ·
Hi, Thank you for your analysis Yes, this query is executed multiple times by different users using YAWL (Yet another workflow language). This table is very small table of 80 records and even update is quick. Actually i tried to increase deadlock timeout but that too didn't work. Not sure what i need to do on transaction level. Regards, Harish Banjan
0 Likes 0 ·
but what else happens in the workflow, the transaction will hold the lock until the whole workflow has finished. you need to get jTDS to stop executing implicit transactions
0 Likes 0 ·
sajikumar.vk avatar image
sajikumar.vk answered
we need to track origin of these multiple updates to [TABLENAME] , use this query to get all running queries SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time, req.Start_Time, DB_NAME(database_id) [Database] FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext where database_id = 11 /*--------------------------------------------------------------------------------------------------------------------------*/ the below one will help you to get the lock status on [TABLENAME ------------------------------------------------------------------------------------------------------------------------------------------- SELECT OBJECT_NAME(P.object_id) AS TableName, Resource_type, request_session_id FROM sys.dm_tran_locks L join sys.partitions P ON L.resource_associated_entity_id = p.hobt_id WHERE OBJECT_NAME(P.object_id) = 'TABLENAME' I think here you have to check whether these updates are triggering from any loop or multiple instance of the same code ..etc, ..
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sajikumar.vk avatar image
sajikumar.vk answered
keep another table to insert the required values rather than updating TABLENAME. create a job to have bulk update from the new table to the TABLENAME. schedule the job at any desired interval within the permissible latency frequency. make obsolete all the successfully updated records in the new table
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Harish6047 avatar image
Harish6047 answered
The workflow only trigger this update. Application call this workflow.We try to redirect Oracle Database to this workflow and try to update...we couldn't find any deadlock on Oracle DB but when we do it on SQL Server same deadlock occurred.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

did you change the actual code of the workflow when you switched from Oracle to SQL? Remember PL/SQL and T-SQL are two completely different languages, while they follow the ANSII standards, one is written to work with cursors the other is written to work with sets. My guess is the code base is still behaving as if its geared for Oracle and is wanting to loop rather than interact with the data all in one go.
0 Likes 0 ·
sajikumar.vk avatar image
sajikumar.vk answered
please share the table schema ...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.