Stop request with rollback guarantees rollback

Question

Say I have a query like this:

BEGIN Transaction UPDATE Person SET Field=1 Rollback 

There are one hundred million people. I stopped the request after twenty minutes. Will SQL Server restart record records?

Answer 1

One update will not update some rows. It will either update everything or 0.

So, if you cancel the request, nothing will be updated.

These are atomic database systems that follow SQL Server.

In other words, you do not need to do this rollback at the end, nothing has been done anyway.

When you cancel the request, it will still hold locks until everything rolls back, so there is no need to panic.

You can test it yourself, run a long request, cancel it, and you will notice that it takes some time before the process really ends.

Answer 2

Until the update statement completes, the transaction will still be open, so make sure you roll back manually or close the window to kill the transaction. You can verify this by including two statements in your transaction in which the first ends, and you cancel it while it starts the second - you can still complete the transaction after it stops, and then get the first half of your results.

 BEGIN Transaction UPDATE Person SET Field=1 WHERE Id = 1 UPDATE Person SET Field=1 Rollback 

If you run this, give it enough time to finish the first line, click the Stop button in SSMS, then commit transaction , you will see that the first change has been applied. Since you obviously do not want the part of the transaction to be successful, I would just kill the whole window after you stopped it so you can make sure that everything is rolled back.

Answer 3

Since you opened the transaction, “Stopping the request” manually does not complete the transaction, this transaction will still be open, and all subsequent queries to this table will be blocked.

You can perform any of the following options

• Kill the connection with the KILL SPID command (SPID is the process identifier of your connection) Note: This will automatically roll back your changes, you can control the rollback status with the KILL SPID WITH STATUSONLY command (after killing).
• run the ROLLBACK command manually

** SPID is your request identifier, you can find it from the sys.sysprocesses table / you can also find it in the Management Studio query. The window, which is in brackets, you can also find it in the lower right corner of your studio control next to the login name.

SQLQuery2.sql example ... (161) - 161 - your spid.