Regarding a deadlock scenario
-
hi all,
I am getting a deadlock situation where a SPID 149 is getting blocked by itself. Please see below for sp_who2 execution report.
SPID Status Login HostName BlkBy DBNAME COMMAND CPUTIME DISKIO LastBatch ProgramName
149 sleeping assmic PSCDALPGSMCM01 149 assmic CREATE INDEX 66656 32619 04/10 19:59:28 149
151 sleeping assmic PSCDALPGSMCM01 149 assmic TRUNCATE TABLE 12829 35368 04/10 21:59:29 151
There is one more strange piece of information which is getting printed in the error log.
It is printing that it is getting DEADLOCKED when it is trying to execute CREATE INDEX statement, the issue is that there is no line in the stored procedure which executes CREATE INDEX on any table.
Deadlock encountered .... Printing deadlock information
2007-04-10 20:01:13.28 spid4
2007-04-10 20:01:13.28 spid4 Wait-for graph
2007-04-10 20:01:13.28 spid4
2007-04-10 20:01:13.28 spid4 Node:1
2007-04-10 20:01:13.28 spid4 PAG: 2:5:67512 CleanCnt:2 Mode: X Flags: 0x0
2007-04-10 20:01:13.28 spid4 Grant List 3::
2007-04-10 20:01:13.28 spid4 Owner:0x4a1c3ba0 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:149 ECID:0
2007-04-10 20:01:13.28 spid4 SPID: 149 ECID: 0 Statement Type: CREATE INDEX Line #: 1
2007-04-10 20:01:13.28 spid4 Input Buf: RPC Event: tdc_refresh;1
2007-04-10 20:01:13.28 spid4 Requested By:
2007-04-10 20:01:13.28 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:149 ECID:0 Ec
0x2A3CB528) Value:0x565519a0 Cost42/B87FAE0)2007-04-10 20:01:13.34 spid4 Using 'dbghelp.dll' version '4.0.5'
Then I tried executing sp_lock stored procedure to find out the locks held by 149.
SPID 149 hold X locks on many different resources.
It will be really helpful to me if any body could help me solve this deadlock situation.
Thanks
Venkat.
-
Some indication of the code being called would help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Run DBCC INPUTbuffer to check what exactly the processes 149 and 151 are running.
-
Thanks for your replies!!
I looked at the dbcc input buffer it is just telling that it is an RPC event:
RPC Event: gsa_proc_homepage_cache_refresh;1
Nothing else pretty useful.
I went through the deadlock scenario again. I think this is a deadlock scenario where the process is locked by itself.
In this case 149 is deadlocked with 149 itself because it is trying to access the same resource again and again.
spid dbIDobjIDindId resTyperesourcemode status
149200PAG5:67512 X GRANT
149200PAG5:67512 XWAIT
Initially the spid 149 has taken a Page level lock on a particular page for some modification. Then It has again tried to get a lock on it.
(Note: this resource is on Temporary Database)
1. Is there some way to know more about the page in contention(like which table's data is present in it.)?
2. For which exact statements will SQL server take exclusinve lock?
It would really helpful to me if some one could give hints as to where I can look at it.
Thanks for taking your time to help me out
--Venkat
-
SQL Server takes exclusive locks on a row, page or data when it wants to perform a write operation. You've got a process within your stored procedure that is escalating locks. You can't get much more information outside the stored procedure. You need to open it up & get into the guts of "gsa_proc_homepage_cache_refresh" to see what it's doing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks Grant,
This stored Procedure has many temporary tables. Is there anyway to know more information about the table handled by the page.
So that I can restrict my search accordingly.
I would want one more clarification.
The following is one of the temp table used in the stored procedure:
select a.*
into #final
from #fs_site a , #max b
where a.s_id = b.s_id
and a.name = b.name
and a.date = b.date
create index x1_final on #final(s_id,name, agg_gmt_timestamp)
Is it fine If we create and copy the data to a temp table and then create an index on it?
-
Processes blocking themselves is not a problem. It happens when a process parallels and then has to wait for one of the spawned threads to merge the threads.
Regarding the deadlock, was there a node 2 to the deadlock graph? If so, it would be useful if you could post that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The issues is there was no node 2 involved
Deadlock encountered .... Printing deadlock information
2007-04-10 20:01:13.28 spid4
2007-04-10 20:01:13.28 spid4 Wait-for graph
2007-04-10 20:01:13.28 spid4
2007-04-10 20:01:13.28 spid4 Node:1
2007-04-10 20:01:13.28 spid4 PAG: 2:5:67512 CleanCnt:2 Mode: X Flags: 0x0
2007-04-10 20:01:13.28 spid4 Grant List 3::
2007-04-10 20:01:13.28 spid4 Owner:0x4a1c3ba0 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:149 ECID:0
2007-04-10 20:01:13.28 spid4 SPID: 149 ECID: 0 Statement Type: CREATE INDEX Line #: 1
2007-04-10 20:01:13.28 spid4 Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
2007-04-10 20:01:13.28 spid4 Requested By:
2007-04-10 20:01:13.28 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:149 ECID:0 Ec
0x2A3CB528) Value:0x565519a0 Cost42/B87FAE0)2007-04-10 20:01:13.34 spid4 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 4, PSS = 0x433700c0, EC = 0x433703f0
***************************
SP_LOCK
***************************
spiddbIDobjIDindId resTyperesourcemodestatus
149200PAG3:58375 XGRANT
149200PAG5:67512 XGRANT
149200PAG5:67512 XWAIT
......
150700DB SGRANT
151713830120080TAB Sch-MWAIT
*********************
SP_WHO2
*********************
SPIDStatusLoginHostNameBlkByDBNAMECOMMANDCPUTIMEDISKIOLastBatchProgramName
149 sleeping assurentPSCDALPGSMCM01149 assurentCREATE INDEX 666563261904/10 19:59:28 149
150 sleeping atriumpullPSCDALDQASQL1 . assurentAWAITING COMMAND0004/10 21:39:22Enterprise Integration Engine150
151 sleeping assurentPSCDALPGSMCM01149 assurentTRUNCATE TABLE 128293536804/10 21:59:29 151
-------------------------------------
Thanks
Venkat
-
Generally, it's probably safer to create the index & temp table together and then add data to it. Clearly it's affecting the locking, but it will also cause recompiles.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
1) Confused. In your original post you stated there was no line in the sproc that did CREATE INDEX, yet in the last post you made you gave a temp table creation (via SELECT INTO) then had a CREATE INDEX statement on that table. Which is it?
2) Can you post the entire sproc code?
3) Realize that usage of many temp tables in a sproc can cause a several unintended consequences:
a) recompiles (MANY more than you might suspect, since IIRC for every 6 rows you insert/modify, the code referencing the temp table will be recompiled). This gets expensive.
b) physical overhead in tempdb, which could affect performance
c) Locks (and potential blocking) in tempdb due to system object changes (such as sys.objects and sys.indexes. I have seen poor temporary object usage bring production systems to it's knees before due to this problem.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
a) recompiles (MANY more than you might suspect, since IIRC for every 6 rows you insert/modify, the code referencing the temp table will be recompiled). This gets expensive.
Plus every time you switch from ddl to dml or vis-versa you'll get a recompile. I had a stored proc that recompiled on average 6 times for each execution, just due to the creation of the temp tables. The sub procs that inserted into those temp tables also each recompiled 2-3 times. It was not pretty.
One thing I just noticed is that the deadlock graph indicated the create index is line 1. That's very odd for a stored proc. In my experience you only get that when using dynamic sql.
Got any dynamic sql in that proc?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
A client of mine had the same problem. We only managed to solve it by altering Table Variables to Temp Tables.
Do you use Table Variables in your SPROC?
Regards,
Hans
-
If all of this is happening in an explicitly declared transaction, that would be the source of your problems.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for your replies!!
I am looking at all the replies you gave above.
In the mean while
Can you provide me some links to know the features, architecture and working of tempDB?
When my deadlock occurred the tempDB Log had grown to 3.8 GB.
What will be the consequences of a huge tempDB?
Can you please provide your inputs on the above questions.
Thanks in advance for your replies!!
-
1) A web search and also a perusal/search of BOL (book online for sql server) will provide a wealth of information about the tempdb. Note that it's usage has changed fairly drastically in sql 2005 and it is now a much more critical part of the infrastructure than in previous editions.
2) If you started with default settings for tempdb and it was 3.8GB, that is REALLY bad - in large part due to os-level file fragmentation. If your system really needs a tempdb that big (apparently it does) you really need to make it that size from the get go. You should size the tempdb log appropriately too. Not doing so will lead to severe disk file fragmentation and potentially very slow performance. Make sure you set file/log growth increments to something fairly large (200-500MB perhaps?) if you have a 4GB log file need. Again, avoid fragmentation if it has to expand during operations. Limit the max size such that you don't fill up a drive (this could litterally stop sql server in some cases). Also consider placing tempdb on multiple files, one per cpu, if you have a box with lots of cpus and a busy system with good I/O capabilities for maximal performance. See BOL for this too.
3) Other than the above mentioned fragmentation there is no real 'consequence' of a huge tempdb. It will be as big as it needs to be. Well, if you don't enable autogrowth your system will stop responding when it fills up tempdb.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply