Blocking Definiton - Attempting to acquire a lock that conflicts with a lock held by another connection.
Deadlock Definition - A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed (Source)
Demo Blocking
Create simple tables for test purpose
--STEP 1
CREATE TABLE TESTTable
(Id Int Identity(1,1),
Name VARCHAR(50))
--STEP 2
--Populate Data
DECLARE @i int
SET @i = 0
WHILE 1 = 1
BEGIN
INSERT INTO TESTTable(Name)
SELECT CONVERT(VARCHAR(20),@i)+'Test'
SET @i = @i+1
IF @i > 10000
BREAK;
END
--STEP 3
BEGIN TRAN
Update TESTTable
SET Name = '1Test1'
WHERE Name = '1Test'
Open another new session (Not a gr8 query a little example to block it, Transaction is open in both the cases)
STEP 1
BEGIN TRAN
Update TESTTable
SET Name = '1Test1'
WHERE Name = '1Test'
Open another session to detect blocking
STEP 1
Run the below query to check for blocking. This would show current blocking. More details at statement level run query provided in next step.
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
STEP 2
One more blocked query script. This would show blocking and waiting statement and details.
Source - http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/sql05vb044.mspx?mfr=true
This query would show
STEP 3
Setup blocked process report as provided in link
Monitoring for Blocked Processes On SQL 2005 - Extended Version
This is very clearly explained in provided link.
When To Use Blocked Processes Reports
Zeroing in on blocking on seemingly unrelated tables
CodePlex - SQL Server Blocked Process Monitor
Troubleshooting Blocking 101 on SQL Server
Happy learning...
January 22, 2010
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment