If I have a SQL script with multiple Update statements in series. What happens if query device goes offline after completing half the Update Statements?
Does it rollback the currently executed Update statement only? Or all the Update statements you ran before it disconnected?
Does this behavior change if you click cancel vs just losing your connection?
Thanks in advance!
On basis of how ACID is generally implemented you have multiple possible outcomes, depending on how the statements were started/executed and depending on your client connection settings and/or client application settings.
If you had a batch of statements like this:
UPDATE [TABLE1] SET [COLUMN1] = '[VALUE1]' WHERE [PREDICATE];
UPDATE [TABLE2] SET [COLUMN3] = '[VALUE3]' WHERE [PREDICATE];
UPDATE [TABLE3] SET [COLUMN4] = '[VALUE4]' WHERE [PREDICATE];
UPDATE [TABLE4] SET [COLUMN7] = '[VALUE2]' WHERE [PREDICATE];
Then the overall outcome may vary slightly, depending on the client application settings and/or your connection settings.
One of those settings is SET IMPLICIT_TRANSACTIONS. If this setting was turned ON before the UPDATES started, then you would have to issue a COMMIT TRAN to write the data to the tables. In case of a session timeout or broken connection, the UPDATE statements will be rolled back, because no COMMIT was issued.
An explanation is given in the article SET IMPLICIT_TRANSACTIONS (Transact-SQL)
When ON, the system is in implicit transaction mode. This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first: ... UPDATE ...
The default for some client connections is described further down in the article:
The SQL Server Native Client OLE DB Provider for SQL Server, and the SQL Server Native Client ODBC driver, automatically set IMPLICIT_TRANSACTIONS to OFF when connecting. SET IMPLICIT_TRANSACTIONS defaults to OFF for connections with the SQLClient managed provider, and for SOAP requests received through HTTP endpoints.
All DML statements that were successful before the disconnect / session timeout are written to the tables as explained in the article:
When OFF, each of the preceding T-SQL statements is bounded by an unseen BEGIN TRANSACTION and an unseen COMMIT TRANSACTION statement. When OFF, we say the transaction mode is autocommit.
When a session times out or you are disconnected, then all the UPDATE statements should be rolled back. There is no autocommit turned on.
BEGIN TRAN
UPDATE [TABLE1] SET [COLUMN1] = '[VALUE1]' WHERE [PREDICATE];
UPDATE [TABLE2] SET [COLUMN3] = '[VALUE3]' WHERE [PREDICATE];
UPDATE [TABLE3] SET [COLUMN4] = '[VALUE4]' WHERE [PREDICATE];
UPDATE [TABLE4] SET [COLUMN7] = '[VALUE2]' WHERE [PREDICATE];
-- COMMMIT TRAN
-- ROLLBACK TRAN
COMMIT TRAN statement.ROLLBACK TRAN statement.UPDATE, then all UPDATES are rolled back, because neither the ROLLBACK nor the COMMIT could be issued. This is because you explicitly issued a BEGIN TRAN and did not issue either a COMMIT TRAN or ROLLBACK TRAN, because your session was closed and/or timed out.If no issues occur, then all UPDATES are committed, when you issue the COMMIT TRAN statement multiple times, depending on the @TRANCOUNT level as explained in the article.
When your INSERT statements and anything else in your unit of work is finished, you must issue COMMIT TRANSACTION statements until @@TRANCOUNT is decremented back down to 0....
If no issues occur, then all UPDATES are rolled back, if you decide to issue the ROLLBACK TRAN statement as explained in the article.
...Or you can issue one ROLLBACK TRANSACTION.
If a session timeout / disconnect occurs during the third UPDATE, then all UPDATES are rolled back, because neither the ROLLBACK nor the COMMIT could be issued. Additionaly you had explicitly issued a BEGIN TRAN and that was never COMMITed or ROLLedBACK, because your session was closed and/or timed out.
Does it rollback the currently executed Update statement only?
Normally, yes. But it depends. See the given explanations.
Or all the Update statements you ran before it disconnected?
Normally, not. But it depends. See the given explanations.
Does this behavior change if you click cancel vs just losing your connection?
Normally, not. There is no difference between cancelling the (currently) running statement vs. losing your connection during the (currently) running statement. However, the outcome may vary depending on your connection settings and/or your client's application settings.
Disclaimer
This is by no means a complete summary of all possible settings which may or may not affect how the DBMS handles "Cancel Query"/"Timeouts"/"Disconnects". But in general you can apply the ACID principles to modern DBMS.
