Blocking vs Dead lock

Question

Blocking vs Dead lock

passivebyz

SSCommitted

Points: 1908
More actions
May 25, 2010 at 8:57 pm

#141069

Hi,
Could you please explain me the difference between Blocking and Dead lock?
Are they same?
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

Leo.Miller SSChampion Points: 13048 More actions · Answer 1

In blocking, one process is holding s resource that another process requires. SQL knows that once the blocking process finishes the resource will be available and so the blocked process will wait (until it times out), but it won't be killed.

In a deadlock, there are 2 processes. P1 & P2 trying to get to 2 resources R1 & R2.

P1 gets a lock on R1

and

P2 gets a lock on R2

THEN

P1 tries to get a lock on R2 but can't because it is locked by P2

and

P2 tries to get a lock on R1 but can't because it is locked by P1

At this point neither process can finish because they are both waiting on locked resources. i.e. they are deadlocked. One of them must be killed to allow either of them to finish.

SQL decides which is the "least expensive process to kill" and it becomes the Deadlock victim.

Leo

There are 10 types of people in the world.

Those who understand binary and and those that don't

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

passivebyz SSCommitted Points: 1908 More actions · Answer 2

Thanks so much,

Is there any script that looks for Blocking or dead lock every 5 mins?

and if that script detects any blocking or dead lock, it should send an email including the sessions that involved in blocking and automatically kill if the blocking is >10 mins

give me some idea how can I achieve this

thanks

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 3

third party tool like sql heart beat will help..

u can view blocking by sp_who2

passivebyz SSCommitted Points: 1908 More actions · Answer 4

third party tool like sql heart beat will help..
u can view blocking by sp_who2

Thanks,

Yes, We can know the blocking by sp_who2 when blocking occurs. But not after the blocking. I believe there will be some kind of script to capture blocking and notify the dba to take proper action. I think all dba's cannot use 3 rd party tools right and they might use some kind of monitoring scripts & capturing blocking & take proper action.

And I want to get start to write a script for that? any help will be appreciated.

thanks

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 5

Hope this will help

sp_configure 'show advanced options',1 ;

GO

RECONFIGURE;

GO

sp_configure 'blocked process threshold',5 ;

GO

RECONFIGURE;

GO

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

--Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 50

exec @rc = sp_trace_create @TraceID output, 2, N'c:\BlockedProcessTrace', @maxfilesize, NULL

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 137, 15, @on

exec sp_trace_setevent @TraceID, 137, 1, @on

exec sp_trace_setevent @TraceID, 137, 13, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references

select TraceID=@TraceID

goto finish

error:

select ErrorCode=@rc

finish:

go

passivebyz SSCommitted Points: 1908 More actions · Answer 6

Thanks,

I'm looking for a script which will include the logic to send an email when blocking occurs that includes the information such as what sessions were blocked and what session is causing the blocking and wait for 10 mins and then kill the blocking session.

If anybody using this kind of script, could you please share it, if possible.

Thanks

homebrew01 SSC Guru Points: 55539 More actions · Answer 7

passivebyz (5/26/2010)
Thanks,
I'm looking for a script which will include the logic to send an email when blocking occurs that includes the information such as what sessions were blocked and what session is causing the blocking and wait for 10 mins and then kill the blocking session.
If anybody using this kind of script, could you please share it, if possible.
Thanks

I don't think automatically killing a process is a good idea. Better to find out why the blocking is happening, and solve the problem.

Leo.Miller SSChampion Points: 13048 More actions · Answer 8

You can do a query against master..sysprocesses and look at the blocked value. This will also tell you the SPID doing the blocking.

I have a script but I'll need to dig it up which may take a day as I'm really busy. It returns blocking chains, you can then build it into a job and add the e-mail portion.

You'll need to set up db_mail

Leo

There are 10 types of people in the world.

Those who understand binary and and those that don't

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

denis.hosdil SSC Veteran Points: 253 More actions · Answer 9

This will ensure the Deadlocks get written to the SQL Server Log file:

DBCC TRACEON(1222,-1)

And you should consider adding these to the Start-up Parameters.

ianT SSCrazy Points: 2956 More actions · Answer 10

Do you have blocking issues?

If so, how do you know that you do? what symptons/problems do you have?

Monitoring blocking may be expensive in resources etc; a performance issue could be linked to a lot of factors - blocking is only one of them.

denis.hosdil SSC Veteran Points: 253 More actions · Answer 11

Our department eliminated blocking by using 'nolock' table hint. It has worked great, or so we think.

I believe the best option is Snaphot Isolation Level:

http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

Then readers don't block writers and writers don't block readers while avoiding dirty reads. This is new to 2005 and has been the main reason that SQL Server lagged behind Oracle. I have to admit, I haven't (yet) used it myself.

Ray Laubert SSCrazy Points: 2910 More actions · Answer 12

Blocking is a natural process in databases. As more and more request for information (queries) and more DML activities are generated you will see more blocking locks. Most will be cleared so quickly that you would not be able to do anything about them anyway. Once in a while you might find a long running query that is a problem.

If you are trying to performance tune, then I would look at capturing the activity with Profiler and then run index tuning against that. I think you will find that with the proper indexes you will reduce the blocking aspects as well as improve the query performance.

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g