一:背景
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;
从图中看果然解决了 既要 .... 又要
的问题,既没有阻塞,也没有脏读,