BEGIN/COMMIT TRANSACTION question

Question

BEGIN/COMMIT TRANSACTION question

MyDoggieJessie

SSC-Forever

Points: 44486
More actions
February 24, 2011 at 3:18 pm

#246115

I am attempting to prune out data from some tables that have millions of rows of data (which who want to archive off (TSQL not included below, just the delete is included). I was deleting about 2.6 million rows and as I was checking on the remaining rowcounts I noticed I was down to 220,000+ records - plus! However a few minutes later I checked it again and it had went up to 560,000+ records. Fired off sp_whom2 and saw that it was in ROLLBACK (strange) - after about an hour I was back up to 1,912,623 rows in the table.
How on earth is this possible if I COMMITTED the deletion in 10,000 record increments?
Should the ROLLBACK only haved rolled back the last 10,000 records it tried to delete???
When the code below fires, and there @@ERROR = 0 shouldn't the operation be committed?
BEGIN TRANSACTION
SET @RowCount = @RowCount + @batchsize
/* Delete the data */
SET @SQL = '
DELETE TOP (' + CAST(@batchsize as varchar(6))
+ ') FROM ' + RTRIM(@PhyDB) + '.' + RTRIM(@Schema) + '.' + RTRIM(@Table)
+ ' WITH(ROWLOCK) WHERE PostTime < ''' + RTRIM(@ArchiveDate ) + '''
'
EXEC sp_executesql @SQL
SET @batchsize = @@ROWCOUNT
/* Determine if transaction needs to be committed or rolledback */
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
PRINT('Transaction completed')
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT('Transaction rolled back')
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

several comments here:

@@ERROR is only correct for the statement immediately preceeding it so is not reflecting the status of the actual delete.

I think try catch is a superior way to trap and handle errors, because using the old methodolog, the procedure (or script) will terminate and rollback on serious errors before it even gets to your @@error check. That is what I suspect may be happening in this case.

The probability of survival is inversely proportional to the angle of arrival.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Serge Mirault (2/24/2011)
How on earth is this possible if I COMMITTED the deletion in 10,000 record increments?

Do you have a higher level wrapper transaction?

Should the ROLLBACK only haved rolled back the last 10,000 records it tried to delete???
When the code below fires, and there @@ERROR = 0 shouldn't the operation be committed?

AFAIK, yes, but @@ERROR will always be 0... because it's checking the SET statement to @@ROWCOUNT

You want to do this immediately after the sp_executeSQL:

SELECT

@batchsize = @@ROWCOUNT,

@ErrorVar = @@ERROR

Then you do this:

IF @ErrorVar = 0 ....

I assume, from your statement, you never saw a series of prints of transaction success or rollback?

Also, you might want to throw in a checkpoint in your loop, say every 10 runs or so. Just makes things easier on your logfile.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

OMG...talk about an ID10T error...I think maybe I spent too much time looking at it to see the obvious! :s

Thanks for pointing it out 🙂

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience