FIX: SELECT INTO Locking Behavior
This article was previously published under Q153441
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 14818 (DCR, 6.50)
In SQL Server 6.5, SELECT INTO wraps within a transaction. Tables createdby 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 SELECTINTO statement. With larger system objects, this leads to the conditionwhere many internal tasks can be blocked by other users performing SELECTINTO statements. For example, on high-activity servers, many users runningthe SQL Enterprise Manager tool to monitor system processes can block oneach other, which leads to a condition where the SEM application appears tostop responding.
When you upgrade to SQL Server 6.5 Service Pack 1, SELECT INTO lockingcharacteristics is a system settable feature. Wrapping the SELECT INTO witha transaction remains the default behavior. For users wishing not to holdsystem catalog locks on the activity, a trace flag has been added to allowfor such operations. To apply the trace flag, start the server with the-T5302 command line parameter, or from within a query window, use thefollowing commands:
dbcc traceon (3604)godbcc traceon (5302)go
When the 5302 trace flag is applied and a SELECT INTO fails, the table isstill created. Note that the locking behavior you select is applied for alldatabases within the server.
It is important to understand that even with trace flag 5302 enabled, if aSELECT INTO is executed in SQL Server 6.5 within the context of an explicittransaction (i.e. BEGIN TRAN), the system tables will still be locked untilthe transaction completes. This is because even though the trace flagallows the server to perform the SELECT INTO in 2 operations, the creationof the target table phase is in effect a DDL statement within atransaction.
Any CREATE TABLE statement within a transaction will hold EX_PAGE locks onsysobjects, sysindexes, and syscolumns. SQL Server 6.0 did not allow SELECTINTO to be executed as part of a user-defined explicit transaction.Attempting to do this would result in Msg 226, "SELECT INTO command notallowed within multi-statement transaction".
NOTE: This problem does not apply to SQL Server 7.0 and later.
Article ID: 153441 - Last Review: 10/26/2013 03:54:00 - Revision: 4.0
Microsoft SQL Server 6.5 Standard Edition
- kbnosurvey kbarchive kbfix kbnetwork KB153441