15

I recently executed an update query against 100,000 records. I realized I'd made a mistake while the query was running and quickly unplugged the network cable.

Does the update query

  1. stop processing and completely rollback?
  2. continue processing to completion and commit?
  3. stop processing and leave only part of the target rows updated?
3
22

As mentioned by Nick and Martin, the eventual status of your query depends on whether SQL Server knows about your network cable pull before the query completed. From Books Online (though I find it interesting that there are equivalent topics for this in 2000, 2005, 2008, and 2008 R2, but not 2012 or 2014):

If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

(As an aside, that word connections in the 2nd last sentence was probably meant to be transactions. I don't know how one rolls back a connection.)

In a similar way, SQL Server may undo or redo transactions during recovery after the server is shut down unexpectedly, and this will depend on the state of the transaction at the moment of shutdown. I have seen people use this tactic to achieve what you were trying to do (cancel the transaction(s)) and when the server started up again much of the work was simply redone (so net effect of their knee-jerk reaction was much closer to zero than they expected).

So rather than be subject to this, instead of doing drastic things in a panic, like yanking a network cable or shutting off the machine, I suggest in the future you have better discipline about running ad hoc queries against important systems. For example, instead of:

UPDATE dbo.sometable 
-- where *oops* I forgot this part

Have this:

BEGIN TRANSACTION;

UPDATE dbo.sometable
-- where *oops* I forgot this part

-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Then, if the update was indeed correct, you can highlight the COMMIT part and run it. If it wasn't, you can calmly highlight the ROLLBACK part and run that. You can even use add-ins like SSMS Tools Pack to edit your New Query template to include that boilerplate.

Now it could still get you in trouble in the event that you run the query and then don't either commit or rollback, because now your transaction is blocking other users. But this is better then irrevocably modifying data.

And of course, as always, have a backup you can rely on.

2
  • 5
    This is excellent advice and addresses the root of the OP's problem, but it doesn't actually answer the question of whether the query continued running or not. Aug 2 '13 at 11:01
  • 3
    Thanks @Nick, my motivation was to address the cause (which spurred the question), not the symptom, but I've updated my answer. Aug 2 '13 at 13:45
8

@Aaron is correct. Creating a transaction before your commands is your best bet. If you can't remember to do that then one option is to go into the Tools-Options setting and turn on SET IMPLICIT_TRANSACTIONS. This will automatically start a transaction as soon as certain commands are run. This includes UPDATE, DELETE etc. This appears to be a fairly complete list of any command that would "change" something. SELECT is also included in the list and will start a transaction. You can see a full list of the commands that start a transaction will this setting on here. It will not create a transaction if one is already started. Now the down side to this is that you will have to remember to COMMIT after any change made.

NOTE: Based on @Aaron's suggestion I'm going to re-emphasize this.

This is very important!  You will have to remember to COMMIT after any change made!

Basically you are trading off forgetting to BEGIN a transaction and messing something up, for forgetting to COMMIT a transaction and having it hung if you leave it open and then leave for the day. I did test just closing a query window thinking it would roll back my transaction, however it did prompt me if I wanted to commit or rollback the transaction.

enter image description here

4
  • Actually: SELECT will start a transaction (which is also documented in the link you posted) Aug 1 '13 at 22:36
  • Thanks @a_horse_with_no_name for catching that! I didn't read carefully enough and was going off of an old memory (that was obviously wrong). Aug 2 '13 at 2:50
  • 1
    This is a helpful post, but it doesn't actually answer the OP's question of whether the query continued running or not. Aug 2 '13 at 12:40
  • 2
    It was meant as an add on to @Aaron's answer. It was just to much to put in a comment. Aug 2 '13 at 14:12
2

i think it really depends:

if the command already reaches the server before you unplugged the network cable, the command will still continue to execute normally.

if you have a TransactionScope (used in .Net, not sure other languages) to encapsulate all the update commands, you probably can stop the transaction to be committed only if the transactionScope.Complete() has not been executed, but no guarantee...

2
  • 2
    You said "if the command already reaches the server before you unplugged the network cable, the command will still continue to execute normally." This is contradicted by the SQL Server BOL page that Martin linked to above. See "Errors During Transaction Processing". Aug 2 '13 at 11:04
  • you are right. with a transaction specified, the command will roll-back automatically. but as we experienced, when no transaction was specified explicitly, the command (a batch update without a transaction) was executed fully even we stopped our application in the middle, which actually broke the connection - but it's not really a good example as the timing was probably not correct. probably it's good to do some testing for that
    – Rex
    Aug 5 '13 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.