FIX: SELECT INTO Locking Behavior

• Microsoft SQL Server, version 6.5

SUMMARY

In SQL Server 6.5, SELECT INTO wraps within a transaction. Tables created by using SELECT INTO hold to the ACID (atomicity, consistency, isolation, durability) transaction properties. This also means that system resources, such as pages, extents, and locks, are held for the duration of the SELECT INTO statement. With larger system objects, this leads to the condition where many internal tasks can be blocked by other users performing SELECT INTO statements. For example, on high-activity servers, many users running the SQL Enterprise Manager tool to monitor system processes can block on each other, which leads to a condition where the SEM application appears to stop responding.

MORE INFORMATION

When you upgrade to SQL Server 6.5 Service Pack 1, SELECT INTO locking characteristics is a system settable feature. Wrapping the SELECT INTO with a transaction remains the default behavior. For users wishing not to hold system catalog locks on the activity, a trace flag has been added to allow for such operations. To apply the trace flag, start the server with the -T5302 command line parameter, or from within a query window, use the following commands:

> dbcc traceon (3604) > go > dbcc traceon (5302) > go

When the 5302 trace flag is applied and a SELECT INTO fails, the table is still created. Note that the locking behavior you select is applied for all databases within the server.

It is important to understand that even with trace flag 5302 enabled, if a SELECT INTO is executed in SQL Server 6.5 within the context of an explicit transaction (i.e. BEGIN TRAN), the system tables will still be locked until the transaction completes. This is because even though the trace flag allows the server to perform the SELECT INTO in 2 operations, the creation of the target table phase is in effect a DDL statement within a transaction.

Any CREATE TABLE statement within a transaction will hold EX_PAGE locks on sysobjects, sysindexes, and syscolumns. SQL Server 6.0 did not allow SELECT INTO to be executed as part of a user-defined explicit transaction. Attempting to do this would result in Msg 226, "SELECT INTO command not allowed within multi-statement transaction".