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
{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>
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
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
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
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, ..
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
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.
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.
please share the table schema ...
19 People are following this question.
