Obtain Update Table Lock at start of Stored Procedure in SQL Server
- by Jim Hurne
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?
