如何使用StoredProcedure避免死锁 [英] How to Avoid Dead Locks with the StoredProcedure

问题描述

我的存储过程如下所示

Update Codes Set [IsArchive]=1
	from Codes a
	inner join Party b
	on a.idhash=b.idhash and
	a.[valuehash]<>b.[valuehash]
	where a.[partyid]=@Uns

	--Update  IS Archive to 1 if ID hash is not present

		Update Codes Set [IsArchive]=1
	from Codes a
left join Party b
	on a.idhash=b.idhash 
	
	where a.[partyid]=@Uns and b.idhash is null




--Update IS Import to 1 in the Gbo CRm Party Table when ID hash and value hash is not present in  Codes

Update Party set IsImport=1
from Party a
left join Codes b
on a.idhash=b.idhash and
	a.[valuehash]=b.[valuehash]
	where a.Number=@Uns and (b.idhash is null or b.valuehash is null)

我经常会遇到死锁我们进行并行执行。

I frequently get Dead Locks when we make Parallel Executions.

我尝试添加nolock但没有运气

I tried by adding nolock but No Luck

你能帮我修改一下，这样我就不会陷入僵局。

Can you help me how to modify this so that i wont get deadlocks

Sujith

推荐答案

单个存储过程不会导致死锁，这是另一个同时运行的进程，例如以相同的顺序更新同一个表。你必须找出它是哪一个。

A single stored procedure won't cause a deadlock, it's an other process running at the same time and e.g. update the same table but in a different order. You have to find out which one it is.

你永远不能防止100％的死锁，它们可以发生。

And you can never prevent deadlocks for 100%, they can occur.

这篇关于如何使用StoredProcedure避免死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！