Locking stored procedure
I have a stored procedure with a select and an update. I would like to prevent multiple users, from executing it, at the same time, so I don't update, based on an incorrect select. How do I lock it? I've read various solutions (Transaction isolation, xlock), but I haven't been able to figure what I really wan开发者_如何学Got, and how to do it.
The easiest way is to forget about data locks but look at sp_getapplock to control access through the code
BEGIN TRY
EXEC sp_getapplock ...
SELECT ...
UPDATE ...
EXEC sp_releaseapplock
END TRY
...
Saying that, with thing like the OUTPUT clause and judicious use of ROWLOCK, UPDLOCK there is a good chance the UPDATE and SELECT can be one statement
Using the XLOCK table hint in the SELECT
query:
CREATE TABLE [X]([x] INT NOT NULL)
GO
INSERT [X]([x]) SELECT 0
GO
CREATE PROCEDURE [ATOMIC]
AS
BEGIN
BEGIN TRAN
DECLARE @x INT = (
SELECT [x]
FROM [X] (XLOCK)
) + 1
UPDATE [X] SET [x] = @x
COMMIT TRAN
END
GO
You can then test this by running
EXEC [ATOMIC]
GO 10000
simultaneously from different sessions. You can test using
SELECT [x] FROM [X]
The value should be exactly 10 000 times the number of sessions you ran. If the number is less than expected you don't have atomic read + write, or some SPIDs may have been killed due to dead locking.
精彩评论