控制到 SQL Server 的连接发出的 Transact-SQL 语句的锁定行为和行版本控制行为

转载 2006年06月09日 10:47:00
SET TRANSACTION ISOLATION LEVEL (Transact-SQL) 

 

控制到 SQL Server 的连接发出的 Transact-SQL 语句的锁定行为和行版本控制行为。

主题链接图标Transact-SQL 语法约定

语法
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]
备注

一次只能设置一个隔离级别选项,而且设置的选项将一直对那个连接始终有效,直到显式更改该选项为止。事务中执行的所有读取操作都会在指定的隔离级别的规则下运行,除非语句的 FROM 子句中的表提示为表指定了其他锁定行为或版本控制行为。

事务隔离级别定义了可为读取操作获取的锁类型。针对 READ COMMITTED 或 REPEATABLE READ 获取的共享锁通常为行锁,尽管当读取引用了页或表中大量的行时,行锁可以升级为页锁或表锁。如果某行在被读取之后由事务进行了修改,则该事务会获取一个用于保护该行的排他锁,并且该排他锁在事务完成之前将一直保持。例如,如果 REPEATABLE READ 事务具有用于某行的共享锁,并且该事务随后修改了该行,则共享行锁便会转换为排他行锁。

当事务进行时,您可以随时将事务从一个隔离级别更改为另一个隔离级别。将事务从一个隔离级别更改为另一个隔离级别之后,便会根据新级别的规则对更改后读取的资源执行保护。更改前读取的资源将继续根据先前级别的规则进行保护,例如,一个事务由 REPEATABLE READ 更改为 SERIALIZABLE。由更改前发出的 SELECT 语句读取的行将继续受到行级、页级或表级共享锁的保护。这些锁会继续保持,直至事务结束。由 SELECT 语句在更改后读取的行将受到范围锁的保护。

该表显示事务从一个隔离级别更改为另一个隔离级别时的锁定行为。

更改前的隔离级别 更改后的隔离级别

READ UNCOMMITTED

READ UNCOMITTED:

未更改。

READ COMMITTED:

该行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置:

如果为 OFF,事务将获取共享锁,并在读取期间保留锁。

如果为 ON,事务会使用行版本控制。

SNAPSHOT:

事务必须已作为 SNAPSHOT 启动。事务将会失败,并将回滚所有更改。

REPEATABLE READ:

现在该事务将获取共享锁,并在事务期间保留锁。

SERIALIZABLE:

现在该事务将获取范围锁,并在事务期间保留锁。

READ COMMITTED

READ UNCOMITTED:

事务不再获取用于读取操作的锁。

READ COMMITTED:

未更改。

SNAPSHOT:

事务必须已作为 SNAPSHOT 启动。事务将会失败,并将回滚所有更改。

REPEATABLE READ:

现在该事务将获取共享锁,并在事务期间保留锁。

SERIALIZABLE:

现在该事务将获取范围锁,并在事务期间保留锁。

SNAPSHOT

READ UNCOMITTED:

事务不再使用行版本控制,并且不再获取用于读取操作的锁。

READ COMMITTED:

该行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置:

如果为 OFF,事务将获取共享锁,并在读取期间保留锁。

如果为 ON,事务会使用行版本控制。

SNAPSHOT:

未更改。

REPEATABLE READ:

该事务不再使用行版本控制。现在它获取了共享锁,并在事务执行期间一直保持该锁。

SERIALIZABLE:

该事务不再使用行版本控制。现在它获取了范围锁,并在事务执行期间一直保持该锁。

REPEATABLE READ

READ UNCOMITTED:

该事务在读取操作时不再获取锁。在 REPEATABLE READ 下获取的共享锁保留到事务结束。

READ COMMITTED:

该行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置:

如果为 OFF,事务将获取共享锁,并在读取期间保留这些新锁。

如果为 ON,事务会使用行版本控制。

在 REPEATABLE READ 下获取的共享锁保留到事务结束。

SNAPSHOT:

事务必须已作为 SNAPSHOT 启动。事务将会失败,并将回滚所有更改。

REPEATABLE READ:

未更改。

SERIALIZABLE:

现在该事务将获取范围锁,并在事务期间保留锁。在 REPEATABLE READ 下获取的共享锁保留到事务结束。

SERIALIZABLE

READ UNCOMITTED:

该事务在读取操作时不再获取锁。在 SERIALIZABLE 下获取的范围锁保留到事务结束。

READ COMMITTED:

该行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置:

如果为 OFF,事务将获取共享锁,并在读取期间保留这些新锁。

如果为 ON,事务会使用行版本控制。

在 SERIALIZABLE 级别下获取了范围锁,并且该锁一直保持到事务结束。

SNAPSHOT:

事务必须已作为 SNAPSHOT 启动。事务将会失败,并将回滚所有更改。

REPEATABLE READ:

现在,事务获取了共享锁,并在事务执行期间一直保持该锁。在 SERIALIZABLE 下获取的范围锁保留到事务结束。

SERIALIZABLE:

未更改。

如果在存储过程、触发器、用户定义函数或用户定义类型中发出 SET TRANSACTION ISOLATION LEVEL,则当对象返回控制时,隔离级别会重设为在调用对象时有效的级别。例如,如果在批处理中设置 REPEATABLE READ,并且该批处理调用一个将隔离级别设置为 SERIALIZABLE 的存储过程,则当该存储过程将控制返回给该批处理时,隔离级别就会恢复为 REPEATABLE READ。

当您使用 sp_bindsession 绑定两个会话时,每个会话都会保留它自身的隔离级别设置。使用 SET TRANSACTION ISOLATION LEVEL 更改某个会话的隔离级别设置时,不会影响与该会话绑定的其他任何会话的设置。

SET TRANSACTION ISOLATION LEVEL 会在执行或运行时生效,而不是在分析时生效。

对表执行的优化大容量导入操作会阻塞在下列隔离级别下运行的查询:

  • SNAPSHOT
  • READ UNCOMMITTED
  • 使用行版本控制的 READ COMMITTED
  • 反之,在这些隔离级别下运行的查询也会阻塞优化大容量导入操作。
参数
READ UNCOMMITTED

指定语句可以读取已由其他事务修改但尚未提交的行。

在 READ UNCOMMITTED 级别运行的事务,不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED 事务也不会被排他锁阻塞,排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后,可以读取未提交的修改,这种读取称为脏读。在事务结束之前,可以更改数据中的值,行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。这是隔离级别中限制最少的级别。

在 SQL Server 2005 中,您还可以使用下列任意一种方法,在保护事务不脏读未提交的数据修改的同时尽量减少锁定争用:

  • READ COMMITTED 隔离级别,并将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON。
  • SNAPSHOT 隔离级别。
READ COMMITTED

指定语句不能读取已由其他事务修改但尚未提交的数据。这样可以避免脏读。其他事务可以在当前事务的各个语句之间更改数据,从而产生不可重复读取和幻像数据。该选项是 SQL Server 的默认设置。

READ COMMITTED 的行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置:

  • 如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF(默认设置),则数据库引擎 会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。语句完成后便会释放共享锁。
  • 如果将 READ_COMMITTED_SNAPSHOT 设置为 ON,则数据库引擎 会使用行版本控制为每个语句提供一个在事务上一致的数据快照,因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。

当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时,您可以使用 READCOMMITTEDLOCK 表提示为 READ_COMMITTED 隔离级别上运行的事务中的各语句请求共享锁,而不是行版本控制。

REPEATABLE READ

指定语句不能读取已由其他事务修改但尚未提交的行,并且指定,其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。

对事务中的每个语句所读取的全部数据都设置了共享锁,并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。如果当前事务随后重试执行该语句,它会检索新行,从而产生幻读。由于共享锁一直保持到事务结束,而不是在每个语句结束时释放,所以并发级别低于默认的 READ COMMITTED 隔离级别。此选项只在必要时使用。

SNAPSHOT

指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照,因为该数据在事务开始时就存在。

除非正在恢复数据库,否则 SNAPSHOT 事务不会在读取数据时请求锁。读取数据的 SNAPSHOT 事务不会阻止其他事务写入数据。写入数据的事务也不会阻止 SNAPSHOT 事务读取数据。

在数据库恢复的回滚阶段,如果尝试读取由其他正在回滚的事务锁定的数据,则 SNAPSHOT 事务将请求一个锁。在事务完成回滚之前,SNAPSHOT 事务会一直被阻塞。当事务取得授权之后,便会立即释放锁。

必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON,才能开始一个使用 SNAPSHOT 隔离级别的事务。如果使用 SNAPSHOT 隔离级别的事务访问多个数据库中的数据,则必须在每个数据库中将 ALLOW_SNAPSHOT_ISOLATION 都设置为 ON。

不能将通过其他隔离级别开始的事务设置为 SNAPSHOT 隔离级别,否则将导致事务中止。如果一个事务在 SNAPSHOT 隔离级别开始,则可以将它更改为另一个隔离级别,然后再返回 SNAPSHOT。一个事务从执行 BEGIN TRANSACTION 语句开始。

在 SNAPSHOT 隔离级别下运行的事务可以查看由该事务所做的更改。例如,如果事务对表执行 UPDATE,然后对同一个表发出 SELECT 语句,则修改后的数据将包含在结果集中。

SERIALIZABLE

指定:

  • 语句不能读取已由其他事务修改但尚未提交的数据。
  • 任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。
  • 在当前事务完成之前,其他事务不能使用当前事务中任何语句读取的键值插入新行。

范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内。这样可以阻止其他事务更新或插入任何行,从而限定当前事务所执行的任何语句。这意味着如果再次执行事务中的任何语句,则这些语句便会读取同一组行。在事务完成之前将一直保持范围锁。这是限制最多的隔离级别,因为它锁定了键的整个范围,并在事务完成之前一直保持范围锁。因为并发级别较低,所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。

示例

以下示例为会话设置了 TRANSACTION ISOLATION LEVEL。对于每个后续 Transact-SQL 语句,SQL Server 将所有共享锁一直保持到事务结束为止。

USE AdventureWorks;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT * 
    FROM HumanResources.EmployeePayHistory;
GO
SELECT * 
    FROM HumanResources.Department;
GO
COMMIT TRANSACTION;
GO
请参阅

教程:编写 Transact-SQL 语句

第 1 课:创建数据库对象 本课将介绍如何创建数据库,在数据库中创建表,然后访问表中的数据并对其进行更改。由于本课是对使用 Transact-SQL 的简介,因此它未使用或说明为这些...
  • xinqingwuji
  • xinqingwuji
  • 2016年05月12日 11:23
  • 8568

锁住你的记录:sqlserver锁定数据库中的一行记录

今天发现一个比较有意思的帖子
  • x_wy46
  • x_wy46
  • 2014年05月27日 20:45
  • 1817

清晰理解Yii的事件和行为

编程也许真的是个技术活,每次你被概念搞的蒙头转向,无从下手的时候你也许会这么想.但这也就是一会会的事,如果你在晚上喝着茶,咬着牙,僵硬着脖子,一页一页的点百度,然后在笔记上一句话一句话的记下你看到的每...
  • ltx06
  • ltx06
  • 2016年08月29日 09:16
  • 1566

有关数据库 行 锁 的几个问题(rowlock)

有关数据库 行 锁 的几个问题(rowlock) 行锁的基本说明: SELECT au_lname FROM authors WITH (NOLOCK) 锁定提示  ...
  • evilcry2012
  • evilcry2012
  • 2016年07月01日 12:10
  • 1533

用户行为分析笔记(一)

已经有很长时间没有写工作笔记了,这期间经历了有很多的事情,换工作,买房未遂,结婚,在新的公司忙的焦头烂额……这段时间感觉自己考虑问题还是不够全面,看待问题太肤浅,独立思考的能力不够……反正自己对自己很...
  • Alex201203
  • Alex201203
  • 2016年06月18日 13:30
  • 669

设计模式之行为型模式(11种)

行为型模式 第一类:通过父类与子类的关系进行实现。第二类:两个类之间。第三类:类的状态。第四类:通过中间类 责任链模式 责任链模式说的通俗一点就是,当客户提交一个请求时,从第一个对象开始,链中收到请...
  • u012152619
  • u012152619
  • 2014年11月07日 12:04
  • 1727

AI逻辑实现-用行为树取代状态机

关注AI的朋友可能都看过赖勇浩翻译的《有限状态机时代终结的10大理由》 ,里面谈到了状态机的诸多弊端。同时在ppt(附上下载地址)中述说了行为树的诸多优点,这里就不在赘述了。更多得是想总结一下自己玩了...
  • u011639126
  • u011639126
  • 2014年12月28日 14:13
  • 8339

行为树-学习笔记(1)

概念介绍行为树,有分支节点,叶节点,是根据分支的条件最后决定是否执行叶节点的逻辑结构。简而言之,行为树原理就是从树根开始,经过分支节点的逻辑判断,最后选择出叶节点执行。 行为树一般有4种节点: 顺...
  • weixiao2015
  • weixiao2015
  • 2016年01月05日 11:49
  • 988

【SQL Server技巧篇】如何使用Transact-SQL脚本语言导入Excel表

由于公司不向普通员工开放自有系统后台数据库的接入,但有时需要处理庞大数据量或输出特殊分析报表,这时候如果用Excel就比较卡顿了,处理时间长,报表也不一定能一步到位做出来。遇到这种情况,小屌我喜欢把E...
  • yang_hua_shu
  • yang_hua_shu
  • 2015年05月22日 19:11
  • 748

理性行为理论和计划行为理论

理性行为理论(theory of reasoned action,TRA),是由美国学者Fishbei和Ajzen于1975年提出的。这个模型研究的是有意识行为意向的决定因素,实际上可用于解释任何一种...
  • lanxin0802
  • lanxin0802
  • 2017年04月13日 20:05
  • 3689
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:控制到 SQL Server 的连接发出的 Transact-SQL 语句的锁定行为和行版本控制行为
举报原因:
原因补充:

(最多只允许输入30个字)