Technical Article

Never use select into in transactions......

,

If select into clause is used into transactions till the transaction commits sysobjects table can not acceses directly.
It can be accessed only with 'nolock' hint.

/*
To verify how blocking occurs I have create these three baches
1st batch is starting a transaction, creating a table, performing select into operation 
and waiting for 1 minute( this waiting time could be used for other selects or updates)
2nd batch is the query on sysobjects table
3rd batch is the query on sysobjects table with (nolock) option 
How to run: 
1. Open three query analyzer windows. 
2. Copy and paste each batch in seperate windows.
3. fire all the three baches simultanously started with 1st batch. 
Check sp_who for blocked processes
*/
--------------------------------------------BATCH 1 STARTED--------------------------------------------
BEGIN TRANSACTION
IF OBJECT_ID('DIM_CUSTOMER_TMP1') IS NOT NULL DROP TABLE DIM_CUSTOMER_TMP1 
CREATE TABLE DIM_CUSTOMER_TMP1 (TEST VARCHAR(200))

IF OBJECT_ID('TMP1') IS NOT NULL DROP TABLE TMP1 
SELECT  * INTO TMP1 FROM DIM_CUSTOMER_TMP1 

WAITFOR DELAY '000:01:00'
COMMIT TRANSACTION
--------------------------------------------BATCH 1 FINISHED--------------------------------------------


--------------------------------------------BATCH 2 STARTED--------------------------------------------
SELECT NAME FROM SYSOBJECTS WHERE NAME  LIKE  '%SYSINDEXES%'
--------------------------------------------BATCH 2 FINISHED--------------------------------------------



--------------------------------------------BATCH 3 STARTED--------------------------------------------
SELECT NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE NAME  LIKE  '%SYSINDEXES%'
--------------------------------------------BATCH 3 FINISHED--------------------------------------------

Rate

Share

Share

Rate