What is the mechanism for Transaction Rollback in sql server?

Question

What is the mechanism for Transaction Rollback in sql server?

Answer 1

Have a look at ROLLBACK TRANSACTION (Transact-SQL)

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction.

Answer 2

In terms of how it does it, all of the data modifications within the transaction are stored within the transaction log, with additional space also reserved in the log for the undo records, in the event that it has to rollback. Each transaction log has sufficient information within it, to reverse the change is has made, so that it can undo the change if required. (As well as replay them in a DR scenario)

If we take a simple delete operation as an example (since I've decoded that here as an example of the log contents) the record being deleted is stored inside the transaction log entry of LOP_DELETE_ROWS and with some non-trivial effort you can decode and demonstrate the entire row is within the log entry.

If the transaction is to be rolled back, the undo space reserved in the log is going to be used, and the row would be re-inserted. The reason for the undo reservation of space is to ensure that the transaction log can not be filled up mid transaction, leaving it no space to complete or rollback.

Answer 3

Every update in the database will first write an entry into the log containing the description of the change. Eg. if you update a column value from A to B the log will contain a record of the update, something like: in table T the column C was changed from A to B for record with key K by transaction with id I. 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.