SQL Server事务隔离级别.docx

1、SQL Server事务隔离级别XX得到SQL 事务隔离级别目的在JDBC操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别的概念。问题的提出数据库是要被广大客户所共享访问的，那么在数据库操作过程中很可能出现以下几种不确定情况。 更新丢失（Lost update）两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。 脏读（Dirty Reads）一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。 不可重复读（N

2、on-repeatable Reads）一个事务对同一行数据重复读取两次，但是却得到了不同的结果。它包括以下情况： （1） 事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。 （2） 幻读（Phantom Reads）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。 解决方案为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。 未授权读取（读未提

3、交）（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。 授权读取（读提交）（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。 可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许

4、读事务），写事务则禁止任何其他事务。 序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。 隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制

5、。事务的隔离级别 SQL Server通过在锁资源上使用不同类型的锁来隔离事务。为了开发安全的事务，定义事务内容以及应在何种情况下回滚至关重要，定义如何以及在多长时间内在事务中保持锁定也同等重要。这由隔离级别决定。应用不同的隔离级别，SQL Server赋予开发者一种能力，让他们为每一个单独事务定义与其他事务的隔离程度。事务隔离级别的定义如下： 是否在读数据的时候使用锁 读锁持续多长时间 在读数据的时候使用何种类型的锁 读操作希望读已经被其他事务排他锁住的数据时，怎么办？在这种情况下，SQL Server可以： 一直等到其他事务释放锁 读没有提交的数据 读数据最后提交后的版本 ANSI 99定

6、义了4种事务隔离级别，SQL Server 2005能够完全支持这些级别： 未提交读 在读数据时不会检查或使用任何锁。因此，在这种隔离级别中可能读取到没有提交的数据。 已提交读 只读取提交的数据并等待其他事务释放排他锁。读数据的共享锁在读操作完成后立即释放。已提交读是SQL Server的默认隔离级别。 可重复读 像已提交读级别那样读数据，但会保持共享锁直到事务结束。 可序列化 工作方式类似于可重复读。但它不仅会锁定受影响的数据，还会锁定这个范围。这就阻止了新数据插入查询所涉及的范围，这种情况可以导致幻像读。 此外，SQL Server还有两种使用行版本控制来读取数据的事务级别(本章后文将详细

7、检验这些隔离级别)。行版本控制允许一个事务在数据排他锁定后读取数据的最后提交版本。由于不必等待到锁释放就可进行读操作，因此查询性能得以大大增强。这两种隔离级别如下： 已提交读快照 它是一种提交读级别的新实现。不像一般的提交读级别，SQL Server会读取最后提交的版本并因此不必在进行读操作时等待直到锁被释放。这个级别可以替代提交读级别。 快照 这种隔离使用行版本来提供事务级别的读取一致性。这意味着在一个事务中，由于读一致性可以通过行版本控制实现，因此同样的数据总是可以像在可序列化级别上一样被读取而不必为防止来自其他事务的更改而被锁定。 无论定义什么隔离级别，对数据的更改总是通过排他锁来锁定并

8、直到事务结束时才释放。 很多情况下，定义正确的隔离级别并不是一个简单的决定。作为一种通用的规则，要选择在尽可能短的时间内锁住最少数据，但同时依然可以为事务提供它所需的安全程度的隔离级别。 已提交读 在SQL Server 2005中，已提交读隔离级别是建立连接时的默认隔离级别。这个级别存在两种类型：已提交读和已提交读快照隔离级别。应用哪种类型由数据库选项定义。已提交读级别会在读数据之前等待，直到阻塞锁被释放。已提交读快照级别会在数据被其他事务阻塞时使用行版本控制来读数据最后一次提交的版本。 使用已提交读级别： BEGIN TRAN SELECT FirstName, LastName, Ema

9、ilAddress FROM Person.Contact WHERE ContactID = 1 返回EmailAddress为gustavo0adventure-的联系人Gustavo Achong。 现在假设另一事务在事务打开状态下更改了EmailAddress。打开第二个查询窗口并执行以下批来UPDATE EmailAddress，但不提交事务： USE AdventureWorks; BEGIN TRAN UPDATE Person.Contact SET EmailAddress = uncommittedemail.at WHERE ContactID = 1 这个UPDATE

10、语句会正常运行。一行受到了影响，即使数据在这个事务还没有运行完之前已被查询窗口1中的事务读取。因为已提交读级别并不会在事务结束前保持用于SELECT语句的共享锁。共享锁会在数据读取之后立即被SQL Server释放。需要一致读的时候这将是一个问题。我们将下面的获取一致的可重复读操作实现。 现在切换到查询窗口1并尝试再次读数据： SELECT FirstName, LastName, EmailAddress FROM Person.Contact WHERE ContactID = 1 由于SELECT语句被阻塞，因此这个查询并没有结束。SQL Server会尝试在ContactID= 1的键

11、上获取一个共享锁，但是由于在查询窗口2中的UPDATE语句对其有一个排他锁，因此这个操作不可能完成。虽然查询窗口2处于已提交读级别(由于您没有更改默认级别)，但排他锁依然存在。这个阻塞将持续存在，因为数据更改的排他锁会一直保持直到事务结束。 切换到查询窗口2，让查询窗口1中的查询继续运行。键入并执行以下SELECT语句检查数据库中的授权和等待的锁。 可以看一个状态为WAIT的共享锁。这是查询窗口1中运行的查询。它在等待查询窗口2中的查询，后者在同样的资源上有一个排他锁。 在查询窗口2中执行一个ROLLBACK TRAN语句来回滚UPDATE语句。然后切换回查询窗口1。可以看到，查询窗口1中的查

12、询完成了，并且其结果与以前的一样。查询窗口2中的事务结束的时候，锁被释放了，以至查询窗口1中的查询不再被阻塞。由于查询窗口2中的事务回滚，因此查询窗口1中得到的结果是原来的数据。如果查询窗口2中的事务被提交，则查询窗口1中会得到新的数据作为结果。 在查询窗口1中执行一个COMMIT TRAN语句并关闭所有的查询窗口。 可以看出，在(默认)已提交读级别中SQL Server会等到排他锁释放之后再进行读操作，以此来获取真正的提交数据。还可以看出，共享锁会持续到数据被读取之后，而排他锁会持续到事务提交之后。在许多事务几乎同时更改数据的时候这种行为可能会造成问题。在这些情况下，由于排他锁造成的阻塞，读

13、数据会非常慢。但在有些情况下，使用最后提交的数据版本是恰当的。在这些情况下，可以将已提交读级别更改为已提交读快照级别。 如果要在窗口1读取数据的话，可以使用这样的方法： SELECT FirstName, LastName, EmailAddress FROM Person.Contact WITH (NOLOCK) WHERE ContactID = 1 让它取消所有的锁机制，那么排他锁也不会影响到这句查询。 使用NOLOCK注意：在 SQL Server 中，NOLOCK 提示将启用未提交读行为。在 SQL Server Mobile 中，使用 NOLOCK 提示仍会赋予提交读隔离级别。S

14、QL Server Mobile 将维护数据副本，以确保可以读取数据而不需要使用共享锁帮助保护数据。 使用已提交读快照级别 激活已提交读快照级别 USE master; ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON 注意：设置 READ_COMMITTED_SNAPSHOT 选项时，数据库中仅允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前，数据库中不允许有其他打开的连接。数据库不必处于单用户模式。 现在，执行以下代码开始一个事务并像前面一样更改EmailAddress

15、(但要让事务处于打开状态)： USE AdventureWorks; BEGIN TRAN UPDATE Person.Contact SET EmailAddress = uncommittedemail.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 gustavo0adventure-，这是这一行最后提交的版本。不像没有快照的已提交读级别那样，这个查询不会被阻塞。关闭查询窗口2并切换