asked
Deadlock query
Hi, While running a code I was encountered with a deadlock error, Below are the logs generated. Could you please help deadlock victim=processd8c3948 process-list process id=processd8c3948 taskpriority=0 logused=0 waitresource=KEY: 4:72057594056015872 (87385e3220de) waittime=4015 ownerId=24443299 transactionname=user_transaction lasttranstarted=2013-10-19T03:09:56.847 XDES=0x16fb5b950 lockMode=S schedulerid=14 kpid=6460 status=suspended spid=62 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2013-10-19T03:02:47.287 lastbatchcompleted=2013-10-19T03:02:47.283 clientapp=SQLAgent - TSQL JobStep (Job 0xD260EFF41FC2874D89988C99C9D5A7B7 : Step 2) hostname=VMGDWH hostpid=5752 loginname=VMGDWH\SeaChange isolationlevel=read committed (2) xactid=24443299 currentdb=4 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056 executionStack frame procname=msdb.dbo.dispatcher_sp_addqueueitem line=47 stmtstart=3546 stmtend=5246 sqlhandle=0x0300040043b1ae632fddf20057a200000100000000000000 IF @arg_prevent_duplicate_maintenance = 1 frame procname=IdwData.dbo.bcpInJob line=58 stmtstart=4200 stmtend=5494 sqlhandle=0x03000700ef198f3532cdf20057a200000100000000000000 exec msdb..dispatcher_sp_addqueueitem frame procname=IdwData.dbo.wait_for_checkpoints line=85 stmtstart=4456 stmtend=4702 sqlhandle=0x03000700283e83363fcdf20057a200000100000000000000 exec bcpInJob @serverType, @queueItemId, @queueItemBaseId, @siteId, @filename, @lastUpdate, @lastCount, @lastId; frame procname=adhoc line=1 stmtstart=32 sqlhandle=0x01000100d0c24917701cf229020000000000000000000000 exec IdwData.dbo.wait_for_checkpoints @queueItemId = 53774, @queueItemBaseId = 53714, @serverType = 1, @siteCount = 47, @timeout = 900, @resendSql = 1, @with_bcpins = 1 with recompile frame procname=mssqlsystemresource.sys.sp_executesql line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000 sp_executesql frame procname=adhoc line=28 stmtstart=2026 sqlhandle=0x02000000bc3887047fc64c77cc146c3db14fd5068df64e0d EXEC sp_executesql @sql inputbuf -- Declare variables DECLARE @sql NVARCHAR(500) DECLARE @logmessage NVARCHAR(1000) -- Get the SQL code to run this command SELECT FROM WHERE -- If in the odd case that this SQL code does not exist anymore IF @sql IS NULL BEGIN END -- Replace some variables that can be used SET @sql = REPLACE(REPLACE(@sql, '$ID$', '53774'), '$BASEID$', '53714') -- Execute the SQL Code, whatever it is SET @logmessage = 'Executing SQL Code: ' + @sql EXEC msdb..dispatcher_sp_logqueueitemmessage @arg_queueitem_id = 53774, @arg_levelkey = 'DEBUG', @arg_activity = 'Executing Queue Item', @arg_message = @logmessage EXEC sp_ex process id=process861c748 taskpriority=0 logused=716 waitresource=KEY: 4:72057594056146944 (27757b457b4f) waittime=4015 ownerId=24443297 transactionname=user_transaction lasttranstarted=2013-10-19T03:09:56.847 XDES=0x1dcafb950 lockMode=X schedulerid=6 kpid=2760 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-10-14T14:45:00.967 lastbatchcompleted=2013-10-14T14:45:00.967 clientapp=SQLAgent - TSQL JobStep (Job 0x4E130E04460D984B9650F8D1582397FE : Step 1) hostname=VMGDWH hostpid=5752 loginname=VMGDWH\SeaChange isolationlevel=read committed (2) xactid=24443297 currentdb=4 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128056 executionStack frame procname=msdb.dbo.dispatcher_sp_starttopqueueitem line=73 stmtstart=4134 stmtend=4558 sqlhandle=0x03000400ee1d8b6646ddf20057a200000100000000000000 UPDATE frame procname=msdb.dbo.dispatcher_sp_keepqueuesrunning line=71 stmtstart=6618 stmtend=6872 sqlhandle=0x0300040029cea978beddf20057a200000100000000000000 EXEC @ReturnCode = msdb..dispatcher_sp_starttopqueueitem @arg_queue_id = @CurrentQueueId frame procname=adhoc line=1 sqlhandle=0x0100040082a4a00a603b239a010000000000000000000000 EXEC msdb..dispatcher_sp_keepqueuesrunning inputbuf EXEC msdb..dispatcher_sp_keepqueuesrunning resource-list keylock hobtid=72057594056015872 dbid=4 objectname=msdb.dbo.dispatcher_tbl_QueueItems indexname=dispatcher_idx_QueueItems_id id=lock3231ff780 mode=X associatedObjectId=72057594056015872 owner-list owner id=process861c748 mode=X waiter-list waiter id=processd8c3948 mode=S requestType=wait keylock hobtid=72057594056146944 dbid=4 objectname=msdb.dbo.dispatcher_tbl_QueueItems indexname=dispatcher_idx_QueueItems_getqueueitemlistfields id=lock1bf3b6580 mode=S associatedObjectId=72057594056146944 owner-list owner id=processd8c3948 mode=S waiter-list waiter id=process861c748 mode=X requestType=wait
Hello All, While reviewing the query more in deep and checking the line items below : keylock hobtid=72057594056015872 dbid=4 objectname=msdb.dbo.dispatcher_tbl_QueueItems **indexname=dispatcher_idx_QueueItems_id id=lock3231ff780** mode=X associatedObjectId=72057594056015872 owner-list owner id=process861c748 mode=X waiter-list waiter id=processd8c3948 mode=S requestType=wait keylock hobtid=72057594056146944 dbid=4 objectname=msdb.dbo.dispatcher_tbl_QueueItems **indexname=dispatcher_idx_QueueItems_getqueueitemlistfields id=lock1bf3b6580** mode=S associatedObjectId=72057594056146944 owner-list owner id=processd8c3948 mode=S waiter-list I found that keys are using different index on a single table(msdb.dbo.dispatcher_tbl_QueueItems). So index may be giving problem while updating the table. My question is does removing a non clustered index can solve the problem since it may be only a Performance parameter. ?
It's possible it will help the deadlock, but hurt other queries. Presumably the non-clustered index is there for a reason.
The index was used to improve performance , but the queries are giving me deadlock issue almost twice a week if they are running on daily basis.
Remember, the issue is still performance, not the index. It's possible that updating the index is slowing things down, but why are transactions so slow in general that you're getting deadlocks. That's the principal thing to address.
From what I can see within that section of the deadlock graph you have a procedure, msdb.dbo.dispatcher_sp_addqueueitem that is deadlocking with another, IdwData.dbo.bcpInJob. It also lists another proc, IdwData.dbo.wait_for_checkpoints and two ad hoc statements. Focus on the first two to start with. Line 47 in the first proc is conflicting with Line 58 in the second. That's your starting point. Remember, deadlocks are primarily a performance issue. You need to understand why you're holding transactions open so long they get the chance to conflict with each other. You can reduce some of the conflict by ensuring that locking is done in the same order for every query. This means accessing tables in the same order, TableA, TableB, TableC, etc. If you mix the order TableA, TableC, TableB... you may see deadlocks in long running transactions. Beyond that, I don't see enough to tell you specifically what the issue is.
Tune the queries. Make sure you have good indexes in place. Avoid accessing the tables in order. Pretty much work through standard performance tuning is the best way to address this.
Thank you Grant for the suggestions. The transactions running in the above queries depends upon the time taken for data to move in staging tables. Also, this situation happens very frequently like once in 100 runs. Could you please suggest some way to mitigate these issues.
15 People are following this question.
