安全存储过程更新

Question

我有一个存储过程:

ALTER PROCEDURE [dbo].[myDbProcedure]
@ID int
AS
BEGIN 
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM [myDB] WHERE @ID = [ID])
UPDATE [myDB]
SET [count] = [count] - 1
, [paymentCount]  = [paymentCount]  + 1
WHERE @ID = [ID] 
END

现在我想要安全，如果100人同时更新表的任何信息不要错过

Answer 1

不知道我是否完全理解你的要求:

Begin Tran
    IF EXISTS (SELECT * FROM [myDB] WHERE @ID = [ID])
    UPDATE [myDB]
    SET [count] = [count] - 1
    , [paymentCount]  = [paymentCount]  + 1
    WHERE @ID = [ID] 
Commit

你也可以考虑删除if，因为只有当寄存器实际存在时才会进行更新。这样，您就只需要在事务上下文中运行的更新。考虑查看隔离级别(来自comment: set transaction isolation level serializable)。