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.