SQLSERVER 快照隔离级别到底怎么理解?

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

一背景

1. 讲故事

上一篇写完 SQLSERVER 的四个事务隔离级别到底怎么理解 之后有朋友留言问什么时候可以把 snapshot 隔离级别给补上这篇就来安排快照隔离级别看起来很魔法不过在修车之前得先看下怎么开车。

二snapshot 隔离详解

1. snapshot 之前的困境

在了解 snapshot 之前先看看没有它会存在什么样的困境还是用上一篇的 post 表做案例参考sql 如下。


CREATE TABLE post(id INT IDENTITY,content char(3))
GO
INSERT INTO dbo.post VALUES('aaa')
INSERT INTO dbo.post VALUES('bbb')
INSERT INTO dbo.post VALUES('ccc');
INSERT INTO dbo.post VALUES('ddd');
INSERT INTO dbo.post VALUES('eee');
INSERT INTO dbo.post VALUES('fff');

大家都知道 SQLSERVER 的默认隔离级别是 READ COMMITTED在下面的场景中会话2 会被会话1 阻塞。


---- 会话1 ----

BEGIN TRAN
UPDATE post SET content='zzz' WHERE id=1

---- 会话2 ----

BEGIN TRAN
SELECT * FROM post  WHERE id=1;

那如何缓解呢有一个粗暴的方法就是加 nolock 可以解决这个问题。


BEGIN TRAN
SELECT * FROM post (NOLOCK) WHERE id=1;

但加上 nolock 也不是一种完美的解决方案如果 会话1 在后续操作中 ROLLBACK 了那对 会话2 来说就是脏读那如何解决 既要....又要.... 的问题呢这就引入了 snapshot 隔离级别接下来看下怎么玩的。

2. snapshot 的简单使用

要想使用 snapshot 隔离级别需要打开数据库的 ALLOW_SNAPSHOT_ISOLATION 开关为了方便测试我们把数据库 删除重建。


DROP DATABASE MyTestDB
CREATE DATABASE MyTestDB
ALTER DATABASE MyTestDB  SET ALLOW_SNAPSHOT_ISOLATION ON
USE MyTestDB
CREATE TABLE post(id INT IDENTITY,content char(3))
GO
INSERT INTO dbo.post VALUES('aaa')
INSERT INTO dbo.post VALUES('bbb')
INSERT INTO dbo.post VALUES('ccc');
INSERT INTO dbo.post VALUES('ddd');
INSERT INTO dbo.post VALUES('eee');
INSERT INTO dbo.post VALUES('fff');

然后重新跑一下刚才的会话在会话2的执行中设置快照隔离级别参考 sql 如下


SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT * FROM post  WHERE id=1;

从图中看果然解决了 既要 .... 又要 的问题既没有阻塞也没有脏读🐂哈。。。

3. snapshot 是什么原理

要探究个明白得从底层的数据页说起了可以用 DBCC PAGE 和 DBCC PAGE 命令观察。


DBCC TRACEON(3604)
DBCC IND(MyTestDB,post,-1)
DBCC PAGE(MyTestDB,1,240,3)

从图中可以看到数据页上每一个 Slot 指向的表记录尾部会有一些空间来存储 Version Information 记录的版本信息比如上面的 事务号时间戳版本指针目前看 Version Pointer: Null 指向的是 NULL有了这些基础之后重新将刚才的两个会话开启再次观察 240号数据页。

从图中可以清晰的看到会话1已经将内存页修改成了 zzz会话2 读取的 aaa 肯定就是 3:8:0 指向的版本记录了。

有些朋友可能就有疑问了这个 3:8:0 是怎么看出来的其实就是记录中的 00000800 00000300 这一段看不习惯的话可以用 windbg 附加一下。

接下来的一个问题是 3:8:0 到底指向的是哪里如果看过 MSDN 上的说明应该知道它指向的是 TempDB 数据库接下来用 DBCC PAGE 去看下是不是我的 aaa 记录。


DBCC PAGE(tempdb,3,8,2)

输出结果如下


PAGE: (3:8)
Memory Dump @0x000000203ABF8000

000000203ABF8000:   01020000 2000fe00 00000000 00000100 00000000  .... ...............
000000203ABF8014:   00000100 07000080 451fb900 08000000 03000000  ........E...........
000000203ABF8028:   25000000 78010000 50000000 00000000 00000000  %...x...P...........
000000203ABF803C:   00000000 01000000 00000000 00000000 00000000  ....................
000000203ABF8050:   00000000 00000000 00000000 00000000 26010059  ................&..Y
000000203ABF8064:   0000008b 03000000 00010000 00000000 00050000  ....................
000000203ABF8078:   00000000 00000050 00000000 010b0000 00220000  .......P........."..
000000203ABF808C:   00815c00 00000000 00000000 00140000 0050000b  ..\..............P..
000000203ABF80A0:   00010000 00616161 02000000 00000000 00000080  .....aaa............
000000203ABF80B4:   03000000 00000000 00000000 381f0000 00000000  ............8.......
...
000000203ABF9FF4:   00000000 0b01d000 be006000                    ..........`.

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)

从右边的asc码看果然就是我的 aaa如果大家对整个流程有点懵的话画个图大概就像下面这样。