Bad Idea Cowboy Hat: BEGIN TRANSACTION; GO 1000;

Leave a comment

November 27, 2019 by Kenneth Fisher

One of my favorite presentations is Revenge the SQL by Rob Volk (blog|twitter). It’s a demonstration of a lot of strange, fun, and sometimes terrifying things you can do with SQL Server. Sometimes just for amusement and sometimes if you want to burn down your house. None of it should be used at work!!!

That said, at the end of the presentation during Summit this year Rob asked if anyone had any interesting additions and maybe not so surprisingly I had thought of one.

BEGIN TRANSACTION
GO 1000

If you are already cringing you can probably skip the rest of this post. This could be really nasty at the beginning of a piece of code if someone isn’t paying attention. It actually came from a piece of code I was asked to debug a while back and has a couple of moving parts. The piece of code looked something like this:

BEGIN TRANSACTION
DELETE TOP (10000) FROM dbo.LotsOfData
WHERE Status = 'Done';
GO 1000
COMMIT

The database was in SIMPLE recovery mode and the idea was to remove data in chunks. However the developer complained that it was still blowing up the log and then when he tried to close the query window there was an open transaction.

Let me start by explaining that the number after GO means to run the above batch that number of times. So GO 10 means run the batch 10 times. In this case the DELETE was being run 1000 times, just as the developer wanted, but they missed that the BEGIN TRANSACTION was being run each time was well. Now, in reality there is only one transaction (a single ROLLBACK will undo all levels of the transaction) but each time you run BEGIN TRANSACTION it creates a new level of the transaction, and requires an additional COMMIT to clear. So in order to clear this a COMMIT had to be run an additional 999 times.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,085 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: