FIX: Shared Table Lock Incorrectly Held for Lifetime of Transaction Instead of Statement (300412)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

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:

Start a SQL Profiler Trace and make sure to include the lock escalation event.
Execute a BEGIN TRANSACTION statement.
Execute the query in question.
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.

Modification Type:	Major	Last Reviewed:	10/9/2003
Keywords:	kbBug kbfix kbSQLServ2000preSP2Fix KB300412