SQL Server中的MVCC

由 ·  

0    28    1

Tags：

👉 本文共约3269个字，系统预计阅读时间或需13分钟。

本页目录 隐藏
简介
你知道 SQL Server 是如何实现 MVCC 的吗？
参考
相关文章

简介

SQL Server中的MVCC

ACID 是事务四大属性，理论上 Serializable 隔离级别是最能保证事务属性的。但在实际场景中，Serializable 隔离级别不利用高并发的访问。关系型数据库为了能在高并发下保证数据的一致性，提供了一种叫 Multi-Version Concurrency Control (MVCC)的机制。

主流的关系型数据库系统都实现了 MVCC，只是实现的方式不一样。

Oracle、SQL Server、PG默认隔离级别都是提交读，而MySQL默认隔离级别是可重复读。

你知道 SQL Server 是如何实现 MVCC 的吗？

SQL Server 数据库引擎使用锁或者行版本控制机制来确保事务的完整性，并在多个用户同时访问数据时保持数据库的一致性。锁和行版本控制可以防止用户读取未提交的数据，还可以防止多个用户尝试同时更改同一数据。如果不进行锁定或行版本控制，对数据执行的查询可能会返回数据库中尚未提交的数据，从而产生意外的结果。

下面，我们将一步步进行测试，理解 SQL Server 如何实现 MVCC 的。

们构建2个事务，按顺序从上至下执行，分别对SQL定义了编号 SQL01~SQL05，以方便我们说明，后面测试皆以此模板为参考。

事务一 事务二
begin tran
--SQL01
select * from dbo.Testwhere name = 'kk'
begin tran
--SQL02
update dbo.Testset info = '更改'where name = 'kk'
--SQL03
select from dbo.Test WITH(READPAST)where name = 'kk'
--SQL04
select from dbo.Test WITH(NOLOCK)where name = 'kk'
--SQL05
select * from dbo.Testwhere name = 'kk'

测试一：

首先是最常见的隔离级别 READ COMMITTED ，了解事务之间是如何隔离的。把事务隔离级别更改为 READ COMMITTED，并按照示例中的2个事务顺序执行。

现象：

  • SQL01开始正常执行，结果正常；
  • SQL02可正常执行，更改数据；
  • SQL03可正常执行，但无查询结果（该行被排除在外）；
  • SQL04可正常执行，结果为更新后的值；
  • SQL05发生堵塞，请求共享锁，等待事务二结束；

结论：在隔离级别 READ COMMITTED下，事务中数据的更改只有在提交的情况下才能被读取，即不可重复读。这种情况在 SQL Server 是比较常见的，很容易引起堵塞或者死锁，不利于高并发。

测试二：

关系型数据库对数据一致性的保障，还有一种隔离脚本叫可重复读（REPEATABLE READ），那我们更改为这个隔离级别会怎样呢？我们更改事务隔离级别，其他步骤与上面的测试一样。

现象：

  • SQL01开始正常执行，结果正常；
  • SQL02发生堵塞，请求排他锁，等待事务一结束；
  • SQL03可正常执行，结果为更新前的值；
  • SQL04可正常执行，结果为更新前的值；
  • SQL05可正常执行，结果为更新前的值；

结论：在隔离级别 REPEATABLE READ下，同一个事务可以重复读取了，但是其他事务的更改却被堵塞了。也就是说，REPEATABLE READ 中查询的“优先级”高，READ COMMITTED 中更改的“优先级”高。那这样的隔离级别似乎也没多大好处。

我们可以看看这两个事务的锁请求情况。

SQL Server中的MVCC

测试三：

除了四大事务隔离级别 Read uncommitted、Read committed、Repeatable read、Serializable ，SQL Server 提供了一种独特的快照隔离级别 Snapshot，以此来实现行版本控制，也就是我们常说的MVCC。该级别在读操作期间不使用共享锁。必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON，事务才能在快照隔离下运行。

现象：

  • SQL01开始正常执行，结果正常；
  • SQL02可正常执行，更改数据；
  • SQL03执行错误，此隔离级别不支持READPAST；
  • SQL04可正常执行，结果为更新后的值；
  • SQL05可正常执行，结果为更新前的值；

结论：在快照隔离级别Snapshot下，同一个事务可以重复读取，读取的是更改之前的版本值，并且也不再堵塞其他事务的更新，实现了MVCC。

我们看看事务一中获取的锁情况，发现只有一个数据库共享锁，也就是所谓的“读锁”。事务一并没有更细粒度的锁，也就不会堵塞其他事务的更改操作了。

SQL Server中的MVCC

其实，事务使用的是行版本控制而不是共享锁，tempdb 数据库必须具有足够的空间用于版本存储区。在 tempdb 已满的情况下，更新操作将停止生成版本，并继续执行，但是因为所需的特定行版本不再存在，读取操作可能会失败。这将影响诸如触发器、MARS 和联机索引的操作。只要活动事务需要访问行版本，就必须存储行版本。后台线程每隔一分钟删除一次不再需要的行版本，从而释放 tempdb 中的版本空间。

因此，在大批量数据操作时，尽量分多批事务进行处理，并及时提交/回滚事务，避免tempdb中的行版本数据过大。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

查看tempdb行版本记录大小，此时可以看到版本存储区域分配了数据页。

SQL Server中的MVCC

Snapshot 是特定的事务隔离级别，事务使用行版本控制提供事务级读取一致性。

测试四：

SQL Server 还有另一种隔离级别 read committed snapshot，是在隔离级别 READ COMMITTED中设置数据库 READ_COMMITTED_SNAPSHOT为ON时出现，READ_COMMITTED 事务使用行版本控制提供语句级读取一致性。

现象：

  • SQL01开始正常执行，结果正常；
  • SQL02可正常执行，更改数据；
  • SQL03可正常执行，结果为更新前的值；
  • SQL04可正常执行，结果为更新后的值；
  • SQL05可正常执行，结果为更新前的值；

结论：与“测试三”一样，在快照隔离级别read committed snapshot下，同一个事务可以重复读取，读取的是更改之前的版本值，并且也不再堵塞其他事务的更新，实现了MVCC。

以上我们已经测试了四种事务隔离级别，其中两种隔离级别实现了MVCC。在行版本控制中，大大提高了数据库的并发访问。

测试一SET TRANSACTION ISOLATION LEVEL READ COMMITTED
测试二SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
测试三SET TRANSACTION ISOLATION LEVEL SNAPSHOTALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON
测试四SET TRANSACTION ISOLATION LEVEL READ COMMITTEDALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON

对于大多数应用程序，建议使用行版本控制的读提交隔离（read committed snapshot）而不是快照隔离（snapshot），原因如下：

  • 读提交隔离比快照隔离消耗更少的tempdb空间。
  • 读提交隔离适用于分布式事务，而快照隔离则不然。
  • 读提交隔离适用于大多数现有应用程序，无需任何更改，仍然使用默认隔离级别read committed。行版本控制由数据库选项READ_COMMITTED_SNAPSHOT控制。
  • 读提交隔离不检查更新冲突，快照隔离容易受到更新冲突的影响。
  • 读提交隔离提供语句级读取一致性。事务中的每个语句执行时，都会获取一个新的数据快照并保持每个语句的一致性，直到语句完成执行。快照隔离提供事务级别的读取一致性

参考

https://mp.weixin.qq.com/s/scHiTCyrVFEHclsP864xNA

标签：

头像

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿，我是小麦，需要帮助随时找我哦
  • 18509239930

  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群

  • 个人微店

  • 回到顶部
返回顶部