简介
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已经考虑到这个问题,他会自动管理历史行版本数据,以确保版本控制的行在不再需要时被删除。