SQLServer : Explicitly Locking Table

by bitznbitez

In some database environments there is a handy command LOCK TABLE tablename IN EXCLUSIVE MODE.   Apparently in SQL Server there is no such command.  To accomplish a similar thing in SQLServer you can resort to the following :

declare @bogusVar int
begin tran
select @bogusVar=1 from tablename with (tablockx)

Just don’t forget to either rollback or commit this when done so you don’t hold this table lock for too long.  Use either

rollback tran

or

commit tran

I have found this to be handy whenever I am trying to force a lock wait situation for testing purposes.

Advertisements