Article ID: 915918 - View products that this article applies to.
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:
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:
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:
http://support.microsoft.com/contactus/?ws=supportNote 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:
(http://support.microsoft.com/kb/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
Collapse this tableExpand this table
SQL Server 2005, 64-bit version
Collapse this tableExpand this table
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:
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
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:
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.
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:
http://msdn2.microsoft.com/en-us/library/ms178104(SQL.90).aspxThis 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:
(http://support.microsoft.com/kb/824684/ )Description of the standard terminology that is used to describe Microsoft software updates
Article ID: 915918 - Last Review: October 9, 2011 - Revision: 2.0
Contact us for more help
Connect with Answer Desk for expert help.