7

Is it true that all data modifications are undone when the transaction they are a part of gets rolled back?

For example if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?

Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE?

I know certain statements still execute though like PRINT "MESSAGE".

I'm just trying to get an idea of what kinds of events still occur.

  • I doubt DROP DATABASE can be rolled back, since data and t-log files are physically removed when you drop database.. – Aleksey Vitsko Nov 15 at 19:56
  • 1
    Last time I checked, it was not possible to DROP DATABASE inside a tx. – dean Nov 15 at 21:49
  • 1
    @dean You are correct. It is not possible. I have added that as example to my answer below. – Solomon Rutzky Nov 15 at 22:24
  • My understanding is that transactions are about updates to data pages, so a rollback would affect anything/everything modified in their scope, including any schema changes within a given database. – Zenilogix Nov 16 at 18:23
8

For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be affected by a ROLLBACK (though technically both are somewhat "cheating" in a sense):

  1. DML statements against table variables (variable, even table variables, do not participate in transactions)
  2. statements executed via a linked server and OPENQUERY (even if using a loop back server definition to connect to the current instance) if you have the linked server property of "remote proc transaction promotion" disabled (so that it doesn't attempt to enlist in the current transaction).

    OPENQUERY (with that one option disabled) works because it makes a separate connection. There are other, similar methods of making a separate connection within T-SQL. David Browne, in a comment, mentioned extended stored procedures. We can also add SQLCLR methods (no matter what T-SQL object type they are exposed as) to that list, but only if making a regular / external connection, and specifying "enlist=false;" in the connection string. We can probably also add the OLE Automation stored procedures (i.e. sp_OA*), but I am not 100% sure of these.

  3. There might be one or two statements that can be executed within an explicit transaction but cannot be rolled back, and I think I maybe ran into that many years ago, but I don't recall what it is and could have been user error (some other user, of course ;-), so you would have to consult the documentation for specifics of each statement / operation and/or test as many as you can (sometimes the documentation is incorrect ... it happens).
  4. Not sure if this counts or not as it isn't an explicit change, but is not something that goes back to what it was before the transaction started (because it doesn't even make sense that it could): Tibor Karaszi noted in a comment that "consuming an identity/sequence value" could qualify. I have tested and confirmed that sequences do not give back the requested values.

    Similarly, another value that is database state, and not specifically user data, that is not affected by a rollback is the current / last-used timestamp value (this is per-database and is returned by the system variable @@DBTS; also confirmed via test)

  5. Not sure if these count or not as they are neither an explicit change nor even the state of the database, but their values are not affected by a rollback: in a comment, David Browne mentioned SESSION_CONTEXT, which is a property of each session. Along those same lines, we can include the related / similar CONTEXT_INFO

if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?

Yes, this is the purpose of explicit transactions: to group statements together into an atomic unit of work.

Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE?

Some (maybe most) yes. Some no. You probably should test. For example, here is some DDL that does roll-back:

USE [tempdb];

IF (OBJECT_ID(N'dbo.DropMe') IS NOT NULL)
BEGIN
  DROP TABLE dbo.DropMe;
END;

CREATE TABLE dbo.DropMe (col1 INT);

BEGIN TRAN;

DROP TABLE dbo.DropMe;

ROLLBACK TRAN;

SELECT * FROM dbo.[DropMe];

But, conversely, here is some DDL that cannot be executed within an explicit (or implicit) transaction:

CREATE DATABASE [DropMe];
BEGIN TRAN;
DROP DATABASE [DropMe];
/*
Msg 574, Level 16, State 0, Line XXXXX
DROP DATABASE statement cannot be used inside a user transaction.
*/
ROLLBACK;

So, just take the two examples above and rework them for other statements that you are either curious about or need a definitive answer on.

  • 1
    One could argue that an edge case is consuming an identity/sequence value. Does that modify data? I guess it depends on your viewpoint, level of abstraction etc. – Tibor Karaszi Nov 17 at 13:45
  • @TiborKaraszi Thanks for mentioning that. I have added it as item #4 in my list. I confirmed that sequences did work that way (figured they did, but wanted to make sure). I also confirmed and added @@DBTS value to that edge-case list. – Solomon Rutzky Nov 17 at 20:09
  • 1
    Also writing to the session_context, or the error log, and of course DML run inside a CLR proc not using the context_connection, or in an extended stored procedure. – David Browne - Microsoft Nov 17 at 21:50
  • @DavidBrowne-Microsoft Thanks for pointing those out. I've gone ahead and added most of them, and added CONTEXT_INFO and OLE Automation procs. I didn't add the error log as that, while true, is outside of SQL Server (for the most part). I actually had originally planned on including SQLCLR and sp_OA*, can't remember why I didn't, but I added them now. Just FYI re: SQLCLR, by default regular connections did side-step transactions prior to SQL Server 2012, but SqlConnection default change in .NET 4.0/4.5 for enlist=true, so now you need to add false else it will enlist in the tran. – Solomon Rutzky Nov 18 at 5:50
2

is it true that all data modifications are undone when the transaction they are a part of gets rolled back?

Yes. That is the WHOLE sense of a transaction.

For example if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?

That is the definition of all changes.

Would a DDL statement get rolled back?.

Depends on the statement. RTFM is the only way to get that sorted out (i.e. it is documented on a per command basis) but generally DDL and DML in one transaction do not mix and in GENERAL you better assume that while DML changes ARE transactional (adding a field is a transaction and either all rows get it or none) you are way better off assuming transaction support is limited. That said it also depends tremendously on the sql server version. And very heavy hitters like drop database - they actualyl remove the physical files, you know. They also do NOT execute "in the database" - drop database WOULD be a transaction on the master database and the whole system.

We generally run DML in transactions these days, and this works - but we do have a way in our change scripts (with a comment in the first line) to turn of the transaction wrapping because SOME changes in DML do not execute in transactions.

I know certain statements still execute though like PRINT "MESSAGE".

ALL statements execute. Rolling back is not not executing. it is just that PRINT can not be rolled back because it is not a data change ;)

1

I've just run some tests, here are results:

  1. Drop Database cannot be performed within explicit (begin tran...) transaction
    SQL Server throws:

    DROP DATABASE statement cannot be used inside a user transaction.

  2. Drop table you can do it within explicit transaction without any problem

  3. Create View - you can do it, but first have to run begin tran..., then run create view..., and then rollback or commit. So you can do it step by step, but not all at once because SQL Server will throw:

    'CREATE VIEW' must be the first statement in a query batch.

So if cursor runs SP and simply updates the data in a table and is within an explicit transaction, it can be rolled back. But if your cursor/SP also dropping some databases then no, it can not be rolled back

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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