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.