Regarding a deadlock scenario
-
I get deadlock in the same stored procedure but at different locations each time, In the following query I got the deadlock last time:
select distinct site_id, site_name, lower(su_id) as su_id,
into #temp_su
from site_unit_v2 g,
#temp_ip t
where
g.site_id = t.site_id and
@enddate > timestamp and to_time > @date and
(array_id = @array_id) and
assigned = 1
1)This stored procedure is at nested level 2 and it is called from a while loop.
2)The other thing is at all the times they are trying to get exclusive access to a particular page in tempDB and then it gets hanged there and prints deadlock.
The error log is shown below:
Deadlock encountered .... Printing deadlock information
2007-05-02 18:21:18.59 spid4
2007-05-02 18:21:18.59 spid4 Wait-for graph
2007-05-02 18:21:18.59 spid4
2007-05-02 18:21:18.59 spid4 Node:1
2007-05-02 18:21:18.60 spid4 PAG: 2:1:1224 CleanCnt:2 Mode: X Flags: 0x0
2007-05-02 18:21:18.60 spid4 Grant List 1::
2007-05-02 18:21:18.60 spid4 Owner:0x34ca2f80 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:87 ECID:0
2007-05-02 18:21:18.60 spid4 SPID: 87 ECID: 0 Statement Type: SELECT INTO Line #: 528
2007-05-02 18:21:18.60 spid4 Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
2007-05-02 18:21:18.60 spid4 Requested By:
2007-05-02 18:21:18.60 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost0/B80)2007-05-02 18:21:18.60 spid4 Victim Resource Owner:
2007-05-02 18:21:18.60 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost0/B80)2007-05-02 18:21:18.60 spid4
2007-05-02 18:21:18.60 spid4 End deadlock search 5658 ... a deadlock was found.
2007-05-02 18:21:18.60 spid4 ----------------------------------
2007-05-02 18:21:18.60 spid4 ----------------------------------
2007-05-02 18:21:18.60 spid4 Starting deadlock search 5659
2007-05-02 18:21:18.60 spid4 Target Resource Owner:
2007-05-02 18:21:18.60 spid4 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:88 ECID:0 Ec
0x1BFED520) Value:0x35456da02007-05-02 18:21:18.60 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:88 ECID:0 Ec
0x1BFED520) Value:0x35456da02007-05-02 18:21:18.60 spid4
2007-05-02 18:21:18.60 spid4 Previous victim encountered ... aborting search
2007-05-02 18:21:18.60 spid4
2007-05-02 18:21:18.60 spid4 End deadlock search 5659 ... a deadlock was not found.
2007-05-02 18:21:18.60 spid4 ----------------------------------
2007-05-02 18:21:18.73 spid4 ----------------------------------
2007-05-02 18:21:18.73 spid4 Starting deadlock search 5660
2007-05-02 18:21:18.73 spid4 Target Resource Owner:
2007-05-02 18:21:18.73 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c02007-05-02 18:21:18.73 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c02007-05-02 18:21:18.73 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c02007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4 Deadlock cycle was encountered .... verifying cycle
2007-05-02 18:21:18.73 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost28/26F1D7F3)2007-05-02 18:21:18.73 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost28/1)2007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4
Deadlock encountered .... Printing deadlock information
2007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4 Wait-for graph
2007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4 Node:1
2007-05-02 18:21:18.73 spid4 PAG: 2:1:1224 CleanCnt:2 Mode: X Flags: 0x0
2007-05-02 18:21:18.73 spid4 Grant List 1::
2007-05-02 18:21:18.73 spid4 Owner:0x34ca2f80 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:87 ECID:0
2007-05-02 18:21:18.73 spid4 SPID: 87 ECID: 0 Statement Type: SELECT INTO Line #: 528
2007-05-02 18:21:18.73 spid4 Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
2007-05-02 18:21:18.73 spid4 Requested By:
2007-05-02 18:21:18.73 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost28/218FB18)2007-05-02 18:21:18.73 spid4 Unresolvable deadlock encountered. Server may need to be restarted.
.... Printing deadlock information
2007-05-02 18:21:18.73 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost28/26F1D7F3)2007-05-02 18:21:18.73 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost28/1)2007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4
Deadlock encountered .... Printing deadlock information
2007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4 Wait-for graph
2007-05-02 18:21:18.73 spid4
2007-05-02 18:21:18.73 spid4 Node:1
2007-05-02 18:21:18.73 spid4 PAG: 2:1:1224 CleanCnt:2 Mode: X Flags: 0x0
2007-05-02 18:21:18.73 spid4 Grant List 1::
2007-05-02 18:21:18.73 spid4 Owner:0x34ca2f80 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:87 ECID:0
2007-05-02 18:21:18.73 spid4 SPID: 87 ECID: 0 Statement Type: SELECT INTO Line #: 528
2007-05-02 18:21:18.73 spid4 Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
2007-05-02 18:21:18.73 spid4 Requested By:
2007-05-02 18:21:18.73 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:87 ECID:0 Ec
0x1C0DD558) Value:0x36ae50c0 Cost28/218FAE0)2007-05-02 18:21:19.24 spid4 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 4, PSS = 0x198ea0c0, EC = 0x198ea3f0
*Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0002.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 05/02/07 18:21:19 spid 4
*
* Unresolved deadlock
*
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 009B9FBC Module(sqlservr+005B9FBC) (GetOSErrString+00004F68)
* 009BA8E5 Module(sqlservr+005BA8E5) (GetOSErrString+00005891)
* 00988550 Module(sqlservr+00588550) (CDStream::operator=+0003E4F3)
* 006D7383 Module(sqlservr+002D7383) (SQLExit+000E3C94)
* 006D7D51 Module(sqlservr+002D7D51) (SQLExit+000E4662)
* 005A1F03 Module(sqlservr+001A1F03)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E66063 Module(kernel32+00026063) (GetModuleFileNameA+000000EB)
Let me know if more information is needed.
-
Excessive temporary object creation can cause blocking in tempdb system tables. I have seen this bring production systems to a halt. The only solution is to rework your code to reduce temp object usage. Very occassionally putting tempdb on a faster I/O subsystem or multiple files can help some.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
When I got the deadlock scenario I took the snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.
rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?(Note: I tried MSDN site they have just a single line in it it doesn;t give much information other than that.)
2) what is the relationship between req_transactionId column and spid column?.Currently I am finding that one SPID has many transationID's from the entry shown above in syslockinfo table.
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between req_transactionId column and spid column in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) should there be contention for locks between transactions within a single spid, currently there is a contention for locks between transactions within a single spid.
it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while
the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607
If some body could please help me it would be really helpful for me.
Thanks in advance for helping me!!
-
Without seeing any code, table scripts, sample data and table row counts we really can't EFFECTIVELY help you.
Given that disclaimer, if I were you I would rework the entire sproc to eliminate as many temp tables as possible. It has been my experience that developers who have made the initial step away from using cursors and row-based processing tend to use temp tables way too much.
Also, using table variables may not help since they will get dropped down to tempdb if they get too big anyway. They carry other limitations as well.
Try declaring ALL temp tables at the very top of the sproc (i.e. get rid of select intos). There is a fine line about the indexing issue. Putting it on the table first then populating leads to a VERY fragmented index, unless it is a PK index on an identity. Putting it on after data population leads to a tight index but will definitely cause a recompile. I tend to eat the recompile since that is usually less expensive in the long run than lots more I/Os that would occur from all the index page splits during data insertion as well as extra reads due to that fragmentation when accessing the data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Everything the Guru said and, check the use of transactions in your process. Something seems to be doing some sort of lock escalation within the transaction because blocking, by itself, doesn't cause deadlocks.
"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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply