FIX: The internal deadlock monitor may not detect a deadlock between two or more sessions in SQL Server 2005
This article has been archived. It is offered "as is" and will no longer be updated.
Bug #: 459 (SQL Hotfix)
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.
This article describes the following about this hotfix release:
- The issues that are fixed by the hotfix package
- The prerequisites for applying the hotfix package
- Whether you must restart the computer after you apply the hotfix package
- Whether the hotfix package is replaced by any other hotfix package
- Whether you must make any registry changes after you apply the hotfix package
- The files that are contained in the hotfix package
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.
Hotfix informationA 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 Cumulative hotfix package (build 2153) for SQL Server 2005 is available
PrerequisitesThere are no prerequisites for this hotfix.
Restart informationYou do not have to restart your computer after you apply this hotfix.
Registry informationTo use one of the hotfixes in this package, you do not have to make any changes to the registry.
Hotfix file informationThis 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.
SQL Server 2005, 32-bit versions
|File name||File version||File size||Date||Time||Platform|
SQL Server 2005, 64-bit version
|File name||File version||File size||Date||Time||Platform|
Manually detect a long-term deadlockTo 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 (Transact-SQL)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:
blocked process threshold option
Blocked Process Report event class
Blocked Process Report event class
Reduce the delays that are caused by an undetected deadlockTo 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. 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. 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:
sp_configure 'max degree of parallelism', 1goreconfigure with overrideQuery hint (Transact-SQL)
max degree of parallelism option
- 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:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
To verify that the blocking sessions are experiencing a true deadlock, run the following line of code.
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.
Select * from sys.dm_os_waiting_tasks
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:
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.
- 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.
- You manually intervene.
- A time-out terminates one of the blocked transactions.
- You restart the SQL Server service.
824684 Description of the standard terminology that is used to describe Microsoft software updates
Article ID: 915918 - Last Review: 01/17/2015 13:44:07 - Revision: 3.0
Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Standard Edition
- kbnosurvey kbarchive kbautohotfix kbtshoot kbhotfixserver kbqfe KB915918