Transaction was deadlocked on lock resources with another process
I have following code, Which when run with 2 or more users simultenously thows an error as " Transaction (Process ID 182) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. " Can somebody help me how to avoide the above message Code is like this Create table ComID ( CompanyID int, Process varchar(40), Prefix varchar(40), StartFrom int, Suffix varchar(40), IncrementBy int, PrevNumber int ) GO Insert into ComID values(1,'Application','AA',1000,'Z',1,NULL) Insert into ComID values(1,'Billing','IN',1000,'I',1,NULL) Go Create Table tblApp(appID int identity(1,1), CompanyID int NOT NULL, AppNumber varchar(10) NOT NULL, AppFromDT datetime NOT NULL, AppToDT datetime NOT NULL, ModifiedBy int NOT NULL, ModifiedDate datetime NOT NULL, CreatedDate datetime NOT NULL) GO CREATE PROCEDURE InsertAPP( @CompanyID int ,@AppFromDT datetime ,@AppToDT datetime ,@ModifiedBy int ) AS BEGIN DECLARE @ObjectNumber varchar(20) = NULL DECLARE @AppID int = NULL BEGIN TRY Begin Tran T4 Insert into tblApp( CompanyID,AppNumber,AppFromDT,AppToDT,ModifiedBy,ModifiedDate,CreatedDate ) SELECT @CompanyID,'',@AppFromDT,@AppToDT,@ModifiedBy,GETDATE(),GETDATE() SET @AppID = IDENT_CURRENT('tblApp') -- I can not write the 'GenerateNumber' procedure directly into this sp, since GenerateNumber is a common routine and -- Is used for many processes to generate the respective number. EXEC GenerateNumber @CompanyID,'Application',@AppID,@ObjectNumber OUT Update tblApp SET AppNumber = @ObjectNumber WHERE appID = @AppID Commit Tran T4 END TRY BEGIN CATCH If @@TRANCOUNT > 0 BEGIN Rollback END SELECT ERROR_Message() END CATCH END Go CREATE PROCEDURE GenerateNumber( @CompanyId INT , @Process VARCHAR(50) , @ObjectID int, @ObjectNumber VARCHAR(50) OUT ) AS BEGIN DECLARE @IdentifierProcessId int DECLARE @Prefix varchar(20) DECLARE @StartFrom Varchar(20) DECLARE @Suffix varchar(20) DECLARE @IncrementBy int = 1 DECLARE @MaxObjectNbr varchar(20) BEGIN TRANSACTION PT UPDATE ComID SET PrevNumber = (CASE WHEN PrevNumber IS NULL THEN StartFrom ELSE (PrevNumber + IncrementBy) END) WHERE CompanyID = @CompanyID AND Process = @Process SELECT @Prefix = RIGHT('00'+ISNULL(Prefix,''),2), @StartFrom = StartFrom, @Suffix = RIGHT('0'+ISNULL(Suffix,''),1), @IncrementBy = ISNULL(IncrementBy,1), @MaxObjectNbr = PrevNumber FROM ComID WHERE CompanyID = @CompanyID AND Process = @Process COMMIT TRANSACTION PT -- If PrevNumber is not defined, then use the @ObjectID as it is to generate number IF @MaxObjectNbr IS NULL AND @StartFrom IS NULL BEGIN SET @MaxObjectNbr = @ObjectID END ELSE IF @MaxObjectNbr IS NULL AND @StartFrom IS NOT NULL BEGIN SET @MaxObjectNbr = @StartFrom END SET @Prefix = RIGHT('00'+ISNULL(@Prefix,''),2) SET @IncrementBy = ISNULL(@IncrementBy,1) SET @Suffix = RIGHT('0'+ISNULL(@Suffix,''),1) SET @StartFrom = RIGHT('0000'+CONVERT(VARCHAR,ISNULL(@StartFrom,'0000')),4) SELECT @ObjectNumber = isnull(@Prefix,'') + CASE WHEN LEN(CONVERT(VARCHAR,@MaxObjectNbr))<=4 THEN RIGHT('0000'+CONVERT(VARCHAR,@MaxObjectNbr),4) ELSE CONVERT(VARCHAR,@MaxObjectNbr) END + isnull(@Suffix,'') END Go I am using SQL SErver 2008 and database uses isolation level READ_COMMITTED_SNAPSHOT ON Run the following query in 5 to 6 query analyzer waitfor time '10:44:50' -- set the same time in query analyzer so that sp will run at the same time exec InsertAPP 1,'01/01/2007','12/31/2007',1 EDIT; Cross Posted [here] :
Sachin, I have run this script on a test machine that is far from production level performance and do not experience any problems. I am now guessing that you maybe have something happening in the background causing problems. Can you check that there are no other processes accessing the underlying tables. Are there any triggers on the tables that may be causing this? Also, please try not to double/cross post on here. This could have been added to your original question about app_lock problems from yesterday - I have added the links to the bottom of both questions to show this.
As I posted in a comment, I did manage to reproduce the deadlock scenario. I'm successfully avoiding deadlocks by removing the explicit transaction in `InsertApp` - is there any reason for having this wrapped in a transaction
There doesn't seem to be a resolution so far, but thought about the possibility of a foreign key with cascading deletes/updates on it. Have you checked to see if this could be a possible reason for the deadlocking?