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
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
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;
--Create a table
CREATE TABLE dbo.WidgetQueue (
QueueID bigint identity(1,1),
CONSTRAINT PK_WidgetQueue PRIMARY KEY CLUSTERED (QueueID)
--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'
SomeOtherStuff = c1.name + c2.name
FROM sys.columns AS c1
CROSS JOIN sys.columns AS c2;
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.
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.
SET QueueStatus = 'S'
FROM dbo.WidgetQueue AS q
WHERE QueueStatus = 'S';
Now, lets look at the transaction log again:
💥 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.
DROP DATABASE RollMeBack;
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.