sqlserver事务隔离小结

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

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

· 读锁持续多长时间

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

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

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

· 读没有提交的数据

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

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

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

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

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

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

 

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

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

--例子已提交读快照
USE master;

ALTER DATABASE AdventureWorks

SET READ_COMMITTED_SNAPSHOT ON

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

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

--例子
USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT SUM(LineTotal) as OrderTotal

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

四种数据库隔离级别及解决的数据问题如下所示：

设置的语法结构：

    SET TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SNAPSHOT

| SERIALIZABLE

}

[ ; ]

1.READ-UNCOMMITTED(读取未提交内容)

由于在该隔离级别下即使事务未提交所做的修改也会对其他事务产生影响。所以该级别会出现数据脏读的发生。

脏读：一个事务读取了另一个未提交的并行事务写的数据。

锁情况：事务在读取数据的时候并未对数据加锁。在修改数据的时候只对数据增加行级共享锁（其他事务可以读取但是不可以更新）。因此，事务在读取数据的时候会读取到其他事务未提交的数据产生脏读。

问题举例：老板给员工发工资，老板开启事务，然后想员工的账户转钱10000元，事务暂不提交。此时员工开启事务，查看账户余额发现多了10000元，然后提交事务。但是老板发现转多了，于是修改为转5000元，然后提交事务。最终员工账户多5000元而不是10000元，所以之前员工看到的10000元是脏数据。

2. READ-COMMITTED（读取提交内容）

在该隔离级别下事务没有提交是不会对其他事务产生影响的，只有提交的事务才会影响其他事务。所以避免了读脏数据。但是该级别会出现两次读取数据库数据不一致的情况（不可重复读）。

不可重复读：同一个事务中重新读取前面读取过的数据，发现两次读取数据不一样（该数据已经被另一个已提交的事务修改过）。

锁情况：事务对当前被读取的数据加行级共享锁，一旦读完该行就立即释放该锁；事务在更新某数据的瞬间，必须先对数据增加行级排他锁（其他事务不可以对该数据进行任何操作），直到事务结束才释放。因此，事务在修改的工程中由于事务没有提交所以不会影响到其他事务，避免了脏读的发生。但是会出现不可重复读的情况。

问题举例：老板给员工发工资。员工开启事务，查看账户余额（此时对数据增加了行级共享锁），发现账户余额是0元，然后释放了该锁。发现老板尚未转钱。此时，老板开启事务，然后向员工的账户里面转钱一万元（事务对该数据增加了行级排他锁），在事务未提交之前员工是无法进行查看余额的操作。紧接着老板提交事务，在这之后员工可以进行查看操作。然后员工觉得不对劲又查看了一次（员工之前的事务尚未提交），发现现在余额是一万元。即员工在同一事务下两次相同查看操作得到的结果不一致。

3. REPEATABLE-READ(可重复读)

在该隔离级别下两个事务同时进行，其中一个事务修改数据不会对另一个事务造成影响，即使修改的事务已经提交也不会对另一个事务造成影响。因此，该隔离级别不会出现不可重复读的情况。但是，该级别下会出现幻读。

幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户 发现表中还有没有修改的数据行，就好象发生了幻觉一样.

锁情况：事务在读取某数据的瞬间，必须先对其加行级共享锁，直到事务结束才释放；事务在更新某数据的瞬间，必须先对其加行级排他锁，直到事务结束才释放。因此，事务在读取数据的时候其他事务是可以对该数据进行读取操作，因此避免了不可重复读的情况。但是无法避免幻读的发生。

问题举例：目前分数为90分以上的的学生有15人，事务A开启，并读取所有分数为90分以上的的学生人数（事务对该数据增加了行级共享锁），发现有15人，暂不提交事务。此时，事务B开启，并插入一条分数为99的学生记录，然后提交事务。此时，事务A再次读取90分以上的的学生，发现记录为16人，多了一个人。此时产生了幻读。

--实例（设置方法）
    USE AdventureWorks;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659

 

4.SERIERLIZED(可串行化)

该事务隔离级别最严厉，在进行查询时就会对表加上共享锁，其他事务对该表将只能进行读操作，而不能进行写操作。

锁情况：事务在读取数据时，必须先对其加表级共享锁，直到事务结束才释放；事务在更新数据时，必须先对其加表级排他锁，直到事务结束才释放。因此，事务A在查询数据的时候就对该表增加了表级共享锁，其他事务只可以对该表进行查询操作直到事务A提交。所以避免了幻读的产生。

现象：事务1正在读取A表中的记录时，则事务2也能读取A表，但不能对A表做更新、新增、删除，直到事务1结束。(因为事务一对表增加了表级共享锁，其他事务只能增加共享锁读取数据，不能进行其他任何操作）

事务1正在更新A表中的记录时，则事务2不能读取A表的任意记录，更不可能对A表做更新、新增、删除，直到事务1结束。（事务一对表增加了表级排他锁，其他事务不能对表增加共享锁或排他锁，也就无法进行任何操作）