question

sachin K avatar image
sachin K asked

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][1] [1]: http://ask.sqlservercentral.com/questions/30052/about-sp_getapplock
deadlock
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Issue is resolved now, let me explain, In the above script I have used the 'SCOPE_IDENTITY()' instead of IDENT_CURRENT('tblApp'). Also per Kev, I have removed the inner trasaction both thing helps me to resoved the issue. This was sample script I have created to reproduce the issue, in my application I have also applied the same changes but issue was not resolved, I could able to print deadlock graph after setting deadlock priority to low, and it was the Index created on tables were causing the dead lock. After setting fill factor to 100 for those indexes issue was resolved. Thanks to all of you for the help.
0 Likes 0 ·
@sachin : glad you got it sorted in the end.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@WilliamD : Agree there is something else going on here. @Sachin : it can be difficult to diagnose a deadlock without the deadlock info - just giving the code doesn't neccessarily show what exactly is deadlocked. Do you have the wait-for graph in SQL logs?
0 Likes 0 ·
Ok, Thanks William for your reply. I do not have anything running behind the scene, no triggers are defined on this table as well. Using SQL server profiler, I have also selected DeadLock Graph, chain, aquire, relesed events. It is showing any entry corresponding to Deadlock graph, however it does shows the following entry Deadlock Chain SPID = 187 1:51557:0 In my case deadlock victim error was thrown immd when sript is getting executed at the same time. Only one script runs successfully. Have you set the database isolation level to READ_COMMITTED_SNAPSHOT ? Also Have you run at script at least 6 to 7 windows at the same time using Waitfor Time 'HH:MM:SS' ?
0 Likes 0 ·
Sachin - I did everything you said, set the database the right way, the number of queries and the waitfor so they all start at the same time. Each connection started, one of them won the race and got in first, the rest followed without any errors. Please check out which spid 187 was, also check what object 1:51557:0 is.
0 Likes 0 ·
@Sachin - I've just managed to reproduce your deadlock - I'll be back soon........
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Unfortunately I am still facing the deadlock error. I have removed the tractions from child sp. Initially I have added the lock for the same reason, to synchronize the Update statement in the InsertApp sp. But that too was not helped.
0 Likes 0 ·
Really could do with the deadlock graph - I can't be sure that the deadlock I saw is the same as the one you are getting, especially if my fix isn't working for you. You say you didn't get a deadlock graph in Profiler - have you enabled trace flag 1204? See
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.