SQL Server: generate blocking with an open transaction
Open a transaction that keeps its locks for 50 minutes, to test blocking and its monitoring.
This script opens a transaction, inserts a row, then waits 50 minutes before committing. Until the transaction ends, the locks taken by the insert stay in place and block the sessions that access the same data. It is used to test blocking detection.
Query
BEGIN TRANSACTION
INSERT INTO [dbo].[Table_1]([largedata])
VALUES ('test row')
WAITFOR DELAY '00:50:00'
COMMIT TRANSACTION
Notes
- Use only on a test database: the transaction blocks other sessions for the whole wait.
- To release the locks sooner, stop the session with
KILL <session_id>: the open transaction is then rolled back.