Lock SQL table for a period of time

Question

We are trying to test our new software here which is based on SQL 2005. We want to see how it will respond when one of the tables which the software is using during insert/update gets locked.

Is there a way in SQL 2005 we can simulate this? so that we can see how we can handle the error on the front end?

Answer 1

In SQL Server Management Studio, run this TSQL script (set timeout to whatever suits you)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION

UPDATE table
SET ColumnName = ColumnName

WAITFOR DELAY '00:02:00'  -- 2 minutes   hh:mm:ss

-- Run your test while table is locked and delay is counting down

ROLLBACK TRANSACTION

Answer 2

Using another session,

1. Do a BEGIN TRANSACTION

2. Call UPDATE table set columnName = columnName

3. Run your test

4. Verify your results

5. COMMIT TRAN / ROLLBACK TRAN the transaction