4

I need to be able to lock a table in SQL Server while doing some work. The lock needs to block other sessions from reading the table. When the work is complete the table needs to be unlocked.

In MySQL I have done this by using: 

LOCK TABLES [table] WRITE

At this point other sessions will be blocked when trying read from the table, until the table is unlocked.

UNLOCK TABLES

Now the blocking will stop.

Is this possible in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sjallamander
  • 439
  • 2
  • 6
  • 20

1 Answers1

6
BEGIN TRANSACTION 

    SELECT TOP 1 1 FROM TableName WITH (TABLOCKX)
  /* do your stuff here */

COMMIT TRANSACTION

TABLOCKX will obtain an exclusive lock on the table and other users will not be able to read or update the data until you commit the transaction yourself.

Edit

Instead of doing a SELECT * like i suggested before, just do a SELECT TOP 1 1 it will get an exclusive lock on the table and will not waste any time returning actual data like a SELECT *would do.

M.Ali
  • 67,945
  • 13
  • 101
  • 127