金沙官网线上SQL Server 一致性读

我们在Oracle和MySQL数据库中已经对一致性读的概念比较熟悉了,但是在SQL Server中却鲜少提及,但SQL Server自2005版本以来其实也实现了一致性读,几乎所有关系型数据库产品的一致性读都是通过MVCC机制实现的,说白了就是修改之前先把数据存一份儿。

隔离级别定义事务处理数据读取操作的隔离程度,在SQL Server中,隔离级别只会影响读操作申请的共享锁(Shared Lock),而不会影响写操作申请的互斥锁(Exclusive Lock),隔离级别控制读操作的行为:

MVCC的意思就是Multi-Version Concurrency Control--多版本并发控制,这里的version就是指的数据的前镜像,多了一份数据自然就减少了争用,增加了并发。

  • 在读数据时是否使用共享锁,申请何种类型的锁;
  • 事务持有读锁的时间;
  • 读操作引用被其他事务更新,但尚未提交的数据行时,控制读操作的行为:
    • 被阻塞,等待其他事务释放互斥锁;
    • 获取更新之前的数据值,从tempdb中读取行版本,该行版本在事务开始时已经提交;Retrieves the committed version of the row that existed at the time the statement or transaction started.
    • 读没有提交的数据,获取更新之后的数据值;

SQL Server数据库在Read committed snapshot和snapshot隔离级别下通过MVCC机制实现了一致性读,其机制如下:

在执行写操作时,事务总是持有互斥锁,直到事务结束才释放,互斥锁不受事务隔离级别的影响。在SQL Server中,互斥锁和任意锁都不兼容,在同一时间,同一个数据行上,只能有一个事务持有互斥锁,就是说,写操作是顺序进行的,完全隔离的,不能并发执行。隔离和并发,此消彼长。

MVCC一致性读步骤:

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. 

  • A new transaction is initiated, and it is assigned a transaction sequence number.
  • 每个事务开始时被分配一个事务序列号Transaction Sequence Number(TSN)。
  • The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

  • 读事务通过数据库引擎在tempdb中检索TSN小于当前读事务TSN的行(这些行都带有各自事务的TSN信息)。

  • The Database Engine checks to see if the transaction sequence number is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.

  • 数据库引擎检查:步骤2中找到的行的TSN是否在未提交事务列表中,此列表中的未提交事务都是读事务启动时就已经处于active状态的事务。
  • The transaction reads the version of the row from tempdb that was current as of the start of the transaction. It will not see new rows inserted after the transaction was started because those sequence number values will be higher than the value of the transaction sequence number.

  • 当前事务只读取:离当前读事务TSN最近,且小于当前读事务TSN的行版本。这意味只读取最新的已提交数据。

  • The current transaction will see rows that were deleted after the transaction began, because there will be a row version in tempdb with a lower sequence number value.

  • 当前事务永远不会读取到其他事物的未提交修改,因为在tempdb中总会存在修改行的行版本(即前镜像)。

事务的隔离级别共有5个,使用SET命令修改Session-Level的隔离级别,使用DBCC UserOptions 查看当前Session的隔离级别:

需要考虑的一种情形是:

金沙官网线上 1金沙官网线上 2

在读事务开始后有其他DML事务修改、插入、删除数据并在读到数据之前就提交,那么读事务会不会读取到这些更新?

SET TRANSACTION ISOLATION LEVEL
     READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE

DBCC UserOptions

由于条件所限,还未进行测试,但是猜测如下:

View Code

不会读取到更新,因为这些行版本会在tempdb中保留一段时间(同一数据可能存在多个行版本),只要检测到这些行版本的TSN大于当前读事务的TSN那么就意味着这些行在读事务开启后经过了修改,读事务只要找到这些行版本中TSN最新的一个就可以。

一,事务的隔离级别

如果猜测正确那么也意味着tempdb中的行版本(即行的前镜像)是有保留时间的,类似于Oracle的Undo_金沙官网线上,retention。

SQL Server 数据库级别默认的事务隔离级别是Read Committed,用户不能修改Database-Level默认的隔离级别,但是,用户能够修改Session-Level默认的事务隔离级别。Session-Level默认的事务隔离级别是Read Committed,该隔离级别受到数据库选项 READ_COMMITTED_SNAPSHOT 的影响,决定Read Committed隔离级别是使用行版本控制事务的读操作,还是使用加共享锁来控制事务的读操作,在默认的Read Committed隔离级别下:

如果猜测与实践不符,那么就意味着tempdb中的行版本会在事务提交后立马消失,读事务会读取到事务开始后的一部分提交的修改。

  • 如果设置选项READ_COMMITTED_SNAPSHOT为OFF,那么事务在执行读操作时申请共享锁,阻塞其他事务的写操作;
  • 如果设置选项READ_COMMITTED_SNAPSHOT为ON,那么事务在执行读操作时使用Row Versioning,不会申请共享锁,不会阻塞其他事务的写操作;

 

在任何隔离级别下,事务在执行写操作时都申请互斥锁(exclusive lock),持有互斥锁直到事务结束,互斥锁不受隔离级别的控制;而共享锁(Shared Lock)受到隔离级别的控制,隔离级别影响Shared Lock的申请和释放:

本文主要观点来自官网博客:

  • 在 Read Uncommitted隔离级别下,读操作不会申请Shared Lock;
  • 在 Read Committed(不使用row-versioning),Repeatable Read 和 Serializable隔离级别下,都会申请Shared Lock;
  • 在 Read Committed(不使用row-versioning) 隔离级别下,在读操作执行时,申请和持有Share Lock;一旦读操作完成,释放Shared Lock;
  • 在 Repeatable Read 和 Serializable隔离级别下,事务会持有Shared Lock,直到事务结束(提交或回滚);
  • 在Serializable隔离级别下,事务会持有范围Shared Lock(Range Lock),锁定一个范围,在事务活跃期间,其他事务不允许在该范围中进行更新(Insert 或 delete)操作;

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server#how-snapshot-isolation-and-row-versioning-work 

