Obtain Update Table Lock at start of Stored Procedure in SQL Server
Posted
by Jim Hurne
on Stack Overflow
See other posts from Stack Overflow
or by Jim Hurne
Published on 2010-06-04T10:57:17Z
Indexed on
2010/06/06
13:42 UTC
Read the original article
Hit count: 274
sql-server-2005
|locking
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?
© Stack Overflow or respective owner