分区可以解决我的死锁问题吗
我有一个应用程序可以更新 SQL Server 2005 数据库中的表。该应用程序为我的每个区域都有一个线程，并且这些线程同时运行。
在极少数情况下，我在更新表时会遇到 SQL Server 死锁错误。
我最近想到应该按区域对表进行分区。在我看来，问题应该消失，但我无法在测试环境中复制此错误，因此我无法证明这一点。
有人对此有什么想法吗？
I have an application that updates a table in my SQL Server 2005 database. The app has one thread for each of my regions and these threads run concurrently.
On rare occasions, I run into a SQL Server deadlock error when updating the table.
It has recently occurred to me that I should partition my table by region. It seems to me that the problem should go away but I can't replicate this error in my test environment so I can't prove it.
Does anyone have any thoughts on this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要深入研究被锁定的内容以及原因 - 锁是否从行锁升级为页/表锁，或者这是行锁之间的死锁。
从区域性来看，锁升级可能会以某种方式发挥作用，从您的帖子中得出的结论是，您不希望 2 个线程接触相同的记录 - 但这并不能阻止它们编辑相同的数据页/表。
就SQL Server 2005而言，锁升级是从row -> row页面->桌子。仅在 SQL Server 2008 中，分区锁才被放置在该升级中。
您需要深入研究死锁并捕获死锁图，以详细了解导致死锁的原因。有关图表的更多信息，请访问 MSDN：http:// msdn.microsoft.com/en-us/library/ms188246(v=SQL.90).aspx
You would need to dig into what was being locked and why - did the locks escalate from a row lock to a page / table lock, or was this a deadlock between row locks.
It sounds from the regionality that lock escalation may be at play in some way, the inference from your post is that you do not expect 2 threads to ever touch the same records - but that does not stop them from editing the same data page / table.
As far as SQL Server 2005 is concerned, the lock escalation is from row -> page -> table. Only in SQL Server 2008 did partition locks get placed within that escalation.
You need to dig into the deadlock and capture a deadlock graph to understand more about what is hitting the deadlock. More info on the graphs at MSDN : http://msdn.microsoft.com/en-us/library/ms188246(v=SQL.90).aspx
如果没有线程更新同一行，那么您应该使用
with rowlock提示。如果您已经在使用此提示，则问题是由标准死锁以外的其他原因引起的。 （除了死锁之外，还有其他原因导致代码运行速度如此之慢，以至于引擎正在用死锁消息杀死线程。）If no threads will update the same row then you should use the
with rowlockhint. If you are already using this hint then the problem is caused by something other than a standard deadlock. (That is something other than a deadlock is causing the code to run so slow the engine is killing the thread with the deadlock message.)