question

dsmwb-9168 avatar image
0 Votes"
dsmwb-9168 asked ·

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

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

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 Votes 0 ·

Here it is

75153-image.png75172-dl.xml


0 Votes 0 ·
image.png (69.6 KiB)
dl.xml (4.6 KiB)

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

0 Votes 0 ·
Show more comments
DavidBrowne-msft avatar image
0 Votes"
DavidBrowne-msft answered ·

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

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

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


75125-image.png75148-dl2.xml


0 Votes 0 ·
image.png (72.1 KiB)
dl2.xml (3.7 KiB)
dsmwb-9168 avatar image
0 Votes"
dsmwb-9168 answered ·

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

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