Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
BigSam
Starting Member
30 Posts |
Posted - 2006-06-05 : 09:59:32
|
I was investigating a deadlock yesterday that caused me to restart SQL before I could complete researching the cause. However, the jist of my post relates to what I think is a strange deadlock situation. SPID 54 was blocking SPID 54 & SPID 54 was blocked by SPID 54. SPID 54 was an IIS connection. Can anyone give me an example of a transaction that can block itself?ThanksBig Sam |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-05 : 12:36:22
|
You have sp4?Are you sure that it deadlocked? It is common to get a spid marked as blocking itself due to parallelism - just means that the main thread is waiting for another thread to copmplete processing.For it to deadlock sounds like a bug though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-06-05 : 13:02:28
|
>>Can anyone give me an example of a transaction that can block itself?I did see a very funky thing once where a developer emailed me the output of sp_who2 active showing a spid blocking itself. Turned out he was...let me get this straight...within a transaction, inserting to a table, calling xp_cmdshell which was in-turn was making an osql call that selected from the same table. I don't remember exactly what he was doing or why, and I'm sure it doesn't pertain to your situation. I just wanted to share Actually now that I think about it, I remember trying to duplicate that but I couldn't get all the processes to work under the same spid, so maybe that self-blocking thing was due to parallelism as well...hmmm...oh well, nevermindBe One with the OptimizerTG |
|
|
|
|
|