我创建了一个存储过程来对我的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
解决方法
更新语句上的HOLDLOCK提示将避免竞争条件.为了防止死锁,我建议在ID和日期上使用聚簇复合主键(或唯一索引).
以下示例包含这些更改并使用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
