All we need is an easy explanation of the problem, so here it is.
Would it be possible to rollback a transaction using a single thread?
I tried to find an answer to this question, but I couldn’t find one that provided references. I read about rollback mechanisms. See below:
If you rollback the transaction, the engine will start scanning the
log backward looking for records of work done by your transaction and
will undo the work: when it finds the record of update from A to B,
will change the value back to A. An insert will be undone by deleting
the inserted row. A delete will be undone by inserting back the row.
This is described in Transaction Log Logical Architecture and
Write-Ahead Transaction Log.
This is the high level explanation, the exact internal details how
this happen are undocumented for laymen and not subject to your
inspection nor changes.
Rollbacking a transaction takes longer than executing it.
There are several reasons for this.
Now, the question is, how can I test in various scenarios how much resources (single/multithreaded) are consumed by these operations?
From what I have heard, a single thread is claimed. However, I cannot find any evidence to support that claim.
If a single thread operation is performed, can the behavior be changed to multi thread? How about the opposite?
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.
Rollback are mostly single threaded (this is why rollback could take way longer then the transaction you are rollbacking).
You cannot "control" how SQL will do the rollback.
To quote Brent Ozar: "What is it that you are trying to fix".
SQL Server 2019 introduced Accelerated database recovery (ADR).
If you have ADR enabled, transaction rollback during database recovery is instantaneous, irrespective of the time that the transaction has been active or the number of updates that has performed.
So some times, in case of emergences it is better to restart you server than wait for the rollback.
Also, if I remember correctly, before ADR rollback during database recovery phase was multithreaded.
In Sql server there is not multi thread concepts like thread management by other high level language (like c#), and mechanisms for rollback is out of your control and it implemented and managed by internal sql engine.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