大家都知道SQL Server 的默认的事务隔离级别是Read Commited。但是这个事务隔离级别有一个很大缺陷,经常导致读操作阻止写操作(readers block writers),影响数据库的性能。为此,SQL Server引入一种新的事务隔离级别:RCSI(read committed snapshot isolation)。通过下列命令可以改变事务隔离级别,
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
这个事务隔离级别跟Oracle 的Read Commited很像,但是Oracle是通过undo list构造多版本一致性的,sql server的快照事物更新的行在tempdb进行维护的,性能上有本质区别。
RCSI的最大优点是在读取数据时不会产生共享锁(S),因为实际上没有从表中读取数据,而是从 tempDB中的版本读取数据。 一 个简单的说法就是读操作不会阻塞写操作。
RCSI唯一缺点是它增加了临时数据库的使用。 临时数据库已经在 SQL Server上大量使用。 正如您所知,它用于临时表,但也用于工作表、查询排序、Always On的统计信息,以及在 SQLServer中使用 tempDB的所有其他事情。 这是一个高度使用的数据库。 所以,你要确保几件事。 第一,确保你有正确数量的文件。 SQL Server 2016及更高版本根据 CPU查询数为您执行此操作。 如果你有四个操作系统核心,你应该有四个文件。 如果你有八个或更多的查询,你应该有八个文件,你可以增加,如果你需要,但一般来说,八个就足够了。 而这种配置的原因不是任何 IO争用,而是增加了 SGAM页面的数量,即系统分配页面。 它们可供您在内存中写入。 这减轻了所有处理器试图访问相同数据页的问题,并有效地解决了并发问题。 第二,您还需要使用非常快的磁盘。 在现代,这意味着您应该在 SSD驱动器上。 如果您使用的是存储阵列,则可能需要存储管理员将temp DB卷挂接到阵列的 SSD层。第三, 您还需要确保有足够的空间存放临时数据库文件。 如果临时数据库空间不足,特别是与读提交快照隔离结合使用时,可能会导致查询失败,因为 SQLServer将无法读取该版本历史记录。
由于没有采用这些锁,数据库上的并发性会大大增加。 这也意味着可以极大地减少事务的死锁。 一 般来说,我建议在几乎所有的生产工作负载中都使用它。除以下两种情况,基本上很少在真实环境遇到。
- 如果您的代码的正确性依赖于阻塞,如果存在未提交的更改,则使用 RCSI可能不是一个好主意
- 如果应用程序依赖于读取当前提交的数据,而不是事务启动时的当前数据,则可能不是最佳选择