All we need is an easy explanation of the problem, so here it is.
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
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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; 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;
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
ROLLBACK must depend on what sorts of things you’re doing within the transaction:
- If nothing is ever written to the database, then both options perform equally well
- If there is a stored procedure that records any data whatsoever, such as the query execution, then you must use
- If there are any triggers fired as a result of the transaction, then you must use
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