LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon