Learning on Blocking

One more learning added to our list while participating in a discussion.
Scenario -

• Read Committed Snapshot Isolation Enabled Database
• Transaction1 - Begin a Transaction for - Truncate Table, Do not Commit it
• Transaction2 - Begin a Transaction for - Select * from Table
• Transaction2 is blocked by Transaction1. We will try the example and see it why.

 Getting Ready with Required Steps and Data for it

• Enable ReadCommitted Snapshot Isolation
• Create a Table
• Insert Records

STEP 1
ALTER DATABASE TestDatabase
SET READ_COMMITTED_SNAPSHOT ON

STEP 2

Verify it is enabled by below query
select is_read_committed_snapshot_on, snapshot_isolation_state, snapshot_isolation_state_desc,
sys.databases.[name] from sys.databases

STEP 3
Create TestTable and Populate Data

CREATE TABLE TestTable

(
Number INT IDENTITY(1,1),
Name VARCHAR(20) NOT NULL
)
STEP 4
Populate Data in the tables

DECLARE @I INT

SET @I = 100
WHILE 1 = 1
BEGIN
    SET @I = @I + 1
    INSERT INTO TestTable(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    IF @I=10000
    BREAK;
END

STEP 5 (Transaction 1)

Now we are set to experiment now. Open Transaction to trancate table TestTable
BEGIN TRAN
TRUNCATE TABLE TestTable

STEP 6 (Transaction 2)

Open Another Transaction, Transaction2
use TestDatabase
BEGIN TRAN
SELECT * FROM TestTable
 
STEP 7 (Here is Blocking, It has finally Arrived as per the need of this blog post). Detect Blocking with below Query
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
Run Query as provided in link

STEP 8 - Find the locks associated with the transaction
Select * from sys.dm_tran_locks Where request_session_id = 52
Sch-M LOCK GRANT

Select * from sys.dm_tran_locks Where request_session_id = 53

Sch-S LOCK WAIT

For Session 52 It Already Holds Sch-M Lock.

STEP 9 - Reason Why and What is Learning Now for us

A Very Important Idea in MSDN Link

For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock.

Now that we learnt SCH-M Lock is the reason for blocking. Now How do i Fix it.
This is the learning of this Post. Replacing Delete with Truncate Solved the issue.

STEP 10 - Next Step
Transaction1
BEGIN TRAN
DELETE FROM TestTable

Transaction2
BEGIN TRAN
SELECT * FROM TestTable
Since, ReadCommitted Transaction is enabled you will see last committed data.

STEP 11 - Further Analysis

DBCC INPUTBUFFER(52)
--DELETE FROM TestTable
Select * from sys.dm_tran_locks Where request_session_id = 52
It has got X Lock

SELECT object_name(resource_associated_entity_id)

SELECT OBJECT_NAME(2105058535)
--TestTable
DBCC INPUTBUFFER(53)
--SELECT * FROM TestTable
Select * from sys.dm_tran_locks Where request_session_id = 53
It has got S Lock

STEP 12 - Below Query in Link is also good. Its worth to take a look at Lock Compatability Matrix
The schema stability (Sch-S) lock is compatible with all lock modes except the schema modification (Sch-M) lock mode. The Sch-M lock is incompatible with all lock modes. Lock Compatability


More Reads
Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?


Happy Reading.......Happy Learning.....Thanks Roji and Vaibhav for the Discussion.

Troubleshooting blocking

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...