Transaction Log Logical Architecture

引用：http://msdn.microsoft.com/en-us/library/ms180892(SQL.90).aspx

　　The SQL Server 2005 transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it.

　　Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

　　Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

注解：

　　首先需要理解log record的概念，每一个log record都有一个LSN与之对应，我们可以将LSN看成是主键，用来标识每一条log record。sqlserver在修改数据页的时候，会产生相应的log record，这个record中保存有以下信息：产生这条log record的事务id，这条log record的LSN，修改前的状态，修改后的状态等内容。所以log record中保存的不是操作，而是数据的状态。每一个脏页（dirty page）至少有一个log record与之对应。并且脏页的header里面保存的是最后修改这个脏页的log record的LSN。每一个log record都会串行写到日志的逻辑结尾处，所以LSN是递增的。

实例

Query1.sql中执行一个事务

--实验目的，证明lsn是递增的。
USE master
go
--step1：查看数据库信息,获取TESTDB2的dbid为12
sp_helpdb

--step2：通过DBCC LOG(<db_id>, <formart_id>)获取log信息
dbcc log(12,3)

use TESTDB2
--step3:创建表
create table student
(
    stuid int,
    stuname nvarchar(20)
);

--step4:插入表后查看日志信息,也可以在apexlog中查看这个操作的日志.
dbcc log(12,3)--105行日志记录

--step5:查看最老事务
dbcc opentran

--step6:新建事务tran1插入记录
begin tran
insert into student values(1,'zhangsan');
insert into student values(2,'lisi');

----step7:查看最老事务
dbcc opentran

这时候使用APEXLOG查看相应的日志记录

Query2.sql中执行另外一个事务

USE TESTDB2
--step8:新建事务tran2插入记录
begin tran
insert into student values(3,'王五');

这时候使用APEXLOG查看相应的日志记录

回到Query1.sql中执行一下语句

--step9:在tran1中插入一条记录
insert into student values(4,'孙六');

这时候使用APEXLOG查看相应的日志记录

从上面的几张图可以发现，LSN是递增的，同样的一个事务可以有多个log record。

The steps to recover an operation depend on the type of log record:

• Logical operation logged 
  
  • To roll the logical operation forward, the operation is performed again.
  • To roll the logical operation back, the reverse logical operation is performed.
• Before and after image logged 
  
  • To roll the operation forward, the after image is applied.
  • To roll the operation back, the before image is applied.

Many types of operations are recorded in the transaction log. These operations include:

• The start and end of each transaction.
• Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
• Every extent and page allocation or deallocation.
• Creating or dropping a table or index.

Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.

回滚操作也会被写入到日志当中去，不过APEXLOG不记录回滚操作的日志记录。下面以一个实验证明rollback操作会被记录日志：

--实验2：验证Rollback operations are also logged
use TESTDB2
--step1:查看当前记录
select * from student;

--step2:查看当前日志,好对比待会的操作.
dbcc log(12,3)

begin tran
--step3：插入一条记录
insert into student values(4,'lisi');

--step4：发现多了两条记录,LOP_BEGIN_XACT和LOP_INSERT_ROWS
dbcc log(12,3)

--step5：查看发现记录已经被插入
select * from student;

--step6：事务回滚
rollback

--step7：查看日志记录,发现又多了两条记录,LOP_DELETE_ROWS和LOP_ABORT_XACT
dbcc log(12,3)
--实验结论：上面的LOP_DELETE_ROWS是rollback的log record,LOP_BEGIN_XACT和LOP_ABORT_XACT是事务开始跟结束的标志
----------------------------------------------------------

总结：

• insert操作对应log record中的LOP_INSERT_ROWS，其rollback操作是LOP_DELETE_ROWS；
• delete操作对应log record中的LOP_DELETE_ROWS，其rollback操作是LOP_INSERT_ROWS；
• update操作对应log record中的LOP_MODIFY_ROW，其rollback操作是LOP_MODIFY_ROW

The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to do a full recovery of the database. No part of the active log can ever be truncated.

所谓活动事务日志，就是从最老未提交事务产生的log record开始，以及在它之后产生的所有日志记录。

如上图所示，Active Log就是从MinLSN开始到逻辑日志的结尾。

本文转自xwdreamer博客园博客，原文链接：http://www.cnblogs.com/xwdreamer/archive/2012/07/13/2590424.html，如需转载请自行联系原作者