What is the mechanism for Transaction Rollback in sql server?
在sql server中进行事务回滚的机制是什么?
3 个解决方案
#1
8
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.
数据库中的每次更新都将首先在日志中写入包含更改说明的条目。例如。如果您将列值从A更新为B,则日志将包含更新记录,例如:在表T中,C列已从A更改为B,用于记录,其中键K由ID为I的事务处理。如果您回滚事务,引擎将开始向后扫描日志,查找事务完成的工作记录,并撤消工作:当它找到从A到B的更新记录时,将值更改回A.插入将被撤消删除插入的行。通过插入行来撤消删除。事务日志逻辑体系结构和预写事务日志中对此进行了描述。
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.
这是高级别的解释,具体的内部细节如何发生这种情况对于非专业人员没有记录,不受检查和更改。
#2
3
Have a look at ROLLBACK TRANSACTION (Transact-SQL)
看看ROLLBACK TRANSACTION(Transact-SQL)
Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction.
将显式或隐式事务回滚到事务的开头或事务内的保存点。
#3
3
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)
就其如何做而言,事务中的所有数据修改都存储在事务日志中,如果必须回滚,还会在日志中为撤消记录保留额外的空间。每个事务日志中都有足够的信息来反转已经发生的更改,以便它可以根据需要撤消更改。 (以及在DR场景中重播它们)
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.
如果我们以一个简单的删除操作为例(因为我在这里将其解码为日志内容的一个例子),被删除的记录存储在LOP_DELETE_ROWS的事务日志条目中,并且您可以通过一些非常重要的工作来解码和演示整行是否在日志条目中。
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.
如果要回滚事务,将使用日志中保留的撤消空间,并重新插入该行。撤消空间预留的原因是为了确保事务日志在事务处理期间无法填满,使其无法完成或回滚。
#1
8
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.
数据库中的每次更新都将首先在日志中写入包含更改说明的条目。例如。如果您将列值从A更新为B,则日志将包含更新记录,例如:在表T中,C列已从A更改为B,用于记录,其中键K由ID为I的事务处理。如果您回滚事务,引擎将开始向后扫描日志,查找事务完成的工作记录,并撤消工作:当它找到从A到B的更新记录时,将值更改回A.插入将被撤消删除插入的行。通过插入行来撤消删除。事务日志逻辑体系结构和预写事务日志中对此进行了描述。
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.
这是高级别的解释,具体的内部细节如何发生这种情况对于非专业人员没有记录,不受检查和更改。
#2
3
Have a look at ROLLBACK TRANSACTION (Transact-SQL)
看看ROLLBACK TRANSACTION(Transact-SQL)
Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction.
将显式或隐式事务回滚到事务的开头或事务内的保存点。
#3
3
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)
就其如何做而言,事务中的所有数据修改都存储在事务日志中,如果必须回滚,还会在日志中为撤消记录保留额外的空间。每个事务日志中都有足够的信息来反转已经发生的更改,以便它可以根据需要撤消更改。 (以及在DR场景中重播它们)
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.
如果我们以一个简单的删除操作为例(因为我在这里将其解码为日志内容的一个例子),被删除的记录存储在LOP_DELETE_ROWS的事务日志条目中,并且您可以通过一些非常重要的工作来解码和演示整行是否在日志条目中。
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.
如果要回滚事务,将使用日志中保留的撤消空间,并重新插入该行。撤消空间预留的原因是为了确保事务日志在事务处理期间无法填满,使其无法完成或回滚。