INF: Canceled Transaction in a Temp Table Causes X and U Locks

Last reviewed: April 9, 1997
Article ID: Q159747

The information in this article applies to:

Microsoft SQL Server, version 6.5

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:

   ARTICLE-ID: Q117143
   TITLE     : 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.

Additional query words:
Keywords : kbusage SSrvGen SSrvLock
Version : 6.5
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.