SQL事务隔开等级

其实锁的本质就是数据的隔离级别 在通过控制隔离级别也能达到加锁的效果
,而且效果更好。

执行以下语句来回滚事务并切换回已提交读级别(这个查询将等待直到关闭查询窗口2):

更新丢失(Lost update)

  两个事务都同时更新一行数据,但是第二个事务却中途失败退出,导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作,因此并发事务并没有被隔离开来。

请求锁模式 请求排他锁 请求共享锁
请求排他锁
请求共享锁

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

不可重复读(Non-repeatable Reads)

  一个事务对同一行数据重复读取两次,但是却得到了不同的结果。它包括以下情况:

  (1) 事务T1读取某一数据后,事务T2对其做了修改,当事务T1再次读该数据时得到与前一次不同的值。

  (2) 幻读(Phantom
Reads)
:事务在操作过程中进行两次查询,第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据(这里并不要求两次查询的SQL语句相同)。这是因为在两次查询过程中有另外一个事务插入数据造成的。

 

  为了避免上面出现的几种情况,在标准SQL规范中,定义了4个事务隔离级别,不同的隔离级别对事务的处理不同。

  ● 未授权读取(读未提交)(Read
Uncommitted):允许脏读取,但不允许更新丢失。如果一个事务已经开始写数据,则另外一个数据则不允许同时进行写操作,但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

  ● 授权读取(读提交)(Read
Committed):允许不可重复读取,但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行。

  ● 可重复读取(Repeatable
Read):禁止不可重复读取和脏读取,但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。

  ● 序列化(Serializable):提供严格的事务隔离。它要求事务序列化执行,事务只能一个接着一个地执行,但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

  隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read
Committed,它能够避免脏读取,而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题,在可能出现这类问题的个别场合,可以由应用程序采用悲观锁或乐观锁来控制。

 

 

事务的隔离级别

SQL
Server通过在锁资源上使用不同类型的锁来隔离事务。为了开发安全的事务,定义事务内容以及应在何种情况下回滚至关重要,定义如何以及在多长时间内在事务中保持锁定也同等重要。这由隔离级别决定。应用不同的隔离级别,SQL
Server赋予开发者一种能力,让他们为每一个单独事务定义与其他事务的隔离程度。事务隔离级别的定义如下:

· 是否在读数据的时候使用锁

· 读锁持续多长时间

· 在读数据的时候使用何种类型的锁

· 读操作希望读已经被其他事务排他锁住的数据时,怎么办?在这种情况下,SQL
Server可以:

· 一直等到其他事务释放锁

· 读没有提交的数据

· 读数据最后提交后的版本

ANSI 99定义了4种事务隔离级别,SQL Server 2005能够完全支持这些级别:

· 未提交读 在读数据时不会检查或使用任何锁。因此,在这种隔离级别中可能读取到没有提交的数据。

· 已提交读 只读取提交的数据并等待其他事务释放排他锁。读数据的共享锁在读操作完成后立即释放。已提交读是SQL
Server的默认隔离级别。

· 可重复读 像已提交读级别那样读数据,但会保持共享锁直到事务结束。

· 可序列化 工作方式类似于可重复读。但它不仅会锁定受影响的数据,还会锁定这个范围。这就阻止了新数据插入查询所涉及的范围,这种情况可以导致幻像读。

 

此外,SQL
Server还有两种使用行版本控制来读取数据的事务级别(本章后文将详细检验这些隔离级别)。行版本控制允许一个事务在数据排他锁定后读取数据的最后提交版本。由于不必等待到锁释放就可进行读操作,因此查询性能得以大大增强。这两种隔离级别如下:

· 已提交读快照 它是一种提交读级别的新实现。不像一般的提交读级别,SQL
Server会读取最后提交的版本并因此不必在进行读操作时等待直到锁被释放。这个级别可以替代提交读级别。

· 快照 这种隔离使用行版本来提供事务级别的读取一致性。这意味着在一个事务中,由于读一致性可以通过行版本控制实现,因此同样的数据总是可以像在可序列化级别上一样被读取而不必为防止来自其他事务的更改而被锁定。

 

无论定义什么隔离级别,对数据的更改总是通过排他锁来锁定并直到事务结束时才释放。

很多情况下,定义正确的隔离级别并不是一个简单的决定。作为一种通用的规则,要选择在尽可能短的时间内锁住最少数据,但同时依然可以为事务提供它所需的安全程度的隔离级别。

已提交读

在SQL Server
2005中,已提交读隔离级别是建立连接时的默认隔离级别。这个级别存在两种类型:已提交读和已提交读快照隔离级别。应用哪种类型由数据库选项定义。已提交读级别会在读数据之前等待,直到阻塞锁被释放。已提交读快照级别会在数据被其他事务阻塞时使用行版本控制来读数据最后一次提交的版本。

使用已提交读级别:

BEGIN TRAN

 

SELECT

    FirstName, LastName, EmailAddress

FROM

    Person.Contact

WHERE

ContactID = 1

 

    返回EmailAddress为gustavo0@adventure-works.com的联系人Gustavo
Achong。

现在假设另一事务在事务打开状态下更改了EmailAddress。打开第二个查询窗口并执行以下批来UPDATE
EmailAddress,但不提交事务:

USE AdventureWorks;

 

BEGIN TRAN

UPDATE

    Person.Contact

SET

    EmailAddress = ‘uncommitted@email.at

WHERE

    ContactID = 1

    这个UPDATE
语句会正常运行。一行受到了影响,即使数据在这个事务还没有运行完之前已被查询窗口1中的事务读取。因为已提交读级别并不会在事务结束前保持用于SELECT语句的共享锁。共享锁会在数据读取之后立即被SQL
Server释放。需要一致读的时候这将是一个问题。我们将下面的”获取一致的可重复读操作”实现。

    现在切换到查询窗口1并尝试再次读数据:

    SELECT

        FirstName, LastName, EmailAddress

FROM

        Person.Contact

WHERE

        ContactID = 1

 

    由于SELECT语句被阻塞,因此这个查询并没有结束。SQL
Server会尝试在ContactID=
1的键上获取一个共享锁,但是由于在查询窗口2中的UPDATE语句对其有一个排他锁,因此这个操作不可能完成。虽然查询窗口2处于已提交读级别(由于您没有更改默认级别),但排他锁依然存在。这个阻塞将持续存在,因为数据更改的排他锁会一直保持直到事务结束。

切换到查询窗口2,让查询窗口1中的查询继续运行。键入并执行以下SELECT语句检查数据库中的授权和等待的锁。

可以看一个状态为WAIT的共享锁。这是查询窗口1中运行的查询。它在等待查询窗口2中的查询,后者在同样的资源上有一个排他锁。

在查询窗口2中执行一个ROLLBACK TRAN语句来回滚UPDATE语句。然后切换回查询窗口1。可以看到,查询窗口1中的查询完成了,并且其结果与以前的一样。查询窗口2中的事务结束的时候,锁被释放了,以至查询窗口1中的查询不再被阻塞。由于查询窗口2中的事务回滚,因此查询窗口1中得到的结果是原来的数据。如果查询窗口2中的事务被提交,则查询窗口1中会得到新的数据作为结果。

在查询窗口1中执行一个COMMIT TRAN语句并关闭所有的查询窗口。

可以看出,在(默认)已提交读级别中SQL
Server会等到排他锁释放之后再进行读操作,以此来获取真正的提交数据。还可以看出,共享锁会持续到数据被读取之后,而排他锁会持续到事务提交之后。在许多事务几乎同时更改数据的时候这种行为可能会造成问题。在这些情况下,由于排他锁造成的阻塞,读数据会非常慢。但在有些情况下,使用最后提交的数据版本是恰当的。在这些情况下,可以将已提交读级别更改为已提交读快照级别。

如果要在窗口1读取数据的话,可以使用这样的方法:

SELECT

    FirstName, LastName, EmailAddress

FROM

    Person.Contact WITH (NOLOCK)

WHERE

    ContactID = 1

    让它取消所有的锁机制,那么排他锁也不会影响到这句查询。

    使用NOLOCK注意:在 SQL Server 中,NOLOCK
提示将启用”未提交读”行为。在 SQL Server Mobile 中,使用 NOLOCK
提示仍会赋予”提交读”隔离级别。SQL Server Mobile
将维护数据副本,以确保可以读取数据而不需要使用共享锁帮助保护数据。

使用已提交读快照级别

激活已提交读快照级别

USE master;

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT ON

    注意:设置 READ_COMMITTED_SNAPSHOT 选项时,数据库中仅允许存在执行 ALTER
DATABASE 命令的连接。在 ALTER
DATABASE 完成之前,数据库中不允许有其他打开的连接。数据库不必处于单用户模式。

现在,执行以下代码开始一个事务并像前面一样更改EmailAddress(但要让事务处于打开状态):

USE AdventureWorks;

BEGIN TRAN

UPDATE Person.Contact

SET EmailAddress = ‘uncommitted@email.at

WHERE ContactID = 1;

打开第二个查询窗口并执行以下语句来读取ContactID
1的列Name和EmailAddress列。

    USE AdventureWorks;

BEGIN TRAN

SELECT FirstName, LastName, EmailAddress

FROM Person.Contact

WHERE ContactID = 1;

返回了联系人Gustavo
Achong的EmailAddress gustavo0@adventure-works.com,这是这一行最后提交的版本。不像没有快照的已提交读级别那样,这个查询不会被阻塞。关闭查询窗口2并切换到查询窗口1。

执行以下语句来回滚事务并切换回已提交读级别(这个查询将等待直到关闭查询窗口2):

ROLLBACK TRAN

GO

USE master;

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT OFF

重要提示 这个隔离级别可以用于减少阻塞。但要意识到这是一个数据库选项。当它发生了更改,将在数据库系统中使用已提交读级别的所有事务也会改变它们的行为。因此,只有在所有这些事务读最后提交的数据版本与读真正提交的数据版本在逻辑上同样正确的时候,使用这种级别才是明智的。

获取一致的可重复读操作

已提交读级别的一个缺点是,一个事务读取的数据在事务运行期间可能被另一个事务更改。因此,在两种已提交读级别下,不能保证一致性读。获取一致性读的意思是,在一个事务中,读取的数据始终是一样的。

1.  已提交读在读数据的时候使用共享锁,但在读操作完成后会立即释放这个锁。因此,其他事务可以更改刚被读过的数据。

2.  已提交读快照读取最后一次提交的数据版本。当它第二次读数据的时候,最后一次提交的版本可能由于第二个事务已经提交了对数据的更改而变成一个新版本。

在需要一致性读的时候(例如对于报表),可能这种不一致性会导致问题。想象一下,您的事务通过数据计算了一些商业数值。在已提交读级别中进行这种计算的时候,可能由于基础数据在事务计算过程中发生了变化而导致这些值被错误计算。为了成功地执行这个计算,可以使用快照隔离级别。它会使用行版本管理来提供数据的提交版本,但与已提交读快照不同的是,它总会提供在开始事务时最后提交的数据版本。因此,SQL
Server始终会在整个事务执行过程中获取同样的数据。

使用快照隔离级别

    快照隔离级别需要在数据库中一次性地激活。激活之后,每个连接可以在需要的时候使用它。

    USE master;

ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION ON;

    现在假设我们希望运行一些基于Sales.SalesOrderDetail表的报表,但需要一致性的读操作。执行以下语句为事务激活快照隔离级别并开始一个返回订单行合计的事务。记住OrderTotal的值。

USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

参数SNAPSHOT的含义:

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

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

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

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

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

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

    打开第二个查询窗口并更新SalesOrderDetail表以更改查询窗口1中用到的基础数据。(如果希望重复这个示例,将OrderQty的值5更改为其他数字以使以下代码能真正地更改数据库中的数据):

    USE AdventureWorks;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

AND ProductID = 777

    关闭查询窗口2,切换到查询窗口1,然后重复下面的SELECT语句。

    SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

    可以看出,由于快照隔离级别忽略了事务运行过程中数据的更改,因此结果与以前的相同。在快照级别下总会提供在事务开始时最后提交的值。

