Blocking / Locking - How to...

What is blocking ?
"When two or more activities permanently block each other by each activity having a lock on a resource which the other activity are trying to lock"

Creating table and Inserting sample record(s)
CREATE TABLE Tb_Sample1
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(10),
Column2 DATETIME
)
GO

INSERT Tb_Sample1(Column1,Column2) VALUES('Sample1',CURRENT_TIMESTAMP)
GO 10

CREATE TABLE Tb_Sample2
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(10),
Column2 DATETIME
)
GO

INSERT Tb_Sample2(Column1,Column2) VALUES('Sample2',CURRENT_TIMESTAMP)
GO 10


Execute the script in a session: 57
BEGIN TRAN
UPDATE Tb_Sample1 SET COLUMN1 ='Sample One' WHERE ID = 2


Execute the script in a session: 56
BEGIN TRAN
SELECT * FROM Tb_Sample1 WHERE ID = 2


Session 57 executed successfully, But the session 56 is waiting...waiting..waiting...

How to identify Blocking Session/ Locking Session ?
IF OBJECT_ID('USP_BlockingSession','P') IS NOT NULL
DROP PROC USP_BlockingSession
GO
CREATE PROC USP_BlockingSession
AS
BEGIN
SET NOCOUNT ON

SELECT DB_NAME(tl.resource_database_id) AS [Database Name],
tl.request_session_id [Blocked Session],
bld.[text] [Blocked Query],
wt.blocking_session_id [Blocking Session],
blk.[text] [Blocking Query],
tl.resource_type [Resource Type],
CASE tl.request_mode WHEN 'S' THEN 'Shared(S)'
WHEN 'U' THEN 'Update(U)'
WHEN 'X' THEN 'Exclusive(X)'
WHEN 'IS' THEN 'Intent Shared(IS)'
WHEN 'IX' THEN 'Intent Exclusive(IX)'
WHEN 'SIX' THEN 'Shared with Intent Exclusive(SIX)'
WHEN 'Sch-M' THEN 'Schema Modification(Sch-M)'
WHEN 'Sch-S' THEN 'Schema Stability(Sch-S)'
WHEN 'BU' THEN 'Bulk Update(BU)'
WHEN 'IU' THEN 'Intent Update(IU)'
WHEN 'SIU' THEN 'Shared Intent Update(SIU)'
WHEN 'UIE' THEN 'Update Intent Exclusive(UIE)'
END [Resource Mode],
(wt.wait_duration_ms /1000) [Waiting Time(Sec)]
FROM sys.dm_tran_locks AS tl
JOIN sys.dm_os_waiting_tasks AS wt
ON (tl.lock_owner_address = wt.resource_address)
JOIN sys.dm_exec_connections AS c
ON (C.session_id = tl.request_session_id)
JOIN sys.dm_exec_connections AS c1
ON (C1.session_id = wt.blocking_session_id)
CROSS APPLY SYS.dm_exec_sql_text(c.most_recent_sql_handle) AS Bld
CROSS APPLY SYS.dm_exec_sql_text(c1.most_recent_sql_handle) AS Blk
END
GO


Execute the Stored Procedure:
EXEC USP_BlockingSession
GO


Result is:

The Session 56 blocked by the session 57

How to solve this ?
Go to the blocked Session 56 and apply the table hint (NOLOCK)

No comments:

Post a Comment