SQL Server 2000死锁
我们在生产 SQL Server 2000 数据库中遇到了一些非常烦人的死锁情况。
主要设置如下：
- SQL Server 2000 企业版。
- 服务器使用 ATL OLE 数据库以 C++ 进行编码。
- 所有数据库对象都通过存储过程访问。
- 所有 UPDATE/INSERT 存储过程都将其内部操作包装在 BEGIN TRANS ... COMMIT TRANS 块中。
我按照 Internet 上的几篇文章（例如 这个（忽略它指的是 SQL Server 2005 工具，同样的原理适用）。 从痕迹来看，这似乎是两个 UPDATE 查询之间的死锁。
我们采取了一些措施，可能会降低问题发生的可能性，例如：
- SELECT WITH (NOLOCK) >。 我们已将存储过程中的所有 SELECT 查询更改为使用WITH (NOLOCK)。 我们了解脏读的含义，但查询的数据并不那么重要，因为我们进行了大量自动刷新，并且在正常情况下 UI 将具有正确的值。
- 未提交的内容。 我们已将服务器代码上的事务隔离级别更改为“READ UNCOMMITED”。
- 缩小交易范围。 我们减少了事务的持有时间，以尽量减少发生数据库死锁的可能性。
我们还质疑大多数存储过程中都有事务（BEGIN TRANS ... COMMIT TRANS 块）这一事实。 在这种情况下，我的猜测是事务隔离级别是 SERIALIZABLE，对吧？ 如果我们还在调用存储过程的源代码中指定了事务隔离级别，那么适用哪一个呢？
这是一个处理密集型应用程序，我们对数据库进行大量读取（更大的百分比）和一些写入。
如果这是 SQL Server 2005 数据库，我可以使用 Geoff Dalgas 回答了有关 Stack Overflow 的死锁问题，如果这适用于我遇到的问题的话。 但目前升级到 SQL Server 2005 并不是一个可行的选择。
由于这些最初的尝试失败了，我的问题是：您将如何从这里开始？您将采取哪些步骤来减少甚至避免死锁的发生，或者我应该使用哪些命令/工具来更好地公开问题？
We are experiencing some very annoying deadlock situations in a production SQL Server 2000 database.
The main setup is the following:
- SQL Server 2000 Enterprise Edition.
- Server is coded in C++ using ATL OLE Database.
- All database objects are being accessed trough stored procedures.
- All UPDATE/INSERT stored procedures wrap their internal operations in a BEGIN TRANS ... COMMIT TRANS block.
I collected some initial traces with SQL Profiler following several articles on the Internet like this one (ignore it is referring to SQL Server 2005 tools, the same principles apply). From the traces it appears to be a deadlock between two UPDATE queries.
We have taken some measures that may have reduced the likelihood of the problem from happening as:
- SELECT WITH (NOLOCK). We have changed all the SELECT queries in the stored procedures to use WITH (NOLOCK). We understand the implications of having dirty reads but the data being queried is not that important since we do a lot of automatic refreshes and under normal conditions the UI will have the right values.
- READ UNCOMMITTED. We have changed the transaction isolation level on the server code to be READ UNCOMMITED.
- Reduced transaction scope. We have reduced the time a transaction is being held in order to minimize the probabilities of a database deadlock to take place.
We are also questioning the fact that we have a transaction inside the majority of the stored procedures (BEGIN TRANS ... COMMIT TRANS block). In this situation my guess is that the transaction isolation level is SERIALIZABLE, right? And what about if we also have a transaction isolation level specified in the source code that calls the stored procedure, which one applies?
This is a processing intensive application and we are hitting the database a lot for reads (bigger percentage) and some writes.
If this were a SQL Server 2005 database I could go with Geoff Dalgas answer on an deadlock issue concerning Stack Overflow, if that is even applicable for the issue I am running into. But upgrading to SQL Server 2005 is not, at the present time, a viable option.
As these initial attempts failed my question is: How would you go from here? What steps would you take to reduce or even avoid the deadlock from happening, or what commands/tools should I use to better expose the problem?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
一些评论：
在存储过程中显式指定的隔离级别会覆盖调用者的隔离级别。
如果 sp_getapplock 在 2000 上可用，我会使用它：
http://sqlblogcasts.com/blogs/tonyrogerson /archive/2006/06/30/855.aspx
在许多情况下，可序列化隔离级别会增加出现死锁的机会。
2000 年的好资源：
http://www.code-magazine.com /article.aspx?quickid=0309101&page=1
Bart Duncan 的一些建议也可能适用：
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
A few comments:
The isolation level explicitly specified in your stored procedure overrides isolatlation level of the caller.
If sp_getapplock is available on 2000, I'd use it:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx
In many cases serializable isolation level increases the chance you get a deadlock.
A good resource for 2000:
http://www.code-magazine.com/article.aspx?quickid=0309101&page=1
Also some of Bart Duncan's advice might be applicable:
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
我的设置场景中出现死锁的原因是在所有索引之后。 我们使用（默认生成的）
非聚集索引作为表的主键。 更改为聚集索引解决了该问题。The reason for the deadlocks in my setup scenario was after all the indexes. We were using (generated by default)
non clusteredindexes for the primary keys of the tables. Changing toclusteredindexes fixed the problem.我的猜测是您遇到了死锁，或者：
为了解决这个问题，我首先检查存储过程，并确保修改语句具有它们所需的索引。
注意：这适用于目标表和源表（尽管有 NOLOCK，UPDATE 的源表也会获得锁。检查用户存储过程扫描的查询计划。与批处理或批量操作不同，大多数用户查询和 DML 都可以工作 。
其次，我将检查存储过程以确保存储过程中的所有数据访问都以一致的顺序完成（父级 -> 子级） 通常是首选）。
My guess would be that you are experiencing deadlocks, either:
To address this, I would first examine the stored procedures, and make sure the the modifications statements have the indexes that they need.
Note: this applies to both the target tables and the source tables (despite NOLOCK, an UPDATE's source tables will get locks also. Check the query plans for scans on user stored procedures. Unlike batch or bulk operations, most user queries & DMLs work on a small subsets of the table rows and so should not be locking the entire table.
Then secondly, I would check the stored procedures to ensure that all data access in a stored procedure is being done in a consistent order (Parent -> Child is usually preferred).
除了 Alex 的回答之外：
- 查看事务是否可以缩短（例如稍后开始、较早完成、减少处理）确定您不希望哪些代码失败，并在其他代码中使用 SET DEADLOCK PRIORITY LOW
我们使用这个（SQL 2005 这里有更多选项）来确保某些代码永远不会陷入死锁并牺牲其他代码。
如果您在事务开始时使用 SELECT 来准备一些内容，请考虑 HOLDLOCK（也许是 UPDLOCK）以在持续时间内保持锁定。 我们偶尔会使用此功能，因此请停止其他进程对此表的写入。
In addition to Alex's answer:
- See if transactions can be shortened (eg start later, finish earlier, less processing)Identify which code you'd like not to fail and use SET DEADLOCK PRIORITY LOW in the other
We've used this (SQL 2005 has more options here) to make sure that some code will never be deadlocked and sacrificed other code.
If you have SELECT at the start of the transaction to prepare some stuff, consider HOLDLOCK (maybe UPDLOCK) to keep this locked for the duration. We use this occasionally so stop writes on this table by other processes.