Microsoft KB Archive/300412

= FIX: Shared Table Lock Incorrectly Held for Lifetime of Transaction Instead of Statement =

Article ID: 300412

Article Last Modified on 10/9/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q300412



BUG #: 353794 (SHILOH_BUGS)



SYMPTOMS
The execution of a query that results in lock escalation may lead to the table lock being held for the entire lifetime of the transaction, not just the Transact-SQL statement, when the connection is operating at a read committed isolation level.

To confirm if a query is encountering the problem:
 * 1) Start a SQL Profiler Trace and make sure to include the lock escalation event.
 * 2) Execute a BEGIN TRANSACTION statement.
 * 3) Execute the query in question.
 * 4) Execute the sp_lock stored procedure.

The trace indicates that the lock escalation took place. The sp_lock stored procedure output shows that the shared table lock on the table in question remains.

After you complete (commit or rollback) the transaction, execution of sp_lock then indicates the proper release of the lock.



CAUSE
The table lock is not properly released at the end of the statement and remains for the entire scope of the transaction.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix
NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.

The English version of this fix should have the following file attributes or later:   File name     Platform   Description ---  S80306i.exe   Intel       RTM Based correction S80404i.exe  Intel       SP1 based correction NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.



WORKAROUND
Minimize the use of SELECT statements within a transaction.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



MORE INFORMATION
Holding the lock is appropriate for levels of isolation more restrictive than read committed. However, when the connection is operating at read committed, or a lower isolation level, you do not want the lock to be held.

Certain physical operations, such as bookmark lookups, can require that portions of a statement (query) run at more restrictive isolation levels. SQL Server identifies these physical operations and makes proper adjustments to the query plans to ensure data integrity. In doing so, queries can obtain an isolation level that is more restrictive than read committed and then encounter this problem.

Keywords: kbbug kbfix kbsqlserv2000presp2fix KB300412

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.