Obtain Update Table Lock at start of Stored Procedure in SQL Server

Question

I'm writing a SQL Server stored procedure in which I want to lock a table for update before executing the body of the stored procedure. I don't want to prevent other processes from reading the table, but I do want to prevent other processes updating the table.

Here is my first attempt:

CREATE PROCEDURE someProcedure
BEGIN
   SET TRANSACTION ISOLATION LEVEL READ COMITTED
   BEGIN TRANSANCTION
     SELECT COUNT(*) FROM TheTable WITH (UPDLOCK, TABLOCK)

     -- Pause procedure so that we can view the locks with sp_lock
     WAITFOR DELAY '00:15'

     -- Do stuff
   COMMIT
END

When I execute the stored procedure, and invoke sp_lock, I see that the table is indeed locked. However, it's locked with an Exclusive lock instead of an update lock:

spid | dbid | ObjId     | IndId | Type | Resource | Mode | Status
------------------------------------------------------------------
63   | 10   | 233208031 | 0     | TAB  |          | X    | GRANT

How can I get an update (U) lock instead?

Answer 1

TABLOCK

Specifies that a lock is taken on the table and held until the end-of-statement. If data is being read, a shared lock is taken. If data is being modified, an exclusive lock is taken. If HOLDLOCK is also specified, a shared table lock is held until the end of the transaction.

When used with the OPENROWSET bulk rowset provider to import data into a table without indexes, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking.

Taken from: http://msdn.microsoft.com/en-us/library/ms187373(SQL.90).aspx

Answer 2

You said:

I don't want to prevent other processes from reading the table, but I do want to prevent other processes updating the table.

You simply need a shared read lock for the duration of the TXN. This means no other process can get a "write" lock, in conjunction with a TABLOCK. And you don't need COUNT either.

...
   BEGIN TRANSANCTION
     SELECT TOP 1 KeyCol FROM TheTable WITH (TABLOCK, HOLDLOCK)
...

Why do you think you want an UPDATE LOCK?

HOLDLOCK or SERIALIZABLE

Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. ... Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not.

Edit, after comment:

• "exclusive lock" means "only one process using the data".
• "SERIALIZABLE" basically means hold the locks (shared, exclusive, whatever) for a lot longer.

You can't specify "exclusive lock" and allow other processes to read. The concepts are mutually exclusive. You want to prevent writes to the entire table, which a persisted shared/read lock will do. This is where SERIALIZABLE comes in.

From "Lock Modes"

Shared Locks

...No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

So: a shared lock disallows writes and can be made to persist by making it SERIALIZABLE