CODEWITHSUNDEEP
sql
andreas
andreas

Reputation: 135

Lock SQL table for a period of time

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?

Upvotes: 10

Views: 17073

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300559

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

Upvotes: 31

Jagmag
Jagmag

Reputation: 10356

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

Upvotes: 5

Related Questions