April 4, 2012 at 1:16 am
Hello everyone,
I have been seeing this deadlock for the last few days daily. I am not very familiar with deadlock resolving. I tried to google. Very difficult to understand the deadlock information.
Can you please help me.
Thank you.
2012-04-03 15:17:54.32 spid22s deadlock-list
2012-04-03 15:17:54.32 spid22s deadlock victim=process5c5436e08
2012-04-03 15:17:54.32 spid22s process-list
2012-04-03 15:17:54.32 spid22s process id=process5c5436e08 taskpriority=0 logused=240 waitresource=OBJECT: 14:2121058592:22 waittime=53382 ownerId=3198995449 transactionname=user_transaction lasttranstarted=2012-04-03T15:17:00.937 XDES=0x11a089950 lockMode=IX schedulerid=23 kpid=11512 status=suspended spid=113 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-04-03T15:17:00.937 lastbatchcompleted=2012-04-03T15:17:00.937 clientapp=SQLAgent - TSQL JobStep (Job 0x195B9629CAEC114096FDD7A305E7E79B : Step 1) hostname=MDC-ONL-MSDB02 hostpid=7900 loginname=ONBE\srv_acc_sqldb02 isolationlevel=read committed (2) xactid=3198995449 currentdb=1 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128056
2012-04-03 15:17:54.32 spid22s executionStack
2012-04-03 15:17:54.32 spid22s frame procname=adhoc line=10 stmtstart=568 stmtend=1246 sqlhandle=0x020000001af68701abb6b0d8cd0c17bf3656b6cd55eb64b0
2012-04-03 15:17:54.32 spid22s insert into ABI_ACCOUNTS_DS_TRACK.dbo.ABI_ACCOUNT_ACTION_HISTORY(
2012-04-03 15:17:54.32 spid22s DATE_CREATED, ABI_ACCOUNT_ID, ABI_ACCOUNT_ACTION_ID, GENOME_ID, IP_ADDRESS, DATE_PROCESSED)
2012-04-03 15:17:54.32 spid22s select DATE_CREATED, ABI_ACCOUNT_ID, ABI_ACCOUNT_ACTION_ID, GENOME_ID, IP_ADDRESS, @starttime
2012-04-03 15:17:54.32 spid22s from ABI_ACCOUNTS.accounts_core.ABI_ACCOUNT_ACTION_HISTORY
2012-04-03 15:17:54.32 spid22s -- print @@rowcount
2012-04-03 15:17:54.32 spid22s inputbuf
2012-04-03 15:17:54.32 spid22s declare @starttime datetime = getdate()
2012-04-03 15:17:54.32 spid22s begin transaction
2012-04-03 15:17:54.32 spid22s -- adds a record with specific date the records were moved from
2012-04-03 15:17:54.32 spid22s -- ABI_ACCOUNTS to ABI_ACCOUNTS_DS_TRACK
2012-04-03 15:17:54.32 spid22s insert into ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED(PROCESSED, HISTORY_DATE)
2012-04-03 15:17:54.32 spid22s values(0, @starttime)
2012-04-03 15:17:54.32 spid22s insert into ABI_ACCOUNTS_DS_TRACK.dbo.ABI_ACCOUNT_ACTION_HISTORY(
2012-04-03 15:17:54.32 spid22s DATE_CREATED, ABI_ACCOUNT_ID, ABI_ACCOUNT_ACTION_ID, GENOME_ID, IP_ADDRESS, DATE_PROCESSED)
2012-04-03 15:17:54.32 spid22s select DATE_CREATED, ABI_ACCOUNT_ID, ABI_ACCOUNT_ACTION_ID, GENOME_ID, IP_ADDRESS, @starttime
2012-04-03 15:17:54.32 spid22s from ABI_ACCOUNTS.accounts_core.ABI_ACCOUNT_ACTION_HISTORY
2012-04-03 15:17:54.32 spid22s -- print @@rowcount
2012-04-03 15:17:54.32 spid22s delete from ABI_ACCOUNTS.accounts_core.ABI_ACCOUNT_ACTION_HISTORY
2012-04-03 15:17:54.32 spid22s -- print @@rowcount
2012-04-03 15:17:54.32 spid22s commit transaction
2012-04-03 15:17:54.32 spid22s process id=process2127402c8 taskpriority=0 logused=22421956 waitresource=RID: 14:1:114:14 waittime=146 ownerId=3198386201 transactionname=user_transaction lasttranstarted=2012-04-03T15:15:09.020 XDES=0x7e2af3950 lockMode=U schedulerid=9 kpid=1136 status=suspended spid=697 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-04-03T15:15:01.410 lastbatchcompleted=2012-04-03T15:15:01.410 clientapp=SQLAgent - TSQL JobStep (Job 0xE5A2C7BFA44A444AB1BFB301849972D6 : Step 1) hostname=MDC-ONL-MSDB02 hostpid=7900 loginname=ONBE\srv_acc_sqldb02 isolationlevel=read committed (2) xactid=3198386201 currentdb=1 lockTimeout=4294967295 clientoption1=536870944 clientoption2=128056
2012-04-03 15:17:54.32 spid22s executionStack
2012-04-03 15:17:54.32 spid22s frame procname=adhoc line=9 stmtstart=24 sqlhandle=0x02000000c90ce619d6d9b1c8d71a63017ebef3a8aef10b67
2012-04-03 15:17:54.32 spid22s DELETE [ABI_ACCOUNTS_DS_TRACK].[dbo].[HISTORY_DATES_PROCESSED] WHERE [PROCESSED]=@1
2012-04-03 15:17:54.32 spid22s frame procname=adhoc line=9 stmtstart=416 stmtend=586 sqlhandle=0x020000005e554307f213a8b5f209fef7de4c5cfdf606e45d
2012-04-03 15:17:54.32 spid22s delete from ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED
2012-04-03 15:17:54.32 spid22s where PROCESSED = 1
2012-04-03 15:17:54.32 spid22s inputbuf
2012-04-03 15:17:54.32 spid22s begin tran
2012-04-03 15:17:54.32 spid22s delete h
2012-04-03 15:17:54.32 spid22s from ABI_ACCOUNTS_DS_TRACK.dbo.ABI_ACCOUNT_ACTION_HISTORY h
2012-04-03 15:17:54.32 spid22s join ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED p
2012-04-03 15:17:54.32 spid22s on h.DATE_PROCESSED = p.HISTORY_DATE
2012-04-03 15:17:54.32 spid22s where p.PROCESSED = 1
2012-04-03 15:17:54.32 spid22s delete from ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED
2012-04-03 15:17:54.32 spid22s where PROCESSED = 1
2012-04-03 15:17:54.32 spid22s commit tran
2012-04-03 15:17:54.32 spid22s resource-list
2012-04-03 15:17:54.32 spid22s objectlock lockPartition=22 objid=2121058592 subresource=FULL dbid=14 objectname=ABI_ACCOUNTS_DS_TRACK.dbo.ABI_ACCOUNT_ACTION_HISTORY id=lock978b20880 mode=X associatedObjectId=2121058592
2012-04-03 15:17:54.32 spid22s owner-list
2012-04-03 15:17:54.32 spid22s owner id=process2127402c8 mode=X
2012-04-03 15:17:54.32 spid22s waiter-list
2012-04-03 15:17:54.32 spid22s waiter id=process5c5436e08 mode=IX requestType=wait
2012-04-03 15:17:54.32 spid22s ridlock fileid=1 pageid=114 dbid=14 objectname=ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED id=lock2f6558280 mode=X associatedObjectId=72057594038910976
2012-04-03 15:17:54.32 spid22s owner-list
2012-04-03 15:17:54.32 spid22s owner id=process5c5436e08 mode=X
2012-04-03 15:17:54.32 spid22s waiter-list
2012-04-03 15:17:54.32 spid22s waiter id=process2127402c8 mode=U requestType=wait
April 4, 2012 at 1:29 am
You have two agents that are updating the account and account history actions tables. The locks for these are interfering with each other. In agent 1 ( inserting new objects) the locks are taking for the account and then the account action, in agent 2 ( deleting accounts and history) the locks are taken on account action then account. This is causing the deadlock on the account object row.
The locks should be taken in the same order in both agents. If you post the adhoc tsql code from the two agents then we may be able to assist more.
Fitz
April 4, 2012 at 4:08 am
Thanks for the reply.
job 1. Runs every two minutes.
begin transaction
-- adds a record with specific date the records were moved from
-- ABI_ACCOUNTS to ABI_ACCOUNTS_DS_TRACK
insert into ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED(PROCESSED, HISTORY_DATE)
values(0, @starttime)
insert into ABI_ACCOUNTS_DS_TRACK.dbo.ABI_ACCOUNT_ACTION_HISTORY(
DATE_CREATED, ABI_ACCOUNT_ID, ABI_ACCOUNT_ACTION_ID, GENOME_ID, IP_ADDRESS, DATE_PROCESSED)
select DATE_CREATED, ABI_ACCOUNT_ID, ABI_ACCOUNT_ACTION_ID, GENOME_ID, IP_ADDRESS, @starttime
from ABI_ACCOUNTS.accounts_core.ABI_ACCOUNT_ACTION_HISTORY
-- print @@rowcount
delete from ABI_ACCOUNTS.accounts_core.ABI_ACCOUNT_ACTION_HISTORY
Commit tran
Job 2. Runs every 1 hour.
Begin Tran
delete h
from ABI_ACCOUNTS_DS_TRACK.dbo.ABI_ACCOUNT_ACTION_HISTORY h
join ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED p
on h.DATE_PROCESSED = p.HISTORY_DATE
where p.PROCESSED = 1
delete from ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED
where PROCESSED = 1
-- print @@rowcount
commit transaction
April 4, 2012 at 9:11 am
Job 2 locks the table objects in exactly the reverse of job 1. Try using the code below for job 2. This would cut down the chances of having deadlocks.
Begin Tran
-- this will find the rows and place an update lock on them so that
-- the locks are taken in the same order as job 1
Select * from ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED (updlock)
Where processed = 1
delete h
from ABI_ACCOUNTS_DS_TRACK.dbo.ABI_ACCOUNT_ACTION_HISTORY h
join ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED p
on h.DATE_PROCESSED = p.HISTORY_DATE
where p.PROCESSED = 1
delete from ABI_ACCOUNTS_DS_TRACK.dbo.HISTORY_DATES_PROCESSED
where PROCESSED = 1
-- print @@rowcount
commit transaction
Fitz
April 4, 2012 at 8:24 pm
Thank you very much !!!!!.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply