Detected but now Prevent This Deadlock

Question

Post reply

Detected but now Prevent This Deadlock

dallas13

SSCertifiable

Points: 7491
More actions
March 26, 2010 at 3:28 pm

#217191

Hi all,
I have one prod db (74 MB) which is used for business object reports.
There is a system process that runs 10 times per second on this particular database.
and it checks certain things in one particular table (select statement) and purges data
if the process finds it need to delete it. (delete statement).
According to BO team, this process is runnning since long time but from last week
I am getting intermittent key level deadlock alerts.(got 5 alerts in a week, all came at different time)
No one else is using this db.
So when I ran
select db_name(dbid),* from master..sysprocesses(nolock)
where spid > 50 and db_name(dbid) ='db_name'
I was able to see 2 SPIDs are there. they are logged in since 2010-02-15 12:27:18.980.
spidblockedwaittypewaittimelastwaittypecpuphysical_iomemusagelogin_timelast_batchopen_transtatus
5700x00000MISCELLANEOUS 326595923014922/15/10 12:27 PM3/26/10 5:05 PM0sleeping
5800x00000MISCELLANEOUS 325607523141922/15/10 12:27 PM3/26/10 5:05 PM0sleeping
My detail deadlock report says that deadlock occurs on this particular object (CMS_InfoObjects5).
when I ran profiler to see what actually they are doing, I found this, (see attachemnt for trace)
from trace file,
both spids are issuing these statements,
SELECT dbo.CMS_InfoObjects5.ObjectID FROM dbo.CMS_InfoObjects5 WITH (NOLOCK) WHERE NextRunTime <= '2010 03 26 20 26 51 476' AND SI_RUNNABLE_OBJECT = 1 AND TypeID = 290 AND floor(ObjectID / 2) - (floor( floor(ObjectID / 2) /2 ) * 2) = 1 ORDER BY NextRunTime ASC
go
SELECT dbo.CMS_InfoObjects5.ObjectID FROM dbo.CMS_InfoObjects5 WITH (NOLOCK) WHERE NextRunTime <= '2010 03 26 20 26 51 476' AND SI_RUNNABLE_OBJECT = 1 AND SI_TYPEID_MACHINE = 334 AND floor(ObjectID / 2) - (floor( floor(ObjectID / 2) /2 ) * 2) = 1 ORDER BY NextRunTime ASC
go
(which means same query gets executed so many times here with
If you take a look at this data below, 58 and 57 are two SPIDs and they are running following queries every 10 msec or every 7 msecs respectively.
(observe inside trace spid and starttime columns carefully)
From this it is understandable that, if first process takes little long then second will get stuck or deadlock will occur. It uses objected column which has unique, non clustered index. I have checked fragmentation of indexes and that is fine, we rebuild/reorganize indexes regularly to reduce fragmentation and stop slow performance.
So I‘ve detected the deadlock but The Question is, how can I prevent it?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

dallas13 SSCertifiable Points: 7491 More actions · Answer 1

I was analysing my detailed deadlock report and it looks like...

--For Spid =58

- <process id="processbaf438" taskpriority="0" logused="0" waitresource="KEY: 10:72057594052214784 (4b007efe08c7)" waittime="2421" ownerId="474019538" transactionname="SELECT" lasttranstarted="2010-03-22T11:15:45.360" XDES="0x80754d48" lockMode="S" schedulerid="1" kpid="7796" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2010-03-22T11:15:45.360" lastbatchcompleted="2010-03-22T11:15:41.030" clientapp="BusinessObjects Enterprise" hostname="USNYP2SWS12" hostpid="8216" loginname="bobje" isolationlevel="read committed (2)" xactid="474019538" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

--------------------------------------------------------------------

--For Spid =57

I can see its same for both spid KEY: 10:72057594052214784 and when I tried to find some info abt this

select * from sys.partitions

where hobt_id =72057594052214784

I cant see anything in my system. Howz that possible?? there should be something present.but nothing is there. Experts, please explain me... Thanks

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

Hello guys, any updates ... I really appreicate if someone can focus more on this.. Thanks

kanjasaha 6620 Newbie Points: 6 More actions · Answer 3

Greetings!

What is the delete statement you mentioned at the beginning of the post that is participating in the deadlock?

Is it possible to post the log containing all processes participating in one deadlock?

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

It continuously checks some records by executing select commands and when needed it issues delete statement which is like this below.

DELETE FROM dbo.CMS_InfoObjects5 WHERE ObjectID in (705469...); DELETE FROM dbo.CMS_RELATIONS5 WHERE PARENTID in (705469...) OR CHILDID in (705469...); DELETE FROM dbo.CMS_Aliases5 WHERE ObjectID in (705469...);

I believe once its working on this delete, At the same time it issues another select statement and deadlock occurs thats what I believe but how to overcome this things now...

arussell_10 SSCrazy Points: 2110 More actions · Answer 5

You might check out the link below. Near the bottom he offers suggestions on how to avoid deadlocks.

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

andy russell (3/30/2010)

You might check out the link below. Near the bottom he offers suggestions on how to avoid deadlocks.

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx%5B/quote%5D

That, and the 2 follow-on blog posts, are the Bible for deadlock investigation/resolution. Or you can call me, but I am slightly more expensive than the articles...:-D

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

kanjasaha 6620 Newbie Points: 6 More actions · Answer 7

Is it possible to cut and paste the log details for this particular deadlock scenario. I need to know the processes participating in one single deadlock and who is chosen as victim by SQL Server.

The link SSC Rookie provided is indeed extremely helpful. It had helped me a lot to decipher the deadlock scenario I was dealing with!

rameshbhumi Newbie Points: 5 More actions · Answer 8

Hi,

We are also seeing these Queries getting triggered from BO Server to Database. I would like to know what exactly the purpose of these Queries. It will be Great help if any one lut me know what is the function of these Queries.

Regards

Ramesh

LutzM SSC Guru Points: 107049 More actions · Answer 9

rameshbhumi (2/14/2011)
Hi,
We are also seeing these Queries getting triggered from BO Server to Database. I would like to know what exactly the purpose of these Queries. It will be Great help if any one lut me know what is the function of these Queries.
Regards
Ramesh

What specific queries do you refer to?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

rameshbhumi (2/14/2011)
Hi,
We are also seeing these Queries getting triggered from BO Server to Database. I would like to know what exactly the purpose of these Queries. It will be Great help if any one lut me know what is the function of these Queries.
Regards
Ramesh

PLEASE start a new thread. This one is almost a year old.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service