从SQlServer中删除DeadLock

问题描述

嗨

如何在TRANSACTION ISOLATION LEVEL SERIALIZABLE中删除更新查询DeadLock。查询如下。

Hi
How to remove Update query DeadLock inside TRANSACTION ISOLATION LEVEL SERIALIZABLE.The query is below.

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

推荐答案

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

在选择查询和事务查询中使用Nolock使用RowLock。并使用存储过程而不是使用查询/直接从sql查询中获取数据。

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.

这篇关于从SQlServer中删除DeadLock的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！