Click here to Skip to main content
15,889,867 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
How to remove Update query DeadLock inside TRANSACTION ISOLATION LEVEL SERIALIZABLE.The query is below.

SQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 SET NOCOUNT ON;
 WHILE @Retry > 0
 BEGIN
 BEGIN TRY 
SELECT LastID FROM tblids WITH (UPDLOCK, ROWLOCK) WHERE CentreCode =@CentreCode


-----------Facing DeadLock when we update the Query 
UPDATE dbo.tblIDs WITH (HOLDLOCK) SET @NewID= LastID =LastID+1 WHERE CentreCode =@CentreCode; 
 
Set @TokenNo= @NewID

 IF @NewID IS NULL
 BEGIN
 SET
 @NewID = 1;
 INSERT INTO tblIDs (CentreCode, LastID) VALUES (@CentreCode,
 @NewID);
 END
 SET @Retry = -2;
 /* no need to retry since the operation completed */
 END TRY
 BEGIN CATCH
 IF (ERROR_NUMBER()
 = 1205) /* DEADLOCK */
 begin
 
 SET @Retry = @Retry - 1;
 end
 ELSE

 BEGIN
 SET
 @Retry = -1;
 SET
 @EN = ERROR_NUMBER();
 SET
 @ES = ERROR_SEVERITY();
 SET
 @ET = ERROR_STATE() ;
Rollback
 RAISERROR (@EN,@ES,@ET);
 END
 END CATCH
 END
 IF @Retry = 0 /* must have deadlock'd 5 times.
 */
 BEGIN
 SET @EN = 1205;
 SET @ES = 13;
 SET @ET = 1 ;
 Rollback
 RAISERROR (@EN,@ES,@ET);
 END
Posted
Updated 27-Oct-14 1:29am
v2
Comments
Shweta N Mishra 27-Oct-14 7:44am    
Do you know why you are getting deadlock error functionally ,

I mean are you getting because there are different source/instances running at the same time from where this table gets updated?, Or the frequency of getting this table updated is very high?. how often do you receive this error?

1 solution

SQL
SELECT * FROM TABLENAME WITH (NOLOCK)
INSERT INTO TABLENAME WITH (ROWLOCK)
UPDATE TABLENAME WITH (ROWLOCK)


Use "Nolock" in select query and in transaction query use "RowLock". and Use Stored Procedure Instead of using view/direct fetching data from sql query.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900