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

Answer 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