SQL Server 2008 R2中的死锁问题（.Net 2.0应用程序） [英] Deadlock issue in SQL Server 2008 R2 (.Net 2.0 Application)
问题描述
有问题的Sql Server 2008 R2实例是一个重负载OLTP生产服务器。死锁问题在几天后出现，仍然没有解决。我们收到了Xml死锁报告，列出了死锁中涉及的存储过程和一些其他细节。我会尝试从这个xml首先列出事实：
The Sql Server 2008 R2 instance in question is a heavy load OLTP production server. The deadlock issue came up a few days back and is still unresolved. We received the Xml deadlock report that listed the stored procedures involved in the deadlock and some other details. I'll try to list down the facts from this xml first:
两个存储过程涉及死锁，说SP1和SP2。根据报告 SP1在隔离级别可序列化中运行和 SP2在ReadCommitted中运行
Two stored procedures are involved in the deadlock, say SP1 and SP2. According to the report SP1 was running in Isolation level "Serializable" and SP2 was running in "ReadCommitted".
我们调查了以下内容：
-
我们将SP1
的IsolationLevel设置为SP
代码？ - 否。
Are we setting IsolationLevel of SP1 to "Serializable" inside SP or in Code? - No.
是否有其他IsolationLevel
是Serializable调用SP1的SP？ - 否。
Is any other SP whose IsolationLevel is "Serializable" calling SP1? - No.
SP1使用的表是否由
调用任何其他具有隔离功能的SP
级别为可序列化？ - 是的。
有SP的隔离
级别设置为Serializable和
访问与SP1，
相同的表，但我们不知道他们
是否正在运行在
死锁时或不是死锁时
报告只显示SP1和SP2。
Are the table used by SP1 called by
any other SP that has Isolation
Level as "Serializable"? - Yes.
There are SPs that have Isolation
Level set to "Serializable" and
access the same tables as SP1,
but we don't know whether they
were running at the time of
deadlock or not as the deadlock
report only showed SP1 and SP2.
思路：
我们考虑了以下可能的原因：
Lines of thought:
We have considered the following possible causes:
-
发生死锁是因为SP1是以可序列化形式运行的
。 - 为什么
这个SP运行在Serializable当
我还没有设置它？是隔离
级别升级（像锁做）吗？如果
我们计算出来，并使其运行为
ReadCommitted，问题是
是否已解决？
Deadlock is occurring because SP1 is running as "Serializable". - Why is this SP running in Serializable when I haven't set it? Is the Isolation level escalating (like locks do)? If we figure this out and make it run as ReadCommitted, will the issue be resolved?
SP正在运行，锁定SP1使用的
表，并在SP1和SP2之间导致
死锁。 -
这个SP不会列在
死锁报告中吗？可能死锁
报告错过这样的依赖吗？如果是
，那么我们可能只得到部分
信息。这仍然不是
解决了如何在
中运行SP1。可序列化。
Any other SP is running, locking the table used by SP1 and causes a deadlock between SP1 and SP2. - Wouldn't this SP be listed in the deadlock report? Can the deadlock report miss such a dependency? If yes then we might only be getting partial information. This still doesn't resolve how SP1 is running in Serializable, though.
建议：
-
如果此信息不足以
解决问题，
If this information is not sufficient in resolving the problem, how can I obtain more information from SQL Server for my purpose and what information should I try to collect?
更新：
这是死锁的跟踪日志信息。我更改了SP等的名称，但已检查和验证，这些更改不会错过任何相关信息。检查代码后面的注释以获取有关表等的更多信息。
Update:
This is the trace log information for the deadlock. I've changed the names of SPs etc. but have checked and verified that the changes don't miss out any relevant information. Check the notes succeeding the code for more info on tables etc.
?<EVENT_INSTANCE>
<EventType>DEADLOCK_GRAPH</EventType>
<PostTime>2010-09-07T11:27:47.870</PostTime>
<SPID>16</SPID>
<TextData>
<deadlock-list>
<deadlock victim="process5827708">
<process-list>
<process id="process5827708" taskpriority="0" logused="0" waitresource="KEY: 7:72057594228441088 (8d008a861f4f)"
waittime="5190" ownerId="1661518243" transactionname="SELECT" lasttranstarted="2010-09-07T11:27:42.657"
XDES="0x80bf3b50" lockMode="RangeS-S" schedulerid="4" kpid="2228" status="suspended" spid="76" sbid="0"
ecid="0" priority="0" trancount="0" lastbatchstarted="2010-09-07T11:27:42.657"
lastbatchcompleted="2010-09-07T11:27:42.657" clientapp=".Net SqlClient Data Provider"
hostname="xxx" hostpid="5988" loginname="xxx" isolationlevel="serializable (4)"
xactid="1661518243" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="SP1" line="12" stmtstart="450" stmtend="6536"
sqlhandle="0x0300070090cbdc7742720c00e99d00000100000000000000">
Select ... from Table1, Table2, Table4, Table5
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 2010958736]
</inputbuf>
</process>
<process id="process5844bc8" taskpriority="0" logused="1873648" waitresource="KEY: 7:72057594228441088 (0e00ce038ed0)"
waittime="4514" ownerId="1661509575" transactionname="user_transaction" lasttranstarted="2010-09-07T11:27:40.423"
XDES="0x37979ae90" lockMode="X" schedulerid="7" kpid="3260" status="suspended" spid="104" sbid="0" ecid="0"
priority="0" trancount="2" lastbatchstarted="2010-09-07T11:27:43.350" lastbatchcompleted="2010-09-07T11:27:43.350"
clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="5988" loginname="xxx"
isolationlevel="read committed (2)" xactid="1661509575" currentdb="7" lockTimeout="4294967295"
clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="SP2" line="68" stmtstart="5272" stmtend="5598"
sqlhandle="0x030007003432350f109a0c00e99d00000100000000000000">
UPDATE Table1 ...
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 255144500]
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594228441088" dbid="7" objectname="Table1" indexname="Index1"
id="lock448e2c580" mode="X" associatedObjectId="72057594228441088">
<owner-list>
<owner id="process5844bc8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process5827708" mode="RangeS-S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594228441088" dbid="7" objectname="Table1" indexname="Index1"
id="lock2ba335880" mode="RangeS-S" associatedObjectId="72057594228441088">
<owner-list>
<owner id="process5827708" mode="RangeS-S" />
</owner-list>
<waiter-list>
<waiter id="process5844bc8" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
<TransactionID />
<LoginName>xx</LoginName>
<StartTime>2010-09-07T11:27:47.867</StartTime>
<ServerName>xxx</ServerName>
<LoginSid>xxx</LoginSid>
<EventSequence>116538375</EventSequence>
<IsSystem>1</IsSystem>
<SessionLoginName />
</EVENT_INSTANCE>
SP1正在执行从5个不同的表（表1到表5）获取数据的选择（使用内部查询等等）SP2对Table1执行更新。
一个有趣的事情是SP2更新是Table1中的外键字段和Table2的主键的一个列，而Table1和Table2都是select的一部分
SP1 is performing a select that takes data from 5 different tables (Table1 to Table5) (uses inner query etc.) SP2 performs an update on Table1.
An interesting thing is one of the columns that SP2 updates is a foreign key field in Table1 and primary key of Table2 while both Table1 and Table2 are part of the select statement of SP1, not sure this is relevant but didn't want to miss out anything.
注意：indexname =Index1（在上面的死锁图中） - Index1在与Table1中的外键和Table2的主键相同的列上。
推荐答案
此 MSDN文章说明：
隔离级别具有连接范围，并且在使用SET TRANSACTION ISOLATION LEVEL语句为
连接设置后，
保持有效，直到连接被关闭或另一个隔离
级别设置。当连接关闭并返回到池时，
将保留最后一个SET TRANSACTION ISOLATION LEVEL
语句的隔离级别。重新使用池化
连接的后续连接使用在合并
连接时生效的隔离级别。
An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.
问题是连接被打开具有可序列化隔离级别;关联的事务已被处理，连接也已被处理，但连接未被销毁并转到连接池。下一次请求连接时（使用相同的连接字符串），这个连接被返回，并且由于查询未指定任何隔离级别，因此它在可序列化隔离级别中执行。
The issue was that a connection gets opened with Serializable isolation level; the associated transaction was disposed and so was the connection but the connection was not destroyed and went to the connection pool. Next time when a request for a connection was made (with same connection string) this very connection was getting returned and since the query did not specify any isolation level, it was executing in the Serializable isolation level.
基本上，如果你有一个连接池并在特定的隔离级别中打开一个连接，我们说Serializable，然后连接将返回到池，隔离级别设置为Serializable。下一次请求连接时，您不能确保不会返回此连接，因此即使通过默认隔离级别为ReadCommitted，您也可以获得这些可序列化连接之一。
Basically, if you have a connection pool and open a connection in a particular isolation level, let's say Serializable, then the connection will go back to the pool with the isolation level set to Serializable. Next time you request a connection, you can't be sure that this connection will not be returned so even through the default isolation level is ReadCommitted you may get one of these "Serializable" connections.
另一个警告是，每次设置隔离级别为Serializable（或任何其他事项），你可能会选择不同的连接，慢慢你可能污染越来越多
Another caveat is that each time you set the isolation level to Serializable (or anything else for that matter) you may be picking different connections and slowly you may pollute more and more connections in the connection pool by setting their isolation level to Serializable (or whatever you set).
我没有找到任何机制来重置连接池的连接（当它是在执行我的查询后回到连接池）。一种解决方法是显式重置每个连接的隔离级别。但这是单调乏味。
I didn't find any mechanism to reset the disposing connection (when it was going back to the connection pool after executing my query). One workaround is to explicitly reset the isolation level for each connection. But this is tedious.
因此，最佳替代方案是为不同的隔离级别创建单独的连接池。
这篇关于SQL Server 2008 R2中的死锁问题（.Net 2.0应用程序）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
