Background story: One of our developers need to test what would happen if he tries to access a table and the table is locked for some predefined time. These were tests for query timeout.
Task: Write a query which is going to hold an exclusive lock on a table for some predefined time.
Solution: Here is a query which is going to hold exclusive lock for some predefined time on a table table_name:
declare @var char(8)
select @var = "00:00:10" -- time in seconds(10 seconds)
begin
begin tran
lock table table_name in exclusive mode
waitfor delay @var
commit
end
go
