0

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:

What is the mechanism for Transaction Rollback in SQL-Server?

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?

Improve this question
1
  • 1
    "the exact internal details [...] are [...] not subject to your inspection nor changes".
    – mustaccio
    Commented Jun 10, 2022 at 16:18

3 Answers 3

Reset to default
3

Rollback are mostly single threaded (this is why rollback could take way longer then the transaction you are rollbacking). https://www.brentozar.com/archive/2014/03/happens-issue-kill/

You cannot "control" how SQL will do the rollback.

To quote Brent Ozar: "What is it that you are trying to fix".

Improve this answer
2
  • Some times, When we have single thread rollback, rollback is extremely slow. Thank you for your helpful response.
    – Amirhossein
    Commented Jun 13, 2022 at 7:14
  • 1
    If possible, try to do smaller transaction, that way, the rollback will be faster.
    – Dominique Boucher
    Commented Jun 13, 2022 at 13:05
0

SQL Server 2019 introduced Accelerated database recovery (ADR).

https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts?view=sql-server-ver16

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.

Improve this answer
2
  • 2
    with ADR, SQL doesn't have to do the recovery process starting at the oldest uncommited transaction but only since the last checkpoint. On very busy instance, it may not be instanteneous however.
    – Dominique Boucher
    Commented Jun 10, 2022 at 19:25
  • Thanks for responding, but I know ADR is slow. As I rarely need to rollback a transaction, I do not believe ADR is a good idea.
    – Amirhossein
    Commented Jun 13, 2022 at 7:20
0

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.

Improve this answer
1
  • Thank you for your response. Could you please provide references for that?
    – Amirhossein
    Commented Jun 13, 2022 at 7:16

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.