How to lock a specific row that doesn't exist yet in SQL Server
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 (ie 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.
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 (ie 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
链接地址: http://www.djcxy.com/p/5608.html