PRB: INSERT INTO EXECUTE Holds EX_PAGE Locks on System Tables (162753)

SYMPTOMS

A stored procedure that creates one or more temporary tables generates EX_PAGE locks on syscolumns, sysobjects, and sysindexes in tempdb when invoked as part of an INSERT INTO EXECUTE statement. This behavior occurs even if the stored procedure does not explicitly define transactions. This problem may place undesirable constraints on concurrent access to tempdb. Running the same stored procedure independently of the INSERT INTO EXECUTE statement does not result in this kind of locking.

This locking does not occur if you run the stored procedure on a remote computer.

CAUSE

This behavior is caused by the advanced support for the data definition language (DDL) within transactions in SQL Server 6.5. See "Data Definition Language in Transactions" in "What's New in SQL Server 6.5," (in the Books OnLine).

When a stored procedure that creates temporary tables is run as part of an INSERT INTO EXECUTE statement, it is enclosed within a single transaction. Therefore, the relevant pages in syscolumns, sysobjects, and sysindexes within tempdb are exclusively locked for the duration of the transaction.

When the stored procedure is run independently of the INSERT INTO EXECUTE command, each statement within the stored procedure is treated as atomic, and locks are released immediately upon completion of the statement (assuming no transactions are defined explicitly).

WORKAROUND

To work around this problem, do either of the following:

Do not create temporary tables within a stored procedure that is invoked by an INSERT INTO EXECUTE statement.

-or-
Run the stored procedure remotely.

MORE INFORMATION

Trace flag 5302 does not affect this behavior. For more information on Trace flag 5302, see the following article in the Microsoft Knowledge Base:

153441 : FIX: SELECT INTO Locking Behavior