Article ID: 159747
Article Last Modified on 2/22/2005
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q159747
SUMMARY
If you create a local temporary table in a stored procedure, enter a user- defined transaction, and then cancel the query, exclusive and update locks appear on the tempdb system catalog. These locks persist until the transaction is ended with a COMMIT or a ROLLBACK, or the client disconnects. The locks blocks other data definition language (DDL) within tempdb. This is expected design behavior.
MORE INFORMATION
The scope of temporary tables created within a stored procedure is the boundary of that stored procedure. After exiting the stored procedure, the temporary tables are automatically dropped. Canceling a query running in a user-defined transaction cancels the query and the batch, but does not abort the transaction. For more information, see the following article in the Microsoft Knowledge Base:
117143 : When and How to Use dbcancel() or sqlcancel()
This behavior may cause the non-intuitive situation of locks that materialize in tempdb only after a query is canceled, and that persist until the end of the transaction. Because canceling the query means that the temporary tables must be dropped, system catalog modifications are required. Yet if the cancellation is within a user-defined transaction, all exclusive and update locks acquired (such as those to modify the catalog when the server drops the temporary tables) must be retained until the end of the transaction. This typically includes locks on sysobjects, sysindexes, syscolumns, syscomments, sysprotects, and syskeys in tempdb.
This scenario may occur if you run a stored procedure that creates a temporary table, enters a user-defined transaction, and then gets blocked on a lock or times out (or the user cancels the query). Before the cancellation, you do not see locks on the tempdb system catalog. After the cancellation, locks suddenly appear. This can be reproduced by using the following statements:
create procedure p1 as
create table #t1 (a int)
begin transaction
waitfor delay "00:55:55"
/* EXEC p1, then run sp_lock and note that no locks are acquired. */
/* Cancel the query with the red button in ISQL/w or CTRL+C in ISQL. */
/* After cancellation, run sp_lock from another connection, and note
/* the locks. Issuing ROLLBACK TRANSACTION then drops the locks. */
It is important for applications to carefully manage transactional boundaries with respect to query cancellation. As discussed in article Q117143 (noted above), canceling a query does not cause the transaction to be aborted. This is by design. In the case of a large multi-statement transaction, the programmer may want to cancel a single query within the transaction without aborting the entire transaction. This allows flexibility in application design. However if queries are canceled without exercising proper transactional care, locking and concurrency problems may arise. This problem may occur in a variety of scenarios, not just the one described in this article.
Keywords: kbinfo kbusage KB159747
