Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
2  
once a query hits the server it will continue unless you cancelled the query on the server. –  JP Chauhan Aug 1 '13 at 8:58
4  
1  
Martin's comment provides the direct answer to your question, robocop. If the network notifies SQL Server of the disconnect before your query completes running, then SQL Server will roll it back. Otherwise, if the query completes before SQL Server is told there was a network disconnect, then it will be committed. In no case (assuming you wrote a single update query) will SQL Server execute a partial update. –  Nick Chammas Aug 2 '13 at 11:12
add comment

3 Answers 3

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.

share|improve this answer
3  
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. –  Nick Chammas 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. –  Aaron Bertrand Aug 2 '13 at 13:45
add comment

@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

share|improve this answer
    
Actually: SELECT will start a transaction (which is also documented in the link you posted) –  a_horse_with_no_name 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). –  Kenneth Fisher 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. –  Nick Chammas 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. –  Kenneth Fisher Aug 2 '13 at 14:12
add comment

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...

share|improve this answer
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". –  Nick Chammas 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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