SQL Server支持使用Row Version的隔离级别,事务的读操作只申请SCH-S 表级锁,不会申请Page 锁和Row 锁,事务的修改操作仍然申请锁

关于Read committed snapshot和snapshot隔离级别,参考http://www.cnblogs.com/leohahah/p/8464575.html

  • 当数据库选项 READ_COMMITTED_SNAPSHOT 设置为ON,Read Committed隔离级别使用Row Version提供语句级别(Statement-Level)的读一致性;
    • When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement. 
  • Snapshot隔离级别使用Row Version 提供事务级别(Transaction-Level)的读一致性。在当前事务开始时,任何读操作,都基于相同的数据库snapshot。当读取被其他事务修改的数据行时,从tempdb中获取行版本数据。使用Snapshot隔离级别时,必须设置数据库选项ALLOW_SNAPSHOT_ISOLATION为ON;
    • When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started.
  • 在snapshot 和 read committed snpshot隔离级别下,事务读取的数据都是已提交的;
  • 注意语句级别的读一致性和事务级别的读一致性是snapshot 和 read committed snpshot 最大的区别:
    • 事务级别的读一致性是指:在事务开始,到事务提交期间,该事务持有数据的一个快照。如果在该事务活动期间,其他事务更新表数据,该事务只会读取快照数据,不会读取到被其他事务更新的数据值;
    • 语句级别的读一致性是指:单个语句(single statement)看到的数据是一致性的;在当前事务活动期间,事务中的语句能够读取到被其他事务提交更新的数据值;例如,在语句stmt1执行时,事务没有提交更新,stmt1看到Reader1的值是2;当语句stmt2执行时,事务提交更新,stmt2看到Reader2的值是3;

二,使用行版本(Row Version)的隔离级别

在默认的隔离级别Read Commited下,在执行读操作时,事务申请shared lock,读写操作相互阻塞。在隔离级别Read Uncommitted下,事务不会申请shared lock,因此读操作不会阻塞写操作,但是读操作可能会读到脏数据。脏数据是指被其它尚未提交的事务修改之后的数据值,不是指更新之前的数据值。

行版本是指存储在tempdb中,含有数据行和TSN的数据。数据表的一个Data Row,可以有多个Row Version。修改操作发生时,SQL Server 创建一个Row Version,将Original Row复制到Row Version,并将当前事务的TSN也存储在Row Version中。因此,Row Version存储的是修改之前的数据值。

SQL Server 提供Snapshot隔离级别,用于读取修改之前的数据值。在Snapshot隔离级别下,事务在修改任何数据之前,先将原始数据行复制到tempdb,创建数据行的一个原始版本(Row Version),注意,SQL Server只会复制被修改的数据行,对于未修改的数据行,不会保存行版本数据。后续其他事务的读操作都去读该复制的行版本。在Snapshot隔离级别下,读写操作不会互相阻塞,使用行版本控制能够提高事务的并发性,但是有一个明显的缺点,虽然用户读到的不是脏数据,但是数据可能正在被修改,很快就要过期。如果根据这个过期的数据做数据修改,可能会产生逻辑错误。

1,启用Snapshot隔离级别

设置数据库选项 ALLOW_SNAPSHOT_ISOLATION 为 ON,没有改变Session-Level的事务隔离级别,需要修改Session-Level的事务隔离级别为SNAPSHOT,才能使用行版本数据

alter database current
set allow_snapshot_isolation on;

在使用Snapshot隔离级别时,必须将当前Session的隔离级别设置为Snapshot,只有这样,当前事务才能访问Row Versioning的数据:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

2,数据库选项READ_COMMITTED_SNAPSHOT(简称RCS)

在默认的隔离级别Read Committed下,使事务能够访问Row Versioning数据,需要将数据库选项READ_COMMITTED_SNAPSHOT设置为ON:

alter database current
set allow_snapshot_isolation on;

alter database current 
set read_committed_snapshot on;

前提是必须设置数据库选项ALLOW_SNAPSHOT_ISOLATION为ON;一旦启用RCS选项,在默认的Read Committed 隔离级别中,事务访问版本化的数据行。在RCS隔离级别下,事务有两个特性:

  • 事务使用行版本(Row version)代替加锁,读操作不会阻塞其他事务的写操作;
  • RCS隔离级别保证语句级别的事务一致性,查询语句只能读取在该语句执行时已经提交的数据,如果在该语句执行时数据更新尚未提交,该语句读取不到;

3,READ COMMITTED Snapshot隔离级别

在Read Committed 隔离级别下,事务不能读取被其他事务修改,但尚未提交的数据,即只能读取已提交更新的数据,READ COMMITTED隔离级别的行为受到数据库选项:READ_COMMITTED_SNAPSHOT的影响:

  • 如果设置RCS选项为OFF(默认设置),数据库引擎使用Shared Lock阻止其他事务修改当前事务正在读取的数据;当读取被其他事务修改,但尚未提交更新的数据行时,该读操作将被阻塞;

    • If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed.
  • 如果设置RCS选项为ON,数据库引擎使用行版本化(Row Versioning)的数据实现语句级别的一致性,不会阻塞其他事务的写操作,但只能读取已提交更新的数据

    • If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

三,启用快照隔离级别

1,使用snapshot 隔离级别

step1,设置数据库选项

本文由金沙官网线上发布于数据库,转载请注明出处:金沙官网线上SQL Server 一致性读

您可能还会对下面的文章感兴趣: