June 7, 2002 at 12:55 am
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--------------------------------------------
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 7, 2002 at 5:47 am
You are correct. The problem with SELECT INTO is the way it issues locks to block access to the data. The NOLOCK hint however in a select statement will allow reading of all data including uncomitted data (so you get dirty reads with the hint). This may not be bad for some cases but on a system that relys on committed data only you would not want to do this, especially for calculations. It is better to create your table object first then use INSERT INTO form the table. This will release the locks as soon as the INSERT is done.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 7, 2002 at 7:54 am
June 7, 2002 at 8:19 am
And then depending on what you're doing, decide if it shouldnt be a permanent table to start with. You can use the spid to identify records inside a permanent table.
Andy
June 7, 2002 at 11:00 pm
Agree with Antares, Only thing is select into should be fired before you start transaction, If a transaction is started then there is no way to fire a direct query on sysobjects till you commit a transaction.
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply