sql-server – 创建dupes的SQL Server自定义计数器存储过程

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

它查找传入的API密钥,然后使用“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之后会发生一些锁定.

欺骗并没有破坏任何东西,但我很好奇我的代码是否存在根本错误,或者我是否应该在表格中设置约束以防止这种情况发生.谢谢

更新于6/23/17：我删除了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],1)
COMMIT TRANSACTION Upsert

解决方法

以下示例包含这些更改并使用SET< variable> =< column> =<表达式> 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],[count]) 
                    VALUES (@userID,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

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!