SQL Server 2008：获得死锁...没有任何锁 [英] SQL Server 2008: Getting deadlocks... without any locks

问题描述

我目前正在对SQL Server 2008数据库进行一些实验。更具体地说，我有一个JDBC应用程序，该应用程序使用数百个并发线程来执行数千个任务，每个任务在数据库上运行以下查询：

I'm currently conducting some experiments on a SQL Server 2008 database. More specifically, I have a JDBC application that uses hundreds of concurrent threads to execute thousands of tasks, each of which runs the following query on the database:

UPDATE from Table A where rowID='123'

但是，我得到了每当我将隔离级别设置为高于READ_UNCOMMITTED时，都会发生大量死锁错误（SQL异常1205）。即使设置行锁定，表锁定和互斥锁定提示，它也会发生！甚至在不使用锁的快照隔离中，我仍然会遇到死锁错误。

However, I'm getting a ton of deadlock errors (SQL Exception 1205) whenever I set the isolation level to be higher than READ_UNCOMMITTED. It happens even if I set row locking, table locking, and exclusive lock hints! And even in Snapshot Isolation, which doesn't utilize locks, I still get deadlock errors.

当发生这种情况时，我通过SQL Profiler进行了跟踪以获取死锁图，但这没什么用。它显示了受害进程，该进程连接到线程池，并连接到其他数百个进程。您可以在此处查看：

I ran a trace via SQL Profiler to get the deadlock graph when this happens, but it wasn't of much use. It showed the victim process, connected to a "Thread Pool", connected to hundreds of other processes. You can check it out here:

http：// i。 stack.imgur.com/7rlv3.jpg

是否有人暗示为什么会这样？在过去的几天里，我一直在疯狂尝试解决这个问题。我目前的假设是，这与数据库实例中的可用工作线程，可用的内存量或与实际的查询级锁定无关。

Does anyone have any hints as to why this is happening? I've been going crazy over the past few days trying to figure it out. My current hypothesis is that it's something related to either available worker threads in my DB instance, the amount of memory available, or something that isn't related to actual query-level locks.

谢谢！

推荐答案

您遇到了一个更深奥的野兽：资源僵局。您所拥有的线程比不能产生子任务的线程要多（ sys.dm_os_tasks ）来执行其工作，因为所有工作人员（ sys.dm_os_workers ）正忙。反过来，忙碌的工作人员执行的任务很可能被受害者锁定，可能是普通锁。

You have encountered a more esoteric beast: a resource deadlock. What you have there is a thread than cannot spawn child tasks (sys.dm_os_tasks) to execute its work because all workers (sys.dm_os_workers) are busy. In turn, the busy workers execute tasks that are blocked, likely on ordinary locks, by the victim.

我在这里看到两个教训：

There are two lessons I see here to take home:

1）您发布的UPDATE试图平行进行。如果更新恰好与您发布的内容相同，则意味着一件事也只有一件事： rowId 没有索引。

1) The UPDATE you posted is attempting to go parallel. If the update is exactly as you posted, then it means one and only one thing: no index on rowId.

2）您已达到 最大工作线程数 设置。难怪，考虑到您滥用客户端中的线程（数百个并发线程来执行数千个任务），并且由于不需要的并行性而在服务器中将其倍增。

2) You have bounced on the upper ceiling set by max worker threads setting. No wonder, considering that you abuse threads in the client (hundreds of concurrent threads to execute thousands of task) and multiply this in the server due to unwanted parallelism.

明智的设计将使用异步执行（ BeginExecuteNonQuery ）在真正的异步连接上（ AsynchronousProcessing = true ），并使用未决请求池，因此它不会超出一定的门槛。仍然更有可能的是，您将通过表值参数传入整批更新值，然后在单个语句中更新整个组或批处理的行。我知道我所有的链接都是针对.Net，而不是Java，我不在乎，您可以自己挖掘出等效的Java功能。

A sensible design would use async execution (BeginExecuteNonQuery) on a truly async connection (AsynchronousProcessing=true) and use a pool of pending requests so it does not go above a certain threshold. More likely, still, is that you would pass in an entire batch of update values by a table valued parameter and then update an entire set or rows, batched, in a single statement. I understand that all my links are for .Net, not for Java, I don't care, you can dig out the equivalent Java functionality yourself.

所以虽然很有趣你发现了这样一个深奥的僵局，它的出现只是因为你的设计，好吧……糟透了。

So while is interesting that you discovered such an esoteric deadlock, it only shows up because your design, well... sucks.

这篇关于SQL Server 2008：获得死锁...没有任何锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！