第11章多版本数据原理

1、第11章 多版本数据原理除了read uncommitted隔离级别，在SQL Server 2000版本的其他隔离级别都会发生读写操作相互等待的情况，使数据库运行效率大幅降低。为了解决这种问题，从SQL Server 2005版本开始，引入了多版本数据技术，在一个事务中修改表中的数据时(主要指对表执行update或delete操作)，会把旧版本的数据临时存入tempdb数据库，此事务结束之前，其他连接的读操作可以由tempdb数据库读取这些临时数据，而不是从原数据库读取，从而使得读写操作不再相互等待。本章内容主要包括：l 数据库开启read_committed_snapshot参数后产生的多

2、版本数据l 数据库开启allow_snapshot_isolation参数后产生的多版本数据11.1 开启read_committed_snapshot参数后产生的多版本数据开启read_committed_snapshot参数后，新添加到表中的记录都会在行数据最后附加14字节的版本信息数据，用于指向记录更新后产生的旧版本数据，这些旧版本数据存储于tempdb数据库，开启read_committed_snapshot参数之前已存在的记录会在其被执行update操作时附加14字节的版本信息。11.1.1 参数开启前后行数据存储方式的变化创建测试数据库testRowVersion，并在其中创建测试

3、表t，数据库的read_committed_snapshot参数默认未开启。1 create database testRowVersion2 go 在testRowVersion数据库中创建测试表t，并添加一行测试记录。1 use testRowVersion2 create table t(a int identity, b char(5) default xxxxx)3 go在数据库的read_committed_snapshot参数开启前后，对t表各添加一行记录。1 insert into t default values2 alter database testRowVersion

4、set read_committed_snapshot on3 insert into t default values4 go为了查看t表的两行记录在数据页中的存储形式，先查看表t分配到的数据页。1 dbcc ind(testRowVersion,t,-1)2 goPageFID PagePID IAMFID IAMPID ObjectID IndexID- - - - - - 1 77 NULL NULL 0 1 55 1 77 0先使用参数2执行dbcc page命令，导出55号数据页内容，以下导出内容中的粗体部分即是表t的两行记录对应的十六进制数据。1 dbcc traceon(360

5、4)2 dbcc page(testRowVersion,1,55,2)3 go3C16C050: ?.3C16C060: 10000d00 ?.xxxxx.3C16C070: 50000d00 ?P.xxxxx.3C16C080: ?.Q.3C16DFF0: e00f2e01 0e01e400 0010b800 ?.p.OFFSET TABLE:Row - Offset1 (0x1) - 112 (0x70)0 (0x0) - 96 (0x60)再使用参数3执行dbcc page命令导出55号数据页内容，可以更清楚地看出两行记录的差别。1 dbcc page(testRowVersion,1

6、,55,3)2 goSlot 0 Offset 0x60 Length 16Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16Memory Dump 0x3D4EC060: 10000d00 ?.xxxxx.Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4a = 1Slot 0 Column 2 Offset 0x8 Length 5 Length (physical) 5b = xxxxxSlot 1 Offset 0x70 L

7、ength 30Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30Memory Dump 0x3D4EC070: 50000d00 ?P.xxxxx.: 0000?.Q.Version Information = Transaction Timestamp: 337 Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4a = 2Slot 1 Column 2 Of

8、fset 0x8 Length 5 Length (physical) 5b = xxxxx由以上导出数据可以看出两行记录存储方式的差异：l 第1行记录的行头数据第1个字节为0x10，其二进制数据为：0001 0000。l 第2行记录的行头数据第1个字节为0x50，其二进制数据为：0101 0000。l 第2行记录附加了14字节的版本信息。版本信息前8个字节表示对应的旧版本数据在tempdb数据库中的RowID，后6个字节表示修改记录操作所在事务的序号。因为当前还未产生旧版本数据，前8个字节都为0。版本信息各部分的含义请参考11.1.2节内容。11.1.2 update产生的旧版本数据及版本信

9、息为了观察到update产生的版本信息数据，下面开始一个事务，并在其中更新t表的第1行记录。1 begin tran2 update t set b=aaaaa where a=13 go重新导出55号数据页，查看其第1行记录存储形式的变化。1 dbcc page(testRowVersion,1,55,2)2 go3C16C050: ?.3C16C060: 10000d00 ?.xxxxx.3C16C070: 50000d00 ?P.xxxxx.3C16C080: ?.Q.P.3C16C090: 0d 0000c000 ?.aaaaa.3C16C0A0: 00005f00 ?._.3C16D

10、FF0: e00f2e01 0e01e400 0010b800 70008e00 ?.p.OFFSET TABLE:Row - Offset1 (0x1) - 112 (0x70)0 (0x0) - 142 (0x8e)可以看到，更新后的记录数据被重新添加到数据页的空闲区域，同时修改了slot0的偏移量数值，即第1行记录的偏移量数据指向更新后的数据所存储的位置，而不是在原数据基础上修改。再使用参数3导出55号数据页，可以更清楚地看到第1行记录被更新后，其存储格式的变化。1 dbcc page(testRowVersion,1,55,3)2 goSlot 0 Offset 0x8e Length

11、 30Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30Memory Dump 0x3D4EC08E: 50000d00 ?P.aaaaa.: c 0000?.Version Information = Transaction Timestamp: 391 Version Pointer: (file 1 page 192 currentSlotId 0)由上面导出数据的最后部分可以得知，旧版本数据在tempdb数据库的地址为(1:192:0)。14字节版本数据

12、的前8个字节表示旧版本记录在tempdb数据库中的RowID，后面4个字节表示修改记录所在的事务的序号，其各部分含义可以分解如下。表11-1 版本数据的各部分含义原十六进制数据转换前后顺序十进制数据长度表示意义c0 00 00 000xc01924B旧版本记录所在数据页01 000x112B旧版本记录所在文件号00 000x002B旧版本记录slot序号87 01 00 00 00 000x1873916B修改操作所在事务序号导出tempdb数据库的192号数据页，查看其中的旧版本数据。1 dbcc page(tempdb,1,192,2)2 go3D64C050: ?.3D64C060: 3

13、d702d5d ?&.U.v=p-.3D64C070: ?.3D64C080: d 010d0000 ?.3D64C090: 3dfe2c5d ?.v=.,.3D64C0A0: d ?.xxx3D64C0B0: ?xx.3D64C0C0: ?.3D64DFF0: a80f0000 ?.P.OFFSET TABLE:Row - Offset0 (0x0) - 96 (0x60)可以发现，上面粗体部分是表t第1行记录在修改之前在(1:55)号数据页中存储的数据，从偏移量3D64C060至3D64C0A4为此旧版本数据的行头系统信息。也可以查询动态系统视图sys.dm_tran_version_st

14、ore得到tempdb数据库中的当前多版本数据信息。1 select transaction_sequence_num, database_id, record_image_first_part2 from sys.dm_tran_version_store3 gotransaction_sequence_num database_id record_image_first_part- - - 391 5 0x10000D0000以上各种数据之间的关系可以用下图表示。启动另外一个连接，在其中查询t表数据，可以发现此时不再发生等待，而查到的结果恰是存储在tempdb数据库中的旧版本数据。1 us

15、e testRowVersion2 go1 select * from t2 goa b- - 1 xxxxx 2 xxxxx把第1个连接中的事务提交后，表t的第1行记录的存储形式并未发生变化。1 commit2 go1 dbcc page(testRowVersion,1,55,3)2 goSlot 0 Offset 0x8e Length 30Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30Memory Dump 0x3D64C08E: 50000d00

16、?P.aaaaa.: c 0000?.Version Information = Transaction Timestamp: 391 Version Pointer: (file 1 page 192 currentSlotId 0)但tempdb数据库中的旧版本数据已经失效，如果此时查询tempdb数据库中的旧版本数据，可以看到，这时不再有结果出现。1 select transaction_sequence_num, database_id, record_image_first_part2 from sys.dm_tran_version_store3 gotransaction_seq

17、uence_num database_id record_image_first_part- - -(0 行受影响)而在连接2中重新查询t表的数据，得到的是提交后的结果。1 select * from t2 goa b- - 1 aaaaa 2 xxxxx11.1.3 delete操作产生的旧版本数据及版本信息数据在testRowVersion数据库中重建t表，添加2行记录，用于测试delete操作产生的旧版本数据。1 drop table t2 create table t(a int identity, b char(5) default xxxxx)3 go1 insert into t

18、 default values2 go 2查看t表分配到的数据页。1 dbcc ind(testRowVersion,t,-1)2 goPageFID PagePID IAMFID IAMPID ObjectID IndexID- - - - - - 1 78 NULL NULL 0 1 77 1 78 0以参数2执行dbcc page命令导出77号数据页，查看t表的2行记录存储的初始状态。1 dbcc traceon(3604)2 dbcc page(testRowVersion,1,77,2)3 go3D7AC050: ?.3D7AC060: 50000d00 ?P.xxxxx.3D7AC

19、070: 4c ?.L.P.3D7AC080: 0d ?.xxxxx.3D7AC090: 00004d01 ?.M.3D7AC0A0: ?.3D7ADFE0: ?.3D7ADFF0: 7e ?.!.OFFSET TABLE:Row - Offset1 (0x1) - 126 (0x7e)0 (0x0) - 96 (0x60)再以参数3执行dbcc page命令导出77号数据页数据。1 dbcc page(testRowVersion,1,77,3)2 goSlot 0 Offset 0x60 Length 30Record Type = PRIMARY_RECORD Record Attrib

20、utes = NULL_BITMAP VERSIONING_INFORecord Size = 30Memory Dump 0x3D6AC060: 50000d00 ?P.xxxxx.: 4c 0000?.L.Version Information = Transaction Timestamp: 332 Version Pointer: NullSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4a = 1Slot 0 Column 2 Offset 0x8 Length 5 Length (physical) 5b = xxxxxS

21、lot 1 Offset 0x7e Length 30Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30Memory Dump 0x3D6AC07E: 50000d00 ?P.xxxxx.: 4d 0000?.M.Version Information = Transaction Timestamp: 333 Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4a

22、 = 2Slot 1 Column 2 Offset 0x8 Length 5 Length (physical) 5b = xxxxx可以看到，行数据中已经包含了版本信息数据，因为当前还未产生旧版本数据，其版本指针为空。开始一个事务，在其中删除t表的第2行记录。1 begin tran2 delete from t where a=23 go以参数3执行dbcc page命令，重新导出77号数据页数据，可以看到第2行记录后面出现了版本数据。1 dbcc page(testRowVersion,1,77,3)2 goSlot 0 Offset 0x60 Length 30Record Type

23、 = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30Memory Dump 0x3D9AC060: 50000d00 ?P.xxxxx.: 4c 0000?.L.Version Information = Transaction Timestamp: 332 Version Pointer: NullSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4a = 1Slot 0 Column 2 Offset 0x8 Length

24、5 Length (physical) 5b = xxxxxSlot 1 Offset 0x9c Length 15Record Type = GHOST_VERSION_RECORD Record Attributes = VERSIONING_INFO Record Size = 15Memory Dump 0x3D9AC09C: 4eb80000 007d0100 ?N.Version Information = Transaction Timestamp: 381 Version Pointer: (file 1 page 184 currentSlotId 0)DBCC 执行完毕。如

25、果 DBCC 输出了错误信息，请与系统管理员联系。如果使用参数2执行dbcc page命令，由最后的偏移量列表数据，可以看到这个未提交的delete操作把旧版本数据的信息加入了数据页的空闲空间作为表t的第2行记录，但是其中的数据并不包含行数据，在偏移量列表中未标记原数据，即原数据相当于被删除了。1 dbcc page(testRowVersion,1,77,2)2 go3C2FC050: ?.3C2FC060: 50000d00 ?P.xxxxx.3C2FC070: 4c ?.L.P.3C2FC080: 0d ?.xxxxx.3C2FC090: 00004d01 4eb80000 ?.M.N.

26、3C2FC0A0: 007d0100 ?.3C2FC0B0: ?.3C2FDFE0: ?.3C2FDFF0: 9c ?.!.OFFSET TABLE:Row - Offset1 (0x1) - 156 (0x9c)0 (0x0) - 96 (0x60)查询sys.dm_tran_version_store动态视图，观察存储在tempdb数据库中的旧版本数据。1 select transaction_sequence_num as tran_seq_num,2 database_id as db_id,3 record_image_first_part as rec_1st_part4 from

27、 sys.dm_tran_version_store5 gotran_seq_num db_id rec_1st_part- - - 381 5 0x50000DD这里的查询结果恰是表t的第2行记录删除之前在数据页中对应的数据。查看tempdb数据的184号数据页，可以看到旧版本数据存储的状态。1 dbcc page(tempdb,1,184,2)2 go3D9AC050: ?.3D9AC060: 00001e5a f 3d7d0100 ?&.c.Z./=.3D9AC070: ?.3D9AC080: f 010d0000 ?.3D9AC090: 00bc901f 0580ff03 ?.3D9A

28、C0A0: d ?.P.xxx3D9AC0B0: 004d0100 ?xx.M.3D9AC0C0: 8088af05 68ce0100 ?.h.3D9ADFF0: a80f0000 4090b205 ?.OFFSET TABLE:Row - Offset0 (0x0) - 96 (0x60)如果回滚以上删除记录的操作，则可以看到SQL Server把删除的记录又重新加入了数据页，并且是在数据页的空闲空间区域中加入。1 dbcc page(testRowVersion,1,77,2)2 go3D7AC050: ?.3D7AC060: 50000d00 ?P.xxxxx.3D7AC070: 4c

29、?.L.P.3D7AC080: 0d ?.xxxxx.3D7AC090: 00004d01 4eb80000 ?.M.N.3D7AC0A0: 007d0100 000d0002 ?.P.3D7AC0B0: ?.xxxxx.3D7AC0C0: d ?.M.3D7ADFF0: ab ?.!.OFFSET TABLE:Row - Offset1 (0x1) - 171 (0xab)0 (0x0) - 96 (0x60)注意：这里重新加入的旧版本数据来自于重做日志文件，而不是tempdb中存储的旧版本数据。如果把delete操作提交，SQL Server会如何处理呢？重新创建表t，并添加2行测试记录。

30、1 drop table t2 create table t(a int identity, b char(5) default xxxxx)3 go1 insert into t default values2 go 21 dbcc ind(testRowVersion,t,-1)2 goPageFID PagePID IAMFID IAMPID ObjectID IndexID- - - - - - 1 78 NULL NULL 0 1 77 1 78 0以参数2执行dbcc page命令，导出77号数据页，查看表t的两行记录的初始存储状态。1 dbcc traceon(3604)2 db

31、cc page(testRowVersion,1,77,2)3 go3D3BC050: ?.3D3BC060: 50000d00 ?P.xxxxx.3D3BC070: ab ?.P.3D3BC080: 0d ?.xxxxx.3D3BC090: 0000ac03 ?.3D3BC0A0: 007d0100 000d0002 ?.P.3D3BDFF0: 7e ?.!.OFFSET TABLE:Row - Offset1 (0x1) - 126 (0x7e)0 (0x0) - 96 (0x60)如果最后执行提交，则SQL Server只是把slot 1的偏移量设置为0，详细信息请参考第7章内容。1 b

32、egin tran2 delete from t where a=23 go1 commit2 go1 dbcc page(testRowVersion,1,77,2)2 go3C2FDFF0: ?.!.OFFSET TABLE:Row - Offset1 (0x1) - 0 (0x0)0 (0x0) - 96 (0x60)11.2 开启allow_snapshot_isolation参数产生的的多版本数据从SQL Server 2005版本开始，新加入了snapshot隔离级别，同时数据库增加了allow_snapshot_isolation选项参数。若要在连接中设置snapshot隔离级，

33、需要数据库开启allow_snapshot_isolation参数。设置snapshot隔离级别后，在此连接的事务中执行的查询只能查到这个事务开始之前已经提交的数据，同时允许其他连接修改这个事务中所查询的数据，而不会像serializable隔离级别那样，在事务中查到的数据甚至所查询的表不允许其他连接修改，而只能等待查询所在的事务完成后再进行。即开启数据库的allow_snapshot_isolation参数厚，在snapshot隔离级别下，不会再有写等待读的情况发生，这其中起作用的关键是多版本数据，也就是对应一行记录会有不同版本的多份数据存在。11.2.1 多版本数据产生的过程假定数据库已经

34、开启了allow_snapshot_isolation参数，而且对数据库启动了A、B、C三个连接，下面先说明多版本数据产生的过程，具体实验过程请参看后续部分。1) 连接A设置为snapshot隔离级别。2) 在连接A中开始一个事务并执行了一次查询query_A，为了方便叙述，假定其查询结果为一行，用row_A表示，row_A存储在数据页page_A中。3) 连接B对row_A进行了修改，修改结果为row_B，row_B保存在数据页page_A中，而row_A会被作为旧版本数据存入tempdb数据库，row_A在tempdb数据库中的存储地址作为其版本数据信息保存在row_B中。4) 连接B在连

35、接A中的事务结束之前提交了修改结果，即提交了修改操作所在的事务。为了保证在连接A中再次执行query_A时，查询结果不变，tempdb中存储的row_A要一直保留，而不能被覆盖，这样对于连接A中查到的一行记录，当前就有了两个版本的数据：一是存储于tempdb数据库中的row_A，一是存储于page_A中的row_B。5) 如果此时开启另外一个连接C，并设置为snapshot隔离级别，则此连接中的事务执行query_A查询的结果总为row_B。6) 连接C对row_B进行修改并提交，修改结果为row_C，row_C会保存在数据页page_A中，row_B则会存入tempdb数据库，row_B在t

36、empdb数据库中的存储地址作为其版本数据信息保存在row_C中。7) 如果此时开启另外一个连接继续上述过程，对row_A可以生成更多版本的数据。对于连接A查到的记录，这时有了三个版本，其中最新的row_C保存在原数据库的page_A，另外两个旧版本的row_B、row_A保存在tempdb数据库中，较新版本的数据同时保存了其上一个较旧版本数据的地址。在不同时刻开始的事务执行相同的query_A查询，会查到这行记录的不同版本。下面我们进行实验说明多版本数据的产生。11.2.1 allow_snapshot_isolation参数开启前后行记录的存储方式与数据库开启read_committed_

