sql-server - SQL Server 自定义计数器存储过程创建欺骗

Question

我创建了一个存储过程来对我的 API 实现速率限制，每秒调用大约 5-10k 次，每天我都会注意到计数器表中存在重复的情况。

它查找传入的 API key ，然后使用“UPSERT”检查包含 ID 和日期组合的计数器表，如果找到结果，则执行 UPDATE [count]+1，如果没有，则插入一个新行。

计数器表中没有主键。

这是存储过程:

USE [omdb]
GO
/****** Object:  StoredProcedure [dbo].[CheckKey]    Script Date: 6/17/2017 10:39:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CheckKey] (
@apikey AS VARCHAR(10)
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @userID as int
DECLARE @limit as int
DECLARE @curCount as int
DECLARE @curDate as Date = GETDATE()

SELECT @userID = id, @limit = limit FROM [users] WHERE apiKey = @apikey

IF @userID IS NULL
    BEGIN
        --Key not found
        SELECT 'False' as [Response], 'Invalid API key!' as [Reason]
    END
ELSE
    BEGIN
        --Key found
        BEGIN TRANSACTION Upsert
        MERGE [counter] AS t
        USING (SELECT @userID AS ID) AS s
        ON t.[ID] = s.[ID] AND t.[date] = @curDate
        WHEN MATCHED THEN UPDATE SET t.[count] = t.[count]+1
        WHEN NOT MATCHED THEN INSERT ([ID], [date], [count]) VALUES (@userID, @curDate, 1);
        COMMIT TRANSACTION Upsert

        SELECT @curCount = [count] FROM [counter] WHERE ID = @userID AND [date] = @curDate

        IF @limit IS NOT NULL AND @curCount > @limit
            BEGIN
                SELECT 'False' as [Response], 'Request limit reached!' as [Reason]
            END
        ELSE
            BEGIN
                SELECT 'True' as [Response], NULL as [Reason]
            END
    END
END

我还认为引入此 SP 后会发生一些锁定。

这些骗子并没有破坏任何东西，但我很好奇我的代码是否有根本性的错误，或者我是否应该在表中设置一个约束来防止这种情况发生。谢谢

2017 年 6 月 23 日更新:我删除了 MERGE 语句并尝试使用 @@ROWCOUNT，但它也导致了欺骗

BEGIN TRANSACTION Upsert
UPDATE [counter] SET [count] = [count]+1 WHERE [ID] = @userID AND [date] = @curDate
IF @@ROWCOUNT = 0 AND @@ERROR = 0
INSERT INTO [counter] ([ID], [date], [count]) VALUES (@userID, @curDate, 1)
COMMIT TRANSACTION Upsert

Answer 1

一个HOLDLOCK更新语句上的提示将避免竞争条件。为了防止死锁，我建议在 ID 上使用聚集复合主键(或唯一索引)。和date .

下面的示例合并了这些更改并使用 SET <variable> = <column> = <expression> SET 的形式子句以避免后续 SELECT 的需要最终计数器值，从而提高性能。

ALTER PROCEDURE [dbo].[CheckKey]
    @apikey AS VARCHAR(10)
AS

SET NOCOUNT ON;
--SET XACT_ABORT ON is a best practice for procs with explcit transactions
SET XACT_ABORT ON; 

DECLARE
      @userID as int
    , @limit as int
    , @curCount as int
    , @curDate as Date = GETDATE();

BEGIN TRY;

    SELECT
          @userID = id
        , @limit = limit 
    FROM [users] 
    WHERE apiKey = @apikey;

    IF @userID IS NULL
    BEGIN
        --Key not found
        SELECT 'False' as [Response], 'Invalid API key!' as [Reason];
    END
    ELSE
    BEGIN
        --Key found
        BEGIN TRANSACTION Upsert;

        UPDATE [counter] WITH(HOLDLOCK) 
        SET @curCount = [count] = [count] + 1 
        WHERE
            [ID] = @userID 
            AND [date] = @curDate;

            IF @@ROWCOUNT = 0
            BEGIN    
                INSERT INTO [counter] ([ID], [date], [count]) 
                    VALUES (@userID, @curDate, 1);
            END;

        IF @limit IS NOT NULL AND @curCount > @limit
        BEGIN
            SELECT 'False' as [Response], 'Request limit reached!' as [Reason]
        END
        ELSE
        BEGIN
            SELECT 'True' as [Response], NULL as [Reason]
        END;

        COMMIT TRANSACTION Upsert;

    END;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO