Wednesday, October 17, 2012

Microsoft SQL Server Lock table for some duration

-- Declare a I_LOCK_YOU procedure
BEGIN TRAN I_LOCK_YOU;

-- simply select any table we intend to lock.
SELECT TOP (1000) * FROM VOUCHER.TICKET TKT (HOLDLOCK) JOIN VOUCHER.TRANS TRN (HOLDLOCK) ON TRN.TKT_ID = TKT.TKT_ID
WHERE TRN.STAT_ID = 3;

-- You can sleep as long as you like.
WAITFOR DELAY '00:00:04';

COMMIT TRAN I_LOCK_YOU;