Avoid deadlocks on select / insert in case of concurrency

Question

I have the following table and am trying to enter / select visitor IPs in a high concurrency scenario.

CREATE TABLE [dbo].[stat](
 [stat_id] [int] IDENTITY(1,1) NOT NULL,  
 [stats_ip] [bigint] NOT NULL, 
 CONSTRAINT [PK_stat] PRIMARY KEY CLUSTERED 
(
 [stat_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_stat] ON [dbo].[stat]
(
 [stats_ip] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO

Basically, each concurrent task should perform the operations:

1. select the stat_id of the corresponding visitor IP, if such IP exists in table
2. otherwise, insert the visitor IP and return the corresponding stat_id

So whether the IP exists or does not exist, after inserting, the query should return the ID stat_id.

I've tried various approaches, but none of them prevented the deadlocks or from getting an error (for example, the stat_id ID is not being returned correctly).

Any help is welcome

Thanks in advange

Answer 1

You've got two separate unique indexes on that table, and you're checking one, then inserting into the other, then inserting into the one you initially checked. But you're not checking the first one with a restrictive lock. So two sessions can both check the stats_ip index, discover that no row exists and attempt the insert. The deadlock prevents them from both succeeding. Anyway the solution here is to perform the first lookup with a more restrictive lock, and one that locks empty ranges. Like this:

create procedure GetOrInsertStatId @ip bigint
as
begin
    set xact_abort on;
    set nocount on;

    begin transaction
    declare @stat_id int = 
      ( 
        select stat_id 
        from stat with (updlock, serializable)
        where [stats_ip] = @ip
      )

    if @stat_id is null
    begin
        insert into stat(stats_ip) values (@ip)
        set @stat_id = SCOPE_IDENTITY() 
    end

    select @stat_id stat_id

    commit transaction
end

Answer 2

Now the following versions work correctly (i.e., without deadlocks and returning a value for the ID), but the version with "WITH (NO LOCK)" is 5% faster than the version "WITH (updlock, serializable)", while the version without HINTS is 3% slower than "WITH (updlock, serializable)". These numbers are not absolute as they are extracted with my test, but it is just to give an idea.

-- WITH (NOLOCK)
DECLARE @stat_id INT = (SELECT TOP(1) stat_id FROM stat WITH(NOLOCK) WHERE stats_ip = @ip);

IF @stat_id IS NULL
BEGIN
        INSERT INTO stat (stats_ip) VALUES (@ip);

        SET @stat_id = SCOPE_IDENTITY();
END;

SELECT @stat_id AS 'stat_id';



-- WITH (updlock, serializable)
DECLARE @stat_id INT = (SELECT TOP(1) stat_id FROM stat WITH (updlock, serializable) WHERE stats_ip = @ip);

IF @stat_id IS NULL
BEGIN
        INSERT INTO stat (stats_ip) VALUES (@ip);

        SET @stat_id = SCOPE_IDENTITY();
END;

SELECT @stat_id AS 'stat_id';


-- without hints
DECLARE @stat_id INT = (SELECT TOP(1) stat_id FROM stat  WHERE stats_ip = @ip);

IF @stat_id IS NULL
BEGIN
 INSERT INTO stat (stats_ip) VALUES (@ip);

 SET @stat_id = SCOPE_IDENTITY();
END;

SELECT @stat_id AS 'stat_id';

Thanks everyone for the help