当前位置: 首页 > news >正文

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

一:背景

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 PAGEDBCC 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 的负担。

相关文章:

  • 《SQL基础》09. 事务
  • 设计模式-第1章(简单工厂模式)
  • springboot,vue教务管理系统
  • 5.6 频率响应与阶跃响应
  • Next.js 中的 SEO
  • 初识Linux基础工具之yum vim gcc gdb git以及简单makefile文件的编写
  • Traffic Signs Recognition with 95% Accuracy using CNNKeras
  • axios中params和data的区别
  • 【QT】C++和QML使用多线程优化界面切换卡顿的方法
  • kob后端1
  • Kotlin~工厂方法、抽象工厂模式
  • C++继承(下)
  • PKI证书签发系统(2.0web版)
  • Ubuntu20.4下安装TensorFlow2.x
  • 【项目部署】Python Django部署到阿里云
  • MySQL数据库索引并没有你想的那么难之第一节
  • 数据结构与算法-单链表
  • 记一次git误操作, 合并冲突别人新增文件显示成“自己新增“绿色文件
  • Dubbo----------------------------配置信息整合SpringBoot的三种方式
  • 基于视觉的车道线识别技术在智能车导航中的应用研究
  • 电加热油锅炉工作原理_电加热导油
  • 大型电蒸汽锅炉_工业电阻炉
  • 燃气蒸汽锅炉的分类_大连生物质蒸汽锅炉
  • 天津市维修锅炉_锅炉汽化处理方法
  • 蒸汽汽锅炉厂家_延安锅炉厂家
  • 山西热水锅炉厂家_酒店热水 锅炉
  • 蒸汽锅炉生产厂家_燃油蒸汽发生器
  • 燃煤锅炉烧热水_张家口 淘汰取缔燃煤锅炉
  • 生物质锅炉_炉
  • 锅炉天然气_天燃气热风炉