Avoid deadlocks on select / insert in case of concurrency

Question

question

dsmwb-9168 asked · Mar 07 at 03:06 PM

Avoid deadlocks on select / insert in case of concurrency

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

azure-sql-database

· 6

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman · Mar 07 at 05:46 PM

Post an example of a deadlock graph. See this answer for an query to get the deadlock info from the system_health XE trace file.

0 ·

dsmwb-9168 DanGuzman · Mar 07 at 05:59 PM

Here it is

75172-dl.xml

0 ·

image.png (69.6 KiB)

dl.xml (4.6 KiB)

DanGuzman dsmwb-9168 · Mar 07 at 10:23 PM

I just noticed ALLOW_ROW_LOCKS = OFF on the unique constraint. Try ALLOW_ROW_LOCKS = ON,

0 ·

Show more comments

Answer 1 · 2021-03-07T22:06:48.32Z

DavidBrowne-msft answered · Mar 07 at 10:06 PM

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

· 1 ·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

dsmwb-9168 · Mar 08 at 01:23 AM

thanks for the reply and the explanation, here the deadlock graph for your code, attached you will find also the corresponding XML.

75148-dl2.xml

0 ·

image.png (72.1 KiB)

dl2.xml (3.7 KiB)

Answer 2 · 2021-03-09T10:58:32.697Z

dsmwb-9168 answered · Mar 09 at 10:58 AM

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

·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question