Process Blocking

Question

Process Blocking

mg_dba

SSCrazy

Points: 2679
More actions
April 3, 2014 at 4:22 am

#287193

Hi all,
Just a quick question out of curiosity really....
Occasionally I see the following scenario in Activity Monitor:
Process 1 is blocked by process 2
Process 2 is blocked by process 1
The thing I don't understand is that this does not always result in a deadlock - how can this be?! ...and how does SQL resolve this conflict?
Cheers,
Matt

Viewing 4 posts - 1 through 3 (of 3 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

I think this is the normal scenario of blocking and once either process id completes the update it release the lock. Might be the lock/block is temporary here.

As profiler deadlock graph envent in this link:

http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

It is not possible that request mode(LOCK) is 1 UPDATE & 1 EXCLUSIVE On both side. Hence it will not result into deadlock.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

mg_dba SSCrazy Points: 2679 More actions · Answer 2

I'm still a little confused....I've found the following information about the exact scenario I was referring to:

SPID Type Resource Command Blocking SPID

1 LCK_M_IX PAGE: 96:1:151910 UPDATE 3

2 LCK_M_S PAGE: 96:1:151910 SELECT 1

3 LCK_M_S SELECT 1

As you can see SPID 1 is blocking 3 and vice versa - is this enough information to explain exactly what's going on?

Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

matt.gyton (4/3/2014)
I'm still a little confused....I've found the following information about the exact scenario I was referring to:
SPID Type Resource Command Blocking SPID
1 LCK_M_IX PAGE: 96:1:151910 UPDATE 3
2 LCK_M_S PAGE: 96:1:151910 SELECT 1
3 LCK_M_S SELECT 1
As you can see SPID 1 is blocking 3 and vice versa - is this enough information to explain exactly what's going on?
Thanks

You need to find on what resource Spid 3 is waiting. If SPID 3 completes before, then it won't result in a deadlock.

--

SQLBuddy