CODEWITHSUNDEEP
sql-serversql-server-2014
ahsan Mumtaz Abbasi
ahsan Mumtaz Abbasi

Reputation: 21

Deadlock in SQL Server

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 .....

Upvotes: 2

Views: 998

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6465

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.

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131676

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

Upvotes: 1

Related Questions