SQLServer MVCC 行版本控制

简介

SQLServer其实从2005版本就已经支持MVCC机制了,用来优化读写并发问题。
但是似乎这一功能还未得到广泛使用。

一次项目经历

笔者之前也没太关注这块，直到遇到了实际项目问题，才想到有MVCC这个宝贝。

项目情况大概是这样的：
我们负责从客户生产库实时同步数据到灾备库，另一家公司（下文简称"A公司"）负责从灾备库做实时分析查询统计。
挺好的模式，既对生产库做了容灾，又做了读写分离，减轻了生产库压力。

但是业务上线后，噩梦也就降临。
客户反馈我们的数据同步经常有延迟，有时一延迟就是一天！
好家伙，赶紧远程调查原因，一查，原来是A公司的查询业务把表给锁住了…

A公司的查询SQL十分复杂，若干嵌套查询，耗时很长。
我们首先建议A公司优化查询SQL，无果。
后来我们建议A公司在使用select查询时，加上 "with (nolock)"关键字，避免锁表。无果…

最后找到了MVCC多版本控制机制，开启了MVCC后，世界一下清净了，我们和A公司相安无事。

SQLServer 行版本控制概述

SQLServer默认的隔离级别为：“read commited” (已提交读)
在这种隔离级别下，读写操作互斥，读操作会阻塞写操作，写操作也会阻塞读操作。

SQLServer2005引入了基于行版本控制的隔离级别。这种隔离级别允许读取者得到行的一个前面已提交的值，而不会阻塞。

开启行版本控制

SQLServer提供了两种方式行版本控制：

• Read Committed Snapshot Isolation (已提交读快照隔离，RCSI)
• Snapshot Isolation (快照隔离级别)
  这里我们只学习第一种方式，第二种方式不实用。
  因为第一种方式无需修改当前应用，而第二种方式则需要修改当前应用程序代码来适配。

开启 Read Commited Snapshot Isolation 语法：

ALTER DATABASE 数据库名 SET READ_COMMITTED_SNAPSHOT ON

注意：执行这条命令前，需要先断开数据库所有连接，否则执行该命令会一直阻塞下去。

执行过后通过如下命令查询配置是否生效:

SELECT name, is_read_committed_snapshot_on 
FROM sys.databases  
WHERE name = '数据库名'

验证开启MVCC后，读写是否阻塞

1.准备数据，建张简单表，插入一条数据

create table t1 (
	id		int,
	name	varchar(20)
)
GO

insert into t1 values(1, '111')

2.开启两个会话窗口，第一个会话写操作，第二个会话读操作。按照时间轴依次操作：

时间	事务1	事务2
1	BEGIN TRANSACTION update t1 set name = ‘222’ where id = 1	BEGIN TRANSACTION
2		select * from t1 where id = 1 –返回结果: (1, 111)
3	COMMIT
4		select * from t1 where id = 1 –返回结果: (1, 222)
5		COMMIT

在这个操作序列中，"时间=2"时，事务2的查询并没有因为事务1的写操作而阻塞，而是返回了事务1更新前的行数据：(1,111)。
如果是默认隔离级别read commited下,这条操作是会被阻塞的。
说明开启RCSI行版本控制后，并行的读写没有阻塞，且读会话读取到的是历史版本行数据，符合预期。

行版本数据存储在哪里

既然行版本控制，是通过在读写并发时，让读会话去读取历史行版本数据，从而避免阻塞等待，那么这些历史行数据，存储在什么地方？
答案是： tempdb 数据库！

那么你也一定有个疑问，行版本数据一直存储在tempdb数据库中，tempdb库不是越来越大？
不用担心，SQLServer已经考虑到这个问题，他会自动管理历史行版本数据，以确保版本控制的行在不再需要时被删除。