Does stopping query with a rollback guarantee a 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 query after twenty minutes. Will SQL Server rollback the records updated?

Answer 1

A single update will not update some rows. It will either update all or 0.

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

This is atomicity database systems which SQL Server follows.

In other words, you don't have to do that rollback at the end, nothing was committed anyway.

When you cancel a query, it will still hold locks until everything is rolled back so no need to panic.

You could test it yourself, execute the long query, cancel it and you will notice that it takes a while before the process really end.

Answer 2

While the update statement will not complete, the transaction will still be open, so make sure you rollback manually or close out the window to kill the transaction. You can test this by including two statements in your transaction, where the first one finishes and you cancel while it's running the second - you can still commit the transaction after stopping it, 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 start this, give it enough time for the first line to finish, hit the Stop button in SSMS, then execute commit transaction, you'll see that the first change did get applied. Since you obviously don't want part of a transaction to succeed, I'd just kill the whole window after you've stopped it so you can be sure everything's rolled back.