Problem Statement:
Today, I got a very peculiar request from one of my colleague that he wants to put a lock on a table so that no other requestscan be served. The request was looking weird to me initially as most of us want to avoid locking/blocking scenarios in a system, but the request was looking for creating one. But Why? After asking many questions, I got his intention that he is looking for replicating a locking scenario in his local environment that ends up with a Timeout.
Solution:
The solution provided is simple but put a Schema modification lock(Sch-M) on his table. Schema modification lock will be acquired when a DDL statement is executed on the table. As Schema modification is not compatible with any other locks, it will prevent access to the locked object data. An example of the Sch-M lock is lock during index rebuild (Please note when used with ONLINE option, index rebuild will acquire Sch-M lock shortly at the end of the process).
Session 1:
--Create Table
create Table TestLock(Col1 int)
Insert into TestLock values ('100')
--Raise a Schema modification lock
begin Tran T1
ALTER TABLE TESTLOCK ALTER COLUMN COL1 VARCHAR(5000)
-----------------------------------------------------------------------
Session 2:
Select * From TestLOCK(NOLOCK)--NOLOCK has no impact on SCH-M locked object.
Thoughts:
Finally, I understood he is trying to put some code to handle the locking situation, but not the solution for the timeout. As he mentioned the issue happens ONLY when maintenance window is running, I believe this is something related to any maintenance operations like (rebuild index,update stats etc.). He can , later, better of find the actual issue and fix it like move the functionality out of the maintenance window!!!
