What events still occur within a transaction that gets rolled back?

Question

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.

Answer 1

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.

Answer 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 ;)

Answer 3

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