Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Do I need SERIALIZABLE isolation level for this maintenance task?

    Code:
    BEGIN TRAN PURGE_Table
    
    	exec sp_rename 'dbo.MyTable', 'MyTable_Temp'
    
    	SELECT * INTO MyTable
    		FROM MyTable_Temp 
    		WHERE ID <= 10
    
    	drop table MyTable_Temp
    	SELECT 'Data Reset: ' + convert(char(26), getdate(), 109)
    
    	ALTER TABLE dbo.MyTable ADD CONSTRAINT
    		PK_MyTable PRIMARY KEY CLUSTERED 
    		(
    		[ID]
    		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    COMMIT TRAN PURGE_Table
    just to add, the above is not exactly my query, I am actually comparing against a date-range, but I changed the filter (to WHERE ID <= 10) for readability.
    Last edited by Gagnon; 05-13-11 at 15:02.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    Alternatively I could:

    begin tran
    copy data into 'temp'
    truncate 'main'
    copy data from temp into 'main'
    commit tran

    Is this less risky and any particular isolation level that you could recommend? The amount of data that needs to be deleted is about 95% of the total.
    Last edited by Gagnon; 05-13-11 at 16:50.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    fyi I figured this out, original code worked fine (I put: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE above the BEGIN TRAN)

    I opened up 1 window on my local server that tried to insert into this table with a 1 second delay between inserts.

    I opened up a 2nd window on my local server, modified the code above by putting a 2 second delay between the "exec sp_rename..." and the "select * into...", started execution of 1st window, than 2nd window. 2nd window completed, 1st window continued to run w/o any errors.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •