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

sqlserver,快照,隔离,级别,到底,怎么,理解 · 浏览次数 : 377

小编点评

**1. 谈谈 snapshot 隔离级别** * 首先，我们应该明白 snapshot 隔离级别是指在事务开始之前对数据库进行锁，直到事务完成或抛弃时自动释放锁的隔离级别。 * 常见的 four 个隔离级别是 READ COMMITTED、READ COMMITTED WITH SNAPSHOT、SNAPSHOT 和 NONE。 * READ COMMITTED 和 READ COMMITTED WITH SNAPSHOT 是 READ COMMITTED 的扩展，在读操作结束后自动释放锁。 * SNAPSHOT 是 READ COMMITTED 的特殊情况，它在读操作结束后自动释放锁，但只有在事务成功执行完毕时。 **2. 了解 snapshot 的简单使用** * 首先，我们需要打开数据库的 `ALLOW_SNAPSHOT_ISOLATION` 开关。 * 然后，我们可以创建一个新的表并执行一些写入操作。 * 为了使用 snapshot 隔离级别，我们需要在会话开始时设置 `SET TRAN ISOLATION LEVEL SNAPSHOT` 命令。 **3. 探索 snapshot 的原理** * 我们可以使用 `DBCC PAGE()` 和 `DBCC TABLE()` 等工具观察记录的存储方式，了解记录的版本信息。 * 我们可以模拟添加锁的过程，观察锁的获取和释放过程。 * 我们可以使用 `SQL Profiler` 等工具观察加锁过程，了解加锁机制。 **4. 总结** * 开启 `ALLOW_SNAPSHOT_ISOLATION` 开关会为每条记录添加一个版本信息，浪费了一些数据页空间。 * 使用 snapshot 隔离级别可以避免 X locks 等阻塞问题，提高读写性能。 * 了解 snapshot 的原理可以帮助我们更好地使用它。

正文

一：背景

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，如果大家对整个流程有点懵的话，画个图大概就像下面这样。

对 快照级别事务 的存储原理有了一定的认识之后，接下来从锁的角度观察下为什么能避开阻塞，将二个会话重新执行下，用 SQL Profile 观察下加锁过程。

从图中可以看的非常清楚，会话1在1:240:0 记录上获取到了 X 锁，会话2 压根就没在表记录上附加任何锁，直接提取表记录的 Version Pointer 指向的 Slot，完美避开 X 锁，也就不存在锁互斥啦。。。

三：总结

从储存机制和加锁过程可以看到如下特点：

开启 ALLOW_SNAPSHOT_ISOLATION 之后，每条记录都会有一个 版本信息，浪费了大量的数据页空间。
tempdb 是一个极其宝贵的服务器级别共享空间，被所有的数据库共享，遇到高并发的情况下可能会引发大量的 闩锁 等待造成的语句阻塞，所以一定要慎用，尽可能的减轻 tempdb 的负担。