1

I have an API rate limit table that I'm managing for one of our applications. Here's the definition of it.

CREATE TABLE [dbo].[RateLimit]
(
    [UserId] [int] NOT NULL,
    [EndPointId] [smallint] NOT NULL,
    [AllowedRequests] [smallint] NOT NULL,
    [ResetDateUtc] [datetime2](0) NOT NULL,

    CONSTRAINT [PK_RateLimit] 
        PRIMARY KEY CLUSTERED ([UserId] ASC, [EndPointId] ASC)
) ON [PRIMARY]

The process that performs CRUD operations on this table is multi-threaded, and therefore careful consideration needs to be placed on this table, which acts as the goto for rate limit checks (i.e. have we surpassed our rate limit, can we make another request, etc.)

I'm trying to introduce SQL locks to enable the application to reliably INSERT, UPDATE, and SELECT values without having the value changed from under it. Besides the normal complexity of this, the big pain point is that the RateLimit record for the UserId+EndPointId may not exist - and would need to be created.

enter image description here

I've been investigating SQL locks, but the thing is that there might be no row to lock if the rate limit record doesn't exist yet (i.e first run).

I've thought about creating a temp table used specifically for controlling the lock flow - but I'm unsure how this would work.

At the farthest extreme, I could wrap the SQL statement in a SERIALIZABLE transaction (or something to that degree), but locking the entire table would have drastic performance impacts - I only care about the userid+endpointid primary key, and making sure that the specific row is read + updated/inserted by one process at a time.

How can I handle this situation?

Version: SQL Server 2016

Notes: READ_COMMITTED_SNAPSHOT is enabled

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
contactmatt
  • 18,116
  • 40
  • 128
  • 186
  • *". . . I could wrap the SQL statement in a SERIALIZABLE transaction (or something to that degree), but locking the entire table would have drastic performance impacts. . ."* Serializable isolation level doesn't lock the table. – Mike Sherrill 'Cat Recall' Apr 05 '18 at 03:27
  • I'd use a `MERGE` statement with `HOLDLOCK` hint as described by Dan Guzman in his blog [“UPSERT” Race Condition With MERGE](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx). You can achieve the same result without `MERGE`, see his previous post [Conditional INSERT/UPDATE Race Condition](http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx) – Vladimir Baranov Apr 05 '18 at 05:29
  • Could you give more details about your requirements and environment? How many call/s? Have your measures to be absolutely accurate or you could accept small errors caused by race-conditions? performance requirement? etc. – Fabrizio Accatino Apr 05 '18 at 05:31
  • @FabrizioAccatino there's room for error - absolute guarantees are not needed, since the API we're calling will return an error and we can later re-try. However, we cannot abuse their API or we will be blacklisted. About 1,000-3,000 calls a minute to this table – contactmatt Apr 05 '18 at 14:22
  • @VladimirBaranov Looks promising - I'll take a look – contactmatt Apr 05 '18 at 14:23

0 Answers0