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.
RESOLUTIONTo 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
HotfixNOTE: 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 |
---|
|