SELECT INTO System Catalog Locking Myth

[This is a comment from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22988582.html%5D

SQL 7 locked tempdb..sysobjects at the table level on some types of Select Into. SQL 2000 doesn’t

http://www.sqlservercentra l.com/arti cles/Perfo rmance+Tun ing/ tempta bles/148/
Problem in 6.5, mostly fixed in 7.0

http://support.microsoft.c om/kb/1623 61
(this link also contains detailed info on how to check locks. for those inclined, verify SQL 7.0 locks obtained for SELECT INTO vs those for 2000/2005. you’ll find that I am right)
VI. Blocking Caused by SQL Server 6.5 Atomic SELECT INTO Behavior

By definition, SQL Server treats each statement as a separate transaction. Beginning with SQL Server version 6.5, SELECT INTO was made consistent with this standard by including the table creation and data insert phases in a single atomic operation. A side effect of this is that locks on system catalog tables are maintained for the duration of a SELECT INTO statement. This is more frequently seen in tempdb, because applications often do SELECT INTO temporary tables. Blocking caused by this action can be identified by examining the locks held by the blocking SPID. The atomic SELECT INTO behavior can be disabled with trace flag 5302. For more information, see the following article in the Microsoft Knowledge Base:
153441 (http://support.microsoft. com/kb/153 441/EN-US/ ) FIX: SELECT INTO Locking Behavior

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s