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.

3 Answers 3


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.


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:



USE RollMeBack
--Create a table
CREATE TABLE dbo.WidgetQueue (
    QueueID bigint identity(1,1),
    QueueStatus char(1),
    SomeOtherStuff varchar(500)
--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;
GO 5



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

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.

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

Now, lets look at the transaction log again:

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

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.


The use of COMMIT or ROLLBACK must depend on what sorts of things you’re doing within the transaction:

  1. If nothing is ever written to the database, then both options perform equally well
  2. If there is a stored procedure that records any data whatsoever, such as the query execution, then you must use COMMIT
  3. If there are any triggers fired as a result of the transaction, then you must use COMMIT

If you’d like more details, Microsoft’s documentation on transactions is a remarkably worthwhile read to understand the different types of transactions, when to use them, and their options.


Assuming your code will never need to be changed, fixed, repurposed, debugged or audited, is plain wrong.

The next developer touching your code will hate you big time if you use operators, functions, etc... (not only in SQL) for their side effect and not for their intended purpose. It may as well be yourself, after a while.

Imagine you want to add some data access logging later... and good luck debugging it.

Be explicit in your code. The spaghetti features will develop anyway, don't help them.

On the particular commit/rollback question: If you really, really, really changed nothing in a transaction, SQL server will do nothing in either case - it will just destroy the transaction context.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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