Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen

最新推荐文章于 2024-02-27 22:04:26 发布

转载最新推荐文章于 2024-02-27 22:04:26 发布 · 7.4k 阅读

文章标签：

#resources #concurrency #user #sql #query

Asp.Net 专栏收录该内容

149 篇文章

订阅专栏

本文探讨了在处理多行更新或无锁表时，使用SQL提示如noLock、updlock、readpast等避免死锁和并发问题的方法。通过实例展示了如何在更新操作中应用这些提示，并解释了它们的区别及在不同场景下的应用。

You had written update or delete statements without SQL hints like nolock, updlock, readpast etc.
This is very bad practice when you are dealing with multiple udates on rows or tables without locks.
Below scenario will explain how to use those hints to avoid deadlocks aswell as concurrency issues.

e.g.

UPDATE top 10 xyzTable with (updlock)

set x = 1

In this case if second user comes in to update xyzTable, he has to wait for first user operation to finish.

To let second user do update on next top 10 rows which are not locked by first user, you can

modify above query using readpast hint.

ReadPast hint will return row sets which are not locked by any transaction in that contxt.

UPDATE top 10 xyzTable with (updlock,readpast)
set x = 1

ReadPast and NoLock are two different hints
Readpast Only return rows which are not locked, while nolock returns
commited as well as non commited rows by other transaction this is also called as dirty reads.

SP_LOCK will written locks details.

You can avoid concurrency issues with your transaction using those lock hints.

For more information about locks in MS SQL you can visit

http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx