Sql-server – Does disconnecting the network stop a query

Networksql server

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?

Best Answer

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.

Related Question