Exclusive locks are not working properly. I have setup database to READ_COMMITTED_SNAPSHOT level. I need to syncronize the one code. I have a following code DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'Upsert_app_lock', @LockMode = 'Exclusive', @LockOwner = 'session', -- I tried with Transaction as well @LockTimeout = 600000, @DbPrincipal = 'public -- Tried with DBO as well Update [Table] Set [column] = [Value] WHERE id = [id] SELECT [column] from [Table] WHERE id = [ID] EXEC @res = sp_releaseapplock @Resource = 'Upsert_app_lock', @DbPrincipal = 'public', @LockOwner = 'Session' The above code is implemented in the sp, when two or more user executes this sp at the same time, I am getting following error Transaction (Process ID 186) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Even in profiler it is showing output as Deadlock Chain SPID = 186 0:[Upsert_app_lock]:(42cb47b4). Only one process out of two or more process is able to get access of lock. My question is why other processes do not wait for a lock to be released. When I ran the sp at a time with two or more users, only one user can able to run it successfully rest all gets the above mentioed error. SQL server does not allow other process to wait untill lock gets released, it throws the deadlock victim error. What is wrong with this code ? EDIT: Cross Posted [here] :
I couldn't reproduce this myself - I had no problems with deadlocks, even running multiple versions at the same time. However, you are doing something I cannot quite understand. You set `[column]` to a certain value, then select this from the table again. If you have no error, then this value doesn't need selecting again - so you could remove that from the equation IMO. If you want to make changes that are controlled through transactions, I would use explicit transactions to achieve this rather than app_locks - I am not really familiar with them and see no reason for their use here: BEGIN TRANSACTION UPDATE [Table] SET [column] = [Value] WHERE id = [id]; SELECT [column] FROM [Table] WHERE id = [ID]; COMMIT TRANSACTION If you are on SQL 2005 and newer you can also handle errors inside transactions using TRY/CATCH, so an error can rollback any open transaction - I don't know how to do that with app_locks: BEGIN TRY BEGIN TRANSACTION UPDATE [Table] SET [column] = [Value] WHERE id = [id] ; SELECT [column] FROM [Table] WHERE id = [ID] ; COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH
Thanks for the answer, Let me explain what I am trying to do with this, I have table Create table ComID ( CompanyID int, Process varchar(40), Prefix varchar(40), StartFrom int, Suffix varchar(40), IncrementBy int, PrevNumber int ) this table stores the Company wise value which I need to generate the ApplicationNumber, Billing invoiceNumber, Paymentnumber and so for my project. where Process is Name of module for which it needs to generate number like Application, billing etc. Prefix is - actual prefix to be attached to Application Number like 'AA' StartFrom - First number of application or any entity. Suffix is suffix for above entity number Incrementby is - Based on PrevValue (Prev application number), next number is incremented by this value PrevNumber - It stored the Last generated application number. Now my sp is like this DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'Upsert_app_lock', @LockMode = 'Exclusive', @LockOwner = 'session', -- I tried with Transaction as well @LockTimeout = 600000, @DbPrincipal = 'public -- Tried with DBO as well UPDATE ComID SET PrevValue = (CASE WHEN PrevValue IS NULL THEN StartFrom ELSE (PrevValue + IncrementBy) END) WHERE CompanyID = @CompanyID AND Process = @Process SELECT @Prefix = RIGHT('00'+ISNULL(Prefix,''),2), @Suffix = RIGHT('0'+ISNULL(Suffix,''),1), @MaxObjectNbr = PrevValue FROM CompanyIdentifier WHERE CompanyID = @CompanyID AND Process = @Process EXEC @res = sp_releaseapplock @Resource = 'Upsert_app_lock', @DbPrincipal = 'public', @LockOwner = 'Session' Number will be return like SELECT @Prefix + @MaxObjectNbr + @Suffix This sp is getting executed from multiuser for multiple entities in the application to generate company wise the respective number. Database snap shot level is READ\_COMMITTED\_SNAPSHOT. Since when this sp is run in multiuser enviornment it is throwing deadlock victim error. Hence I applied the lock and that is not working for me.