37、snapshot参数的效果相似， 在allow_snapshot_isolation参数开启后添加的记录会附带14字节的版本信息，而参数开启之前就存在的记录会在其被修改时附带上版本信息。重建testRowVersion数据库。1 drop database testRowVersion2 create database testRowVersion3 go在testRowVersion数据库中创建测试表t。1 use testRowVersion2 create table t(a int identity, b char(5) default xxxxx)3 go在开启allow_snaps

38、hot_isolation参数前后对表t各添加一行记录。1 insert into t default values2 alter database testRowVersion set allow_snapshot_isolation on3 insert into t default values4 go查看t表分配到的数据页信息。1 dbcc ind(testRowVersion,t,-1)2 goPageFID PagePID IAMFID IAMPID ObjectID IndexID- - - - - - 1 77 NULL NULL 0 1 55 1 77 0由以上结果可知，t表

39、分配到一个数据页，为(1:55)。导出55号数据页内容，查看两行记录存储形式的差别。1 dbcc traceon(3604)2 dbcc page(testRowVersion,1,55,2)3 go3C16C050: ?.3C16C060: 10000d00 ?.xxxxx.3C16C070: 50000d00 ?P.xxxxx.3C16C080: bb ?.3C16DFF0: e00f2e01 0e01e400 0010b800 ?.p.OFFSET TABLE:Row - Offset1 (0x1) - 112 (0x70)0 (0x0) - 96 (0x60)由以上导出结果，可以看到这

40、里的结果与开启read_committed_snapshot参数的效果相似，第2行记录附加了14字节的版本信息，而第1行记录未发生变化。使用参数3再次执行dbcc page命令可以更清楚地看到两行记录的差别。1 dbcc traceon(3604)2 dbcc page(testRowVersion,1,55,3)3 goSlot 0 Offset 0x60 Length 16Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16Memory Dump 0x3D64C060: 10000d00

41、?.xxxxx.Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4a = 1Slot 0 Column 2 Offset 0x8 Length 5 Length (physical) 5b = xxxxxSlot 1 Offset 0x70 Length 30Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30Memory Dump 0x3D64C070: 50000d00 ?P.xxxxx.: bb 0

42、000?.Version Information = Transaction Timestamp: 1211 Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4a = 2Slot 1 Column 2 Offset 0x8 Length 5 Length (physical) 5b = xxxxx 因为未对第2行记录进行过修改，此时还不存在与其对应的旧版本数据，其版本信息只包括事务序号，而不包括其他旧版本数据信息。11.2.2 snapshot隔离级别下产生的多版本数据为了查看snapshot

43、隔离级别下产生的多版本数据信息，对testRowVersion数据库开启4个连接，分别标记为A、B、C及D。在连接A中设置snapshot隔离级别，然后开始一个事务，在其中查询t表的第2行记录。1 set transaction isolation level snapshot2 go1 begin tran2 select * from t where a=23 goa b- - 2 xxxxx在连接B中，对表t的第2行记录执行update操作，更改其b列的值为“aaaaa”，然后把隔离级别设置为snapshot，显式开始一个事务，在其中查询t表的第2行记录。1 update t set b

44、=aaaaa where a=22 go1 set transaction isolation level snapshot2 go1 begin tran2 select * from t where a=23 goa b- - 2 aaaaa在连接C中对表t的第2行记录执行update操作，把其b列的值改为“bbbbb”，然后执行与连接B相似的过程。1 update t set b=bbbbb where a=22 go1 set transaction isolation level snapshot2 go1 begin tran2 select * from t where a=23

45、 goa b- - 2 bbbbb 在连接D中对表t的第2行记录执行update操作，把其b列的值改为“ccccc”，然后执行与连接B相似的过程。1 update t set b=ccccc where a=22 go1 set transaction isolation level snapshot2 go1 begin tran2 select * from t where a=23 goa b- - 2 ccccc如果在以上4个连接中分别执行第2次查询，则其查询结果与各自的第1次结果是相同的。也就是说，对应于t表的第2行记录，现在有4份数据，这4份数据包括B、C、D三个连接对表t的第2行记录执行的update操作分别产生的一行旧版本数据，以及最新的修改结果。不同的连接依照其事务开始的不同时刻，各自查到不同的数据。重新导出1:55数据页内容，第1行记录对应的slot 0中的数据未发生变化，而slot 1保存了第2行记录修改后的结果，其中的b值