提交这个事务并执行以下代码再次重复这个查询:现在可看到,由于事务结束了,因此结果发生了变化。

COMMIT TRAN

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

    执行以下代码关闭AdventureWorks数据库的快照隔离级别:

    ALTER DATABASE AdventureWorks

SET ALLOW_SNAPSHOT_ISOLATION OFF;

避免同时发生的数据更新

如前所述,快照隔离级别并不在读操作的时候锁定数据,但能够在整个事务中提供一致性的视图。在某些情况下,有必要在整个事务的执行过程中锁定数据以避免其他事务对数据的更改。假设希望为一个订单开发票。首先需要获取数据并检查它,然后为其生成发票。在这种情况下,需要从事务起始就锁定数据以避免其他事务更改它。在这种情况下,快照隔离或者已提交读隔离级别都不是好的选择。对于这种情况,可以使用可重复读隔离级别。这个隔离级别与没有快照的已提交读级别的工作过程相似,但它会保持共享锁直至事务结束。因此,它防止了对数据的更新。

使用可重复读隔离级别

    假设希望处理OrderID为43659的订单。首先,必须选择数据。为了防止其他事务更改正在读的数据,使用可重复读隔离。

    USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

    参数REPEATABLE READ的含义:

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

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

打开第二个查询窗口并执行以下代码尝试更新SalesOrderDetail表以更改查询窗口1中要使用的基础数据:

    UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

WHERE SalesOrderID = 43659

AND ProductID = 777

    查询会等待。不像快照隔离级别,不可能更新数据,因为共享锁会保持以防止其他事务更改数据。这个锁可以通过前面用过的管理视图sys.dm_tran_locks查看。

    单击工具条上的”取消执行查询”按钮取消在查询窗口2中的查询。而执行以下INSERT语句在订单中加入一个新行项。

    INSERT INTO Sales.SalesOrderDetail

(

    SalesOrderID,

    CarrierTrackingNumber,

    OrderQty,

    ProductID,

    SpecialOfferID,

    UnitPrice,

    UnitPriceDiscount

)

VALUES(43659,’4911-403C-98′,1,758,1,874,0)

    注意,即使正处于可重复读隔离级别,这个语句也会成功执行。因为可重复读会锁定数据以阻止数据的更新,但INSERT语句向数据库中插入新数据,这是允许的。新行处于查询窗口1中事务SELECT语句的查询范围之中,所以会在事务下一次获取相同数据的时候被读取到。这称作幻像读。

    重复SELECT语句并提交这个事务,如下所示:

    SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

COMMIT TRAN

    可以观察到,新行被SELECT语句读取了,因为它处于这个语句的查询范围之内。可重复读级别会阻止现有数据被更改,但不会阻止新数据插入SELECT语句的查询范围内。

其他

    SET TRANSACTION一共有以下几种级别:

    SET TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SNAPSHOT

| SERIALIZABLE

}

[ ; ]

    上面的例子中没有提到的几种隔离级别的说明:

1. READ UNCOMMITTED

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

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

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

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

2.  SNAPSHOT 隔离级别。

1. READ COMMITTED

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

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

1.  如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF(默认设置),则数据库引擎 会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。语句完成后便会释放共享锁。

2.  如果将 READ_COMMITTED_SNAPSHOT 设置为 ON,则数据库引擎 会使用行版本控制为每个语句提供一个在事务上一致的数据快照,因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。

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

    注意:设置 READ_COMMITTED_SNAPSHOT 选项时,数据库中仅允许存在执行 ALTER
DATABASE 命令的连接。在 ALTER
DATABASE 完成之前,数据库中不允许有其他打开的连接。数据库不必处于单用户模式。

1. SERIALIZABLE

请指定下列内容:

1.  语句不能读取已由其他事务修改但尚未提交的数据。

2.  任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。

3.  在当前事务完成之前,其他事务不能使用当前事务中任何语句读取的键值插入新行。

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

需要注意的地方:

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

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

3.  在事务进行期间,可以随时将事务从一个隔离级别切换到另一个隔离级别,但有一种情况例外。即在从任一隔离级别更改到 SNAPSHOT 隔离时,不能进行上述操作。否则会导致事务失败并回滚。但是,可以将在 SNAPSHOT 隔离中启动的事务更改为任何其他隔离级别。

4.  将事务从一个隔离级别更改为另一个隔离级别之后,便会根据新级别的规则对更改后读取的资源执行保护。在更改前读取的资源将继续按照以前级别的规则受到保护。例如,如果某个事务从 READ
COMMITTED 更改为 SERIALIZABLE,则在该事务结束前,更改后所获取的共享锁将一直处于保留状态。

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

在sql  servce 中 事务是一个工作单元
可能包含查询和修改数据以及修改数据定义等多个活动
也可以显示或隐式定义事务边界

FROM Sales.SalesOrderDetail

数据库是要被广大客户所共享访问的,那么在数据库操作过程中很可能出现以下几种不确定情况。

但意向锁不会拒绝更低级别对象的锁请求  

(

脏读(Dirty Reads)

  一个事务开始读取了某行数据,但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的,因为很可能所有的操作都被回滚。

原子性(Atomicity)**:
事务是一个原子工作单元,事务中的所有修改要么提交,要么撤销。在事务提交指令记录到事务日志之前
如果系统出现了故障,重新启动时,SQL Server 会撤销所做的修改。 
如果事务中出现错误 默认会自动回滚   也可以通过 @@TRANCOUNT
检测事务事务完成 或者说是 当前环境是否在事务中 如果在就返回 1 没有就是0 

  1. READ
    UNCOMMITTED

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

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

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

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

    2. SNAPSHOT 隔离级别。

  2. READ
    COMMITTED

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

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

    1. 如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF(默认设置),则数据库引擎 会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。语句完成后便会释放共享锁。

    2. 如果将 READ_COMMITTED_SNAPSHOT 设置为 ON,则数据库引擎 会使用行版本控制为每个语句提供一个在事务上一致的数据快照,因为该数据在语句开始时就存在。不使用锁来防止其他事务更新数据。

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

        注意:设置 READ_COMMITTED_SNAPSHOT 选项时,数据库中仅允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前,数据库中不允许有其他打开的连接。数据库不必处于单用户模式。

  3. SERIALIZABLE

当你试图修改数据时
事务会请求数据资源的一个排他锁,它会一直到事务结束才会解除
期间任何其他事务请求都会被阻塞。对于单条语句事务
只要这条语句结束锁就会自动解除。对于多条语句事务
就只有当他完成所有语句执行并通过 COMMIT TRAN 或 ROLLBACK TRAN
命令时事务才会解除锁

    由于SELECT语句被阻塞,因此这个查询并没有结束。SQL Server会尝试在ContactID= 1的键上获取一个共享锁,但是由于在查询窗口2中的UPDATE语句对其有一个排他锁,因此这个操作不可能完成。虽然查询窗口2处于已提交读级别(由于您没有更改默认级别),但排他锁依然存在。这个阻塞将持续存在,因为数据更改的排他锁会一直保持直到事务结束。

为了在更高级别有效的检测是否有锁不兼容请求,并防止授予这些锁请求,这就是为什么在排他锁请求相同数据行时被阻断的原因。

  • 是否在读数据的时候使用锁

  • 读锁持续多长时间

  • 在读数据的时候使用何种类型的锁

下面是一个完整的事务示例:

SQL Server通过在锁资源上使用不同类型的锁来隔离事务。为了开发安全的事务,定义事务内容以及应在何种情况下回滚至关重要,定义如何以及在多长时间内在事务中保持锁定也同等重要。这由隔离级别决定。应用不同的隔离级别,SQL Server赋予开发者一种能力,让他们为每一个单独事务定义与其他事务的隔离程度。事务隔离级别的定义如下:

BEGIN TRAN;

  DECLARE @neworderid AS INT;


BEGIN TRY
  INSERT INTO Sales.Orders
      (custid, empid, orderdate, requireddate, shippeddate, 
       shipperid, freight, shipname, shipaddress, shipcity,
       shippostalcode, shipcountry)
    VALUES
      (85, 5, '20090212', '20090301', '20090216',
       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
       N'10345', N'France');

  SET @neworderid = SCOPE_IDENTITY();

  SELECT @neworderid AS neworderid;

  INSERT INTO Sales.OrderDetails(orderid, productid, unitprice, qty, discount)
    VALUES(@neworderid, 11, 14.00, 12, 0.000),
          (@neworderid, 42, 9.80, 10, 0.000),
          (@neworderid, 72, 34.80, 5, 0.000);

COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH

    可以观察到,新行被SELECT语句读取了,因为它处于这个语句的查询范围之内。可重复读级别会阻止现有数据被更改,但不会阻止新数据插入SELECT语句的查询范围内。

持续性(Durability):数据修改在写入到数据库磁盘之前,总是先写入数据库的事务日志磁盘。提交后,指令记录在事务日志磁盘上,在尚未修改磁盘数据之前,事务是持续的,回滚也只是删除事务日志的所有修改记录。

    Person.Contact

 

在SQL Server 2005中,已提交读隔离级别是建立连接时的默认隔离级别。这个级别存在两种类型:已提交读和已提交读快照隔离级别。应用哪种类型由数据库选项定义。已提交读级别会在读数据之前等待,直到阻塞锁被释放。已提交读快照级别会在数据被其他事务阻塞时使用行版本控制来读数据最后一次提交的版本。

那么锁都可以锁些什么资源呢?

    现在切换到查询窗口1并尝试再次读数据:

隔离性(Isolation):隔离是一种控制访问数据的机制,确保事务所范围数据是在其所期望的一致性级别中的数据。在SQL
Server
中支持两种不同的模式来处理隔离:基于锁的传统模式和行版本控制(新模式)
但是默认的是 锁模式  而且当前加锁也是共享锁 ,如果数据状态不一致
读取数据就会被阻止 直到状态一致 。而改成行版本控制模式 读取就不需要等待
也不会加共享锁 在不需要及时显示的数据时 这种模式是提高并发的处理方式。
具体实现要看使用的隔离级别。

打开第二个查询窗口并执行以下代码尝试更新SalesOrderDetail表以更改查询窗口1中要使用的基础数据:

 

ContactID = 1

通过GO 在当前批执行完成时自动 提交事务   当然默认情况下SQL Server
将每个单独的语句作为一个事务 每个语句结束后SQL Server 会自动提交事务
也可通过设置会话来改变默认设置 

    INSERT INTO Sales.SalesOrderDetail

SELECT    @@TRANCOUNT

USE master;

一致性(Consistency):指数据状态, 在隔离级别中  每个级别都要是
一致性级别 只有事务保持一致性级别才能访问。在约束中 也指 主外键
书屋会转换数据库的一致性状态到另一个一致性状态 保持一致性

很多情况下,定义正确的隔离级别并不是一个简单的决定。作为一种通用的规则,要选择在尽可能短的时间内锁住最少数据,但同时依然可以为事务提供它所需的安全程度的隔离级别。

交叉的否代表不兼容 请求锁模式会被拒绝 交叉是 表示兼容 请求锁模式会被接收

)

请求锁模式 请求排他锁 请求共享锁 请求意向排他锁 请求意向共享锁
请求排它锁
请求共享锁
请求意向排他锁
请求意向共享锁

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

一个简单的显示事务    也可以是隐式事务

    USE master;

例如:获取一个行上的排他锁 
事务必须首先获取一个行所在页的意向排他锁和一个拥有该页对象的意向排他锁,
同样的 共享锁也是此步骤。

        ContactID = 1

显示定义事务 BEGIN TRAN 开始  如果要提交事务 使用 COMMIT TRAN 
 撤销事务(回滚) ROLLBACK TRAN  如下示例

    快照隔离级别需要在数据库中一次性地激活。激活之后,每个连接可以在需要的时候使用它。

锁是事务保护数据资源而获得的控制资源,防止其他事务的冲突或不兼容访问。

请指定下列内容: 

1. 语句不能读取已由其他事务修改但尚未提交的数据。 

2. 任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。 

3. 在当前事务完成之前,其他事务不能使用当前事务中任何语句读取的键值插入新行。 

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

**需要注意的地方:** 

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

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

3. 在事务进行期间,可以随时将事务从一个隔离级别切换到另一个隔离级别,但有一种情况例外。即在从任一隔离级别更改到 SNAPSHOT 隔离时,不能进行上述操作。否则会导致事务失败并回滚。但是,可以将在 SNAPSHOT 隔离中启动的事务更改为任何其他隔离级别。 

4. 将事务从一个隔离级别更改为另一个隔离级别之后,便会根据新级别的规则对更改后读取的资源执行保护。在更改前读取的资源将继续按照以前级别的规则受到保护。例如,如果某个事务从 READ COMMITTED 更改为 SERIALIZABLE,则在该事务结束前,更改后所获取的共享锁将一直处于保留状态。 

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

[本文出自
51CTO.COM技术博客](http://xu20cn.blog.51cto.com/274020/66109)

事务有4个属性-原子性,一致性,隔离性,持续性 首字母缩写为ACID

SET OrderQty = 5

有一个情况不得不说  在行锁中超过5000个时 会自动升级锁 到表锁
然后每加1250个锁都会触发默认的锁升级

   

在SQL 中默认的隔离级别 READ COMMITTED SNAPSHOT
这种隔离依靠行版本控制,而不是锁,在此模式下
读取者不需要共享锁,因为不需要等待,依赖行版本控制技术提供隔离。 
 如果一个事务在READ COMMITTED隔离级别下 修改数据行
直到事务完成,另一个事务都不能读取相同行。这种并发控制称
悲观式并发”  如果一个事务在READ COMMITTED SNAPSHOT隔离级别下
修改数据行  此时另一个事务读取相同行
会获得最后一次提交的可用状态。这种并发控制称 “乐观式并发
在乐观并发中可以很好的解决修改和展示并发问题。

BEGIN TRAN

 

切换到查询窗口2,让查询窗口1中的查询继续运行。键入并执行以下SELECT语句检查数据库中的授权和等待的锁。

锁定的资源包括RID
,键,行,页,对象,表,数据库,范围,分配单元,堆,B树。

    FirstName, LastName, EmailAddress

设置为ON 后不需要指定BEGIN TRAN 语句开始事务 但必须以COMMIT TRAN或
TOLLBACK TRAN 标记结束

如前所述,快照隔离级别并不在读操作的时候锁定数据,但能够在整个事务中提供一致性的视图。在某些情况下,有必要在整个事务的执行过程中锁定数据以避免其他事务对数据的更改。假设希望为一个订单开发票。首先需要获取数据并检查它,然后为其生成发票。在这种情况下,需要从事务起始就锁定数据以避免其他事务更改它。在这种情况下,快照隔离或者已提交读隔离级别都不是好的选择。对于这种情况,可以使用可重复读隔离级别。这个隔离级别与没有快照的已提交读级别的工作过程相似,但它会保持共享锁直至事务结束。因此,它防止了对数据的更新。

共享锁:在读取事务加锁是默认加的是共享锁  SQL读取的默认隔离级别 READ
COMMITTED  因为此隔离级别 会让事务请求读取资源时默认加上共享锁 
多个事务可以同时拥有相同数据资源共享锁。 此模式下也会因为并发造成幻读
虽然在修改数据时,无法修改锁和持续时间,但可以通过改变隔离级别
在读取数据时控制锁定的处理方式。

FROM Person.Contact

BEGIN TRAN;
INSERT INTO Sales.Orders
      (custid, empid, orderdate, requireddate, shippeddate, 
       shipperid, freight, shipname, shipaddress, shipcity,
       shippostalcode, shipcountry)
    VALUES
      (85, 5, '20090212', '20090301', '20090216',
       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
       N'10345', N'France');
commit tran

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

锁主要有两种锁模式 排他锁和共享锁

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

GO
 INSERT INTO Sales.Orders
      (custid, empid, orderdate, requireddate, shippeddate, 
       shipperid, freight, shipname, shipaddress, shipcity,
       shippostalcode, shipcountry)
    VALUES
      (85, 5, '20090212', '20090301', '20090216',
       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
       N'10345', N'France');
GO

已提交读级别的一个缺点是,一个事务读取的数据在事务运行期间可能被另一个事务更改。因此,在两种已提交读级别下,不能保证一致性读。获取一致性读的意思是,在一个事务中,读取的数据始终是一样的。

锁请求流程规则是什么呢?

   

可以通过ALTER TABLE语句设置LOCK_ESCALATION 的表选项控制锁升级。
也可以禁用。 也可以更改升级方式 比如 分区级别(
把一个表物理的组织成小单元c成为分区)   锁到此语句结束了下次讲 
如何优雅的排除并发导致的阻塞数据处理。

        Person.Contact

 

在查询窗口2中执行一个ROLLBACK TRAN语句来回滚UPDATE语句。然后切换回查询窗口1。可以看到,查询窗口1中的查询完成了,并且其结果与以前的一样。查询窗口2中的事务结束的时候,锁被释放了,以至查询窗口1中的查询不再被阻塞。由于查询窗口2中的事务回滚,因此查询窗口1中得到的结果是原来的数据。如果查询窗口2中的事务被提交,则查询窗口1中会得到新的数据作为结果。

  这种事务之间的并发处理已称为 锁兼容性

    Person.Contact

例如: 一个页上的意向锁不会阻断在该页的其他事务的排他锁 
我们可以通过一个表来细致的了解这些锁的兼容性请求。

现在假设另一事务在事务打开状态下更改了EmailAddress。打开第二个查询窗口并执行以下批来UPDATE EmailAddress,但不提交事务:

例如:一个事务持有行上锁,而另一个事务在该行所在的页或者表请求不兼容锁比如排他锁
有第一个事务的行上锁 有一个 表意向锁  这个时候请求就被拒绝了

| READ COMMITTED

排他锁:如果一个事务在修改行,直到事务完成,其他事务都不能修改相同的行。但是能不能读取相同行
取决于它的隔离级别。

SELECT

SET IMPLICIT_TRANSACTIONS ON

    UnitPriceDiscount

1.事务

    ContactID = 1

为什么要申请意向锁?

SET

2.锁

在查询窗口1中执行一个COMMIT TRAN语句并关闭所有的查询窗口。

通过这些锁得到我们最理想的并发处理 锁定所需要的内容,即受影响行数
。锁是需要内存资源和内部管理开销, 当需要锁时要考虑当前系统资源情况。

可以看出,在(默认)已提交读级别中SQL Server会等到排他锁释放之后再进行读操作,以此来获取真正的提交数据。还可以看出,共享锁会持续到数据被读取之后,而排他锁会持续到事务提交之后。在许多事务几乎同时更改数据的时候这种行为可能会造成问题。在这些情况下,由于排他锁造成的阻塞,读数据会非常慢。但在有些情况下,使用最后提交的数据版本是恰当的。在这些情况下,可以将已提交读级别更改为已提交读快照级别。

SELECT FirstName, LastName, EmailAddress

此外,SQL Server还有两种使用行版本控制来读取数据的事务级别(本章后文将详细检验这些隔离级别)。行版本控制允许一个事务在数据排他锁定后读取数据的最后提交版本。由于不必等待到锁释放就可进行读操作,因此查询性能得以大大增强。这两种隔离级别如下:

| SERIALIZABLE

WHERE ContactID = 1;

WHERE

SET ALLOW_SNAPSHOT_ISOLATION ON;

    可以看出,由于快照隔离级别忽略了事务运行过程中数据的更改,因此结果与以前的相同。在快照级别下总会提供在事务开始时最后提交的值。

    USE AdventureWorks;

   

    CarrierTrackingNumber,

1. 已提交读在读数据的时候使用共享锁,但在读操作完成后会立即释放这个锁。因此,其他事务可以更改刚被读过的数据。

参数SNAPSHOT的含义:

BEGIN TRAN

    返回EmailAddress为gustavo0@adventure-works.com的联系人Gustavo Achong。

2. 已提交读快照读取最后一次提交的数据版本。当它第二次读数据的时候,最后一次提交的版本可能由于第二个事务已经提交了对数据的更改而变成一个新版本。