What is the mechanism for Transaction Rollback in sql server?

Question

What is the mechanism for Transaction Rollback in sql server?

Answer 1

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.

Answer 2

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.