Microsoft distributes Microsoft SQL Server 2005 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release.
In Microsoft SQL Server 2005, a deadlock may occur between two or more sessions. In very specific, rare
circumstances, the
internal deadlock monitor may not detect the deadlock.
The sessions and their current transactions may stay blocked until you manually intervene or until a time-out terminates one of the blocked
transactions.
This
undetected deadlock problem only occurs when all the following conditions are true:
The server is running SQL Server 2005 Service Pack 1 (SP1) or the original release version of
SQL Server 2005.
The server has
multiple processors.
SQL Server is configured to run queries in parallel.
One of the deadlocked statements runs in parallel across multiple
processors.
Typically, the execution plan of this deadlocked statement performs a
sort operation or a hash join operation.
The scan operation or the seek operation under this sort operation or
under this hash join operation waits for a lock.
This lock is incompatible with a lock that is held by a
separate update statement in a different session.
This different session may not be
running in parallel.
A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.
If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.
Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:
Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.
This hotfix is also included in the cumulative hotfix
package (build 2153) for SQL Server 2005 that is described in Microsoft Knowledge Base article 918222.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
918222
(http://support.microsoft.com/kb/918222/
)
Cumulative hotfix package (build 2153) for SQL Server 2005 is available
Prerequisites
There are no prerequisites for this hotfix.
Restart information
You do not have to restart your computer after you apply this hotfix.
Registry information
To use one of the hotfixes in this package, you do not have to make any changes to the registry.
Hotfix file information
This hotfix may not contain all the files that you must have to fully update a product to the latest build. This hotfix contains only the files that you must have to correct the issues that are listed in this article.
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.
To work around this problem, manually detect the long-term deadlock. Then, terminate one of the sessions that appears to be in the
deadlock state. To do this, follow these steps:
To determine the current blocking session, use one of the following methods:
In SQL Server Management Studio, click the instance name in Object
Explorer, click the Summary tab, and then click Activity - All Blocking
Transactions in the
Report list.
In SQL Server Management Studio, expand Management, right-click Activity
Monitor, and then click View locks by Process.
To determine the last batch that ran on each session, run the
following line of code.
DBCC INPUTBUFFER (<session_id>)
To terminate the session that
is causing the deadlock, run the following line of code.
KILL <session_id>
For more information, visit the following Microsoft Developer Network (MSDN) Web sites:
Another method to manually detect the long-term deadlock is to configure the
blocked process threshold. To do this, use the sp_configure stored procedure together with the blocked process threshold option. Then, monitor the Blocked Process Report event class in SQL Server Profiler, or use the sp_trace_create stored procedure and the sp_trace_setevent stored procedure for server-side tracing. For more information, visit the following MSDN Web sites:
Reduce
the delays that are caused by an undetected deadlock
To reduce
the delays that are caused by an undetected deadlock, you can use the following techniques:
Set a reasonable command time-out value in the application that
sends commands to SQL Server. When the application waits longer than
the command time-out value, the query that is running in the
deadlocked session is automatically canceled to avoid additional delays.
To set the command time-out value, use one of the following properties:
In an application that is based on ActiveX Data Objects (ADO), use the CommandTimeout property of the connection object or of the command object.
In an application that is based on Microsoft ADO.NET, use the
SqlCommand.CommandTimeout property. For more information about the
SqlCommand.CommandTimeout property, visit the following MSDN Web site:
Use the SET LOCK_TIMEOUT Transact-SQL statement in the calling application to specify the number
of milliseconds that any statement waits for a lock to be released. After the
lock time-out occurs, one of the two statements in the long-term deadlock is canceled, and that statement's transaction is rolled back. Then, the other
query obtains the required lock, and the other
query runs until it is completed. To examine the
lock time-out setting for a connection, use the following query.
SELECT @@LOCK_TIMEOUT
For more information about the SET LOCK_TIMEOUT Transact-SQL statement, visit the following MSDN Web site:
Set the query wait option by using the sp_configure stored procedure. The query wait option is a server-wide option that defines the maximum time
in seconds that any query on the server waits for a resource before the query times
out. However, this option may have an adverse effect on long-running
queries or on long-running
batch jobs that you expect to take a long time to finish.
For more information about the query wait option, visit the following MSDN Web site:
Use the OPTION (MAXDOP 1) clause to provide a hint in the problem query or in the stored
procedure. Use the sp_create_plan_guide stored procedure to force the hint to use the plan guide.
Note The MAXDOP 1 option may reduce query performance because the query may not be
divided to run on multiple processors.
Disable parallelism for the instance of
SQL Server by limiting the degree of parallelism to one degree. Use the
following code example.
sp_configure 'max degree of parallelism', 1
go
reconfigure with override
Note If the server has multiple processors and multiple high-cost queries that
regularly use parallelism, disabling parallelism may have an adverse effect on
the performance of those queries. For more information, visit the following MSDN Web sites:
Use the best-practice techniques that are outlined in SQL Server
2005 Books Online to write queries that may prevent deadlocks. Additionally, use
the best-practice techniques to tune affected queries by using supporting indexes to
reduce blocking and to avoid the lock conflicts that lead to deadlocks.
For more information about how to minimize deadlocks, visit the following MSDN Web site:
To verify that the blocking sessions are
experiencing a true deadlock, run the following line of code.
Select * from sys.dm_os_waiting_tasks
Review the information in the sys.dm_os_waiting_tasks dynamic
management view. To be a true deadlock, one execution context that is identified by the
exec_context_id column of a certain session is blocked by another session. For example, the blocking_session_id
column is populated. To be a true deadlock, that blocking session is blocked by any one of the execution contexts of the first session. Therefore, this
creates a circular dependency of locks that can never be
obtained.
Similarly, the information in the sys.dm_tran_locks dynamic
management view or
in the sp_lock stored procedure should show that there is waiting for locks. One session has the WAIT value or the CONVERT value in the request_status column. The
opposite session already has the GRANT value on the lock that is
incompatible with the required lock. Therefore, a circle of blocking occurs
in which neither session can win without intervention. The members of the sysadmin
role or of the processadmin role can manually detect the deadlock. For more information about deadlocks and how to detect deadlocks, visit the
following MSDN Web site:
This undetected deadlock problem is not caused by the deadlock monitor algorithm itself. However, this undetected deadlock problem is caused by the way that a SQL Server 2005 SOS task reports information back to the deadlock monitor thread. The information is about which other tasks are blocking deadlock monitor thread progress.
In the rare
circumstances that are mentioned in this section, the SOS task reports that a task is
blocked by a task that is null. This hotfix changes the behavior so that the
blocked task reports the task address of the main execution context for the real
blocking request ID or for the real
blocking session ID.
The following factors make
this undetected deadlock problem occur very rarely:
The events in the following steps must occur exactly in this order. Otherwise, a deadlock does not occur, or a
deadlock occurs and is detected.
This undetected deadlock problem requires a certain type
of parallel scan. The scan is nondeterministic in nature. One thread during the
parallel scan must obtain the locked data pages in a specific order. The specific order is not guaranteed to be
the same every time because the scan is nondeterministic in nature.
Therefore, this undetected deadlock problem may not occur every time that you run the same
statements with the same timing.
The following events must occur in this order for this
undetected deadlock problem to occur:
Two transactions start. For example, transaction A
and transaction B start.
Transaction A updates a row in table T first. However, transaction A does not commit yet. A row lock is being held.
Transaction B scans table T. Transaction B acquires locks that are incompatible with transaction A. For example, this event may occur when the scan is a part of a larger update
query.
The scan in transaction B is a parallel scan that is running
in a special part of a parallel query. The special part of the parallel query is called the exchange segment. Typically, the exchange
segment falls after a sort or after a hash join.
One of scanning threads in transaction B becomes blocked
behind a lock that is held by transaction A. All the other threads finish their scans.
Notice that in transaction B, each worker thread is assigned a
logical order that is nondeterministic and that is based on a first-come first-served
basis. The blocked thread must not be the last thread.
As soon as transaction B becomes blocked, transaction A
runs another update on table T. This time, transaction A becomes
blocked behind a lock that is held by transaction B.
If these events occur with this exact timing and under these conditions, the deadlock may be detected, or the deadlock may not
be detected. This is because of the nature of this problem. If the deadlock is not detected, transaction A is blocked by
transaction B, and transaction B is blocked by transaction A. This ties up
resources until one of the following events occurs:
You manually intervene.
A time-out terminates one of the blocked
transactions.
You restart the SQL Server service.
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684
(http://support.microsoft.com/kb/824684/
)
Description of the standard
terminology that is used to describe Microsoft software updates
Back to the top
