Deadlock in SQL Server

Question

I'm facing deadlock

was deadlocked on lock resources with another process and has been chosen as the deadlock victim.

problem In SQL-Server as i'm inserting data in database by picking max id against a specific column then add a increment got the value against which record will be inserted. i'm calling a procedure as code mentioned below:

CREATE
 PROCEDURE [dbo].[Web_GetMaxColumnID]
 @Col_Name nvarchar(50)        
AS        
BEGIN       
-- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.        
 DECLARE @MaxID BIGINT;              
 SET NOCOUNT ON;   
 -- Insert statements for procedure here        
 BEGIN  
  BEGIN TRAN              
  SET @MaxID = (  
          SELECT Col_Counter  
          FROM   Maintenance_Counter WITH (XLOCK, ROWLOCK)  
          WHERE  COL_NAME = @Col_Name  
      )  

  UPDATE Maintenance_Counter  
  SET    Col_Counter     = @MaxID + 1  
  WHERE  COL_NAME        = @Col_Name  
 COMMIT  
 END      
 SELECT (  
            CONVERT(  
                VARCHAR,  
                (  
                    SELECT office_id  
                    FROM   Maintenance  
                )  
            ) + '' + CONVERT(VARCHAR, (@MaxID))  
        ) AS MaxID  

END

any one help me out .....

Deadlocks happen. You have to be able to make your code less locky or simply handle them — Joe Phillips, May 31 '17 at 06:36
Sir i have made a procedure as code mentioned. for insertion i'm just calling that procedure which return value against which i'm inserting record. when there's different users insert data at same time deadlock occurred . i need help if there's any changes required in above store procedure. — ahsan Mumtaz Abbasi, May 31 '17 at 06:41
What is the point of this code? If you want to generate sequences of numbers use a SEQUENCE. It's available in all supported versions of SQL Server — Panagiotis Kanavos, May 31 '17 at 07:24
You can also use snapshot isolation instead of exclusive locks to avoid deadlocks, which is available since 2005. — Panagiotis Kanavos, May 31 '17 at 07:26
@PanagiotisKanavos With snapshot isolation he will generate duplicates, because the Snapshot gets the value that it had when the transaction started. So two transactions can perfectly read the same value for the Col_Counter. — Marc Guillot, May 31 '17 at 07:30
@MarcGuillot I don't intend to even google for Aaron Bertrand's or Itzik Ben Gan's articles on generating sequences, since SEQUENCE is already available. There *are* duplicate questions that cover these things — Panagiotis Kanavos, May 31 '17 at 07:33
Possible duplicate of [How would you implement sequences in Microsoft SQL Server?](https://stackoverflow.com/questions/282943/how-would-you-implement-sequences-in-microsoft-sql-server) — Panagiotis Kanavos, May 31 '17 at 07:34
@PanagiotisKanavos I know, I have also told him to use sequences, even creating a replacement for his procedure using sequences. I'm not objecting to Sequences. The duplicates problem will appear on your other suggestion : to use snapshot isolation level. — Marc Guillot, May 31 '17 at 07:35

score 1 · Accepted Answer · answered May 31 '17 at 07:48

1

As Marc already answered, use SEQUENCE. It's available in all supported versions of SQL Server, ie 2012 and later. The only reason to avoid it is targeting an unsupported version like 2008.

In this case, you can set the counter variable in the same statement you update the counter value. This way, you don't need any transactions or locks, eg:

declare @counterValue bigint

UPDATE Maintenance_Counter  
SET    Col_Counter = Col_Counter + 1 , @counterValue=Col_Counter+1
WHERE  COL_NAME        = @Col_Name 

select @counterValue

answered May 31 '17 at 07:48

Panagiotis Kanavos

120,703
13
188
236

I beg to differ on "this way you don't need any transactions or locks" This statement will create an update lock to find the row to actually update and then it will transition to an exclusive lock to actually update the value. It will assist without having to lock to read the value again for the variable but it will still lock during update. Here is a good article on the subject. http://blogs.extremeexperts.com/?p=2086 – Matt Dec 20 '18 at 17:25

Marc Guillot · Answer 2 · 2017-05-31T08:25:44.307

0

Yo can use sequences to generate incremental values avoiding any blocking.

I have adapted my own Counter Generator to be a direct replacement for yours. It creates dynamically the SQL statements to manage sequences, if a Sequence doesn't exist for the value we are looking for, it creates it.

ALTER PROCEDURE [dbo].[Web_GetMaxColumnID]
    @Col_Name nvarchar(50)
AS
  declare @Value bigint;
  declare @SQL nvarchar(64);
BEGIN
  if not exists(select * from sys.objects where object_id = object_id(N'dbo.MY_SEQUENCES_' + @Col_Name) and type = 'SO') 
  begin
    set @SQL = N'create sequence dbo.MY_SEQUENCES_' + @Col_Name + ' as bigint start with 1';
    exec (@SQL);     
  end

  set @SQL = N'set @Value = next value for dbo.MY_SEQUENCES_' + @Col_Name;      
  exec sp_executesql @SQL, N'@Value bigint out', @Value = @Value out;   

  select @Value ;
END

The only inconvenience is that your values can get gaps within (because you could have retrieved a value but finally not used it). This is not a problem on my tables, but you have to consider it.

edited May 31 '17 at 08:25

answered May 31 '17 at 06:42

Marc Guillot

6,090
1
15
42

Thanks sir . but unfortunately i'm unable to use your procedure in my case. will be thankful if u can help me out – ahsan Mumtaz Abbasi May 31 '17 at 07:10
Why can't you replace your stored procedure by mine ?. Change the name and parameters, and your application won´t notice the difference. There is no way to prevent locks on your original procedure, because you need to lock Maintenance_Counter in order to not get repeated values. – Marc Guillot May 31 '17 at 07:13
when we run your query an error showing.. Msg 102, Level 15, State 1, Procedure Web_GetMaxColumnID, Line 24 Incorrect syntax near 'throw'. – ahsan Mumtaz Abbasi May 31 '17 at 07:47
I have simplified it, please, try again. – Marc Guillot May 31 '17 at 07:57

Deadlock in SQL Server

2 Answers2