Commit or rollback SQLServer-SQLTransaction when no data changed

May 7, 2021 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

we use the SqlTransaction-class in the backend to access data stored in the MS-SQLServer.

In some cases we definitely know that no data were changed by this transaction (and no errors occurred).
So the question is: How to close transactions in these cases?
Shall we use Commit() or Rollback(), or none of this both functions or something else?
The aim is to find the most performant way to finish the transaction in a "reasonable" way.

asked 2021-05-07 by anion

Answer

My advice

Think about it the other way around.
Only issue a ROLLBACK if you have a reason to undo everything since the start of the transaction–such as an exception, a bad state, or an explicit desire to undo everything. If everything is going as planned, then you should always COMMIT.

Why?

Issuing a ROLLBACK will result in SQL Server undoing any work that was performed inside the transaction.

Even if you "know" that SQL Server hasn’t changed data, there’s really no benefit in issuing a ROLLBACK unless you want SQL Server to undo changes because you’re worried about inadvertent changes. If you are 100% sure that nothing has changed, then I can be 100% confident saying you should just issue a COMMIT instead.

If your code has made changes that are technically updates, but are logically no change, then a ROLLBACK would be extra work.

Here’s an example:

First, let us create a new database that is fresh, clean, and pristine. It’s as clean & fresh as new bed sheets:

CREATE DATABASE RollMeBack;

ALTER DATABASE RollMeBack SET RECOVERY SIMPLE;

USE RollMeBack
GO
--Create a table
CREATE TABLE dbo.WidgetQueue (
    QueueID bigint identity(1,1),
    QueueStatus char(1),
    SomeOtherStuff varchar(500)
    CONSTRAINT PK_WidgetQueue PRIMARY KEY CLUSTERED (QueueID)
);
GO
--put some stuff in it
INSERT INTO dbo.WidgetQueue (QueueStatus, SomeOtherStuff)
SELECT  QueueStatus    = CASE c2. column_id%3
                           WHEN 0 THEN 'Q' 
                           WHEN 1 THEN 'S' 
                           WHEN 2 THEN 'L' 
                         END,
        SomeOtherStuff = c1.name + c2.name
FROM sys.columns AS c1
CROSS JOIN sys.columns AS c2;
GO 5

CHECKPOINT;

BACKUP DATABASE RollMeBack TO DISK = 'NUL;';

That’ll make a nice big table. It probably doesn’t need to be that big. Now, let’s take a fresh CHECKPOINT and look at the transaction log. I get 3 rows returned. Doesn’t matter what they say, that’s not important right now.

USE RollMeBack
GO
CHECKPOINT;

SELECT *
FROM sys.fn_dblog(NULL,NULL);

Now, we’re going to do an update, in a transaction, that logically is making no changes, but it’s actually going to update a bunch of rows. For me, this runs in 2 seconds, and updates 1892100 rows setting the status to exactly the same value as is currently there.

BEGIN TRANSACTION
    UPDATE q
    SET QueueStatus = 'S'
    FROM dbo.WidgetQueue AS q
    WHERE QueueStatus = 'S';

Now, lets look at the transaction log again:

SELECT *
FROM sys.fn_dblog(NULL,NULL);

💥 For me, that returns 2916 rows–these are 2916 log records that record the transaction in the transaction log. Your mileage may vary. These log records are basically a measure of what SQL Server did during your transaction. All these log records tell us that SQL Server did a whole bunch of work inside that transaction. Logically, it had a net zero result, and the data will look exactly the same as at the start….except SQL Server actually updated those rows.

If I issue a ROLLBACK now, SQL Server is going to undo those updates. Logically, it’s the same outcome as having left them alone, but SQL Server is just doing what it was told, and it has to go through those 2916 log records to untangle the word it did, and make sure it leaves everything exactly as it was at the start of the transaction.

If I issue a COMMIT instead, SQL Server just says "OK. DONE!" and logs the COMMIT without having to untangle work done.

And before we go, lets drop that database we created to test.

USE master
GO
DROP DATABASE RollMeBack;

Other Considerations

What if there was a trigger on my dbo.QueueStatus table? Then my update could have fired off a bunch of other work that I didn’t realize, but is still important.

You’re also putting the onus on the developer to know, understand, and maintain the fact that there was zero work done inside that transaction. Future changes to the application might introduce a code path that results in a change that should be persisted–now the application logic needs to be updated to know not to rollback.

What about logging? Even if it is just temporary logging during the development cycle to throw some stuff into a log table to try to troubleshoot a problem, that logging would be rolled back, too.

answered 2021-05-07 by Andy Mallon