浅谈Sql Server中的 隔离层级（Isolation Levels）

mr923

浅谈Sql Server中的 隔离层级（Isolation Levels）

隔离层级决定（影响）用户（广义用户）并发读写时的行为及其结果。读是一般指Select 语句，在默认情况下它使用共享锁。写是指使数据库内容发生改变的一切语句（Insert, Update, Delete 等），它需要排他锁。通常我们不能控制写的时候是否加排他锁，但是我们可以指定读的时候是否加共享锁，当然通过是否加共享锁可以决定（影响）读的结果，有时也可以影响到写的结果。
现有的隔离层级有：READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE, SNAPSHOT, and READ COMMITTED SNAPSHOT。　后面两种只能在Sql2005 (含)以后的版本才能使用。可以使用SET TRANSACTION ISOLATION LEVEL <isolation name>; 或SELECT ... FROM <table> WITH (<isolationname>); 来设置当前连接的隔离层级或当前查询的隔离层级，最后两种需要用下列语句来设置开关后才能使用：
ALTER DATABASE DataBaseName SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE DataBaseName SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE DataBaseName SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE DataBaseName SET READ_COMMITTED_SNAPSHOT OFF;
以下我们将逐一介绍以上六个隔离层级。
1. READ UNCOMMITTED Isolation Level

这个隔离层级是最低要求的隔离层级，使用这个隔离层级读取数据时不请求共享锁，读操作不加共享锁就不会跟任何写操作冲突，但是这样容易读到脏数据。下面通过一个小实验来说明下：
a. 打开一个Sql 查询窗口并执行，即窗口1
　BEGIN TRAN;
UPDATE dbo.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;

SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;

结果为：
productid unitprice
----------- ---------------------
2 20.00

b. 打开另一个Sql查询窗口并执行，即窗口2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;
结果为：
productid unitprice
----------- ---------------------
2 20.00
但是这里的结果是脏数据，因为窗口1中的事务还没有提交，很可能rollback

c. 回到窗口１中执行　 ROLLBACK TRAN;再查询该产品时它的单价是19.00

2. READ COMMITTED Isolation Level
如果你不想读到脏数据，那么你需要更高级别的隔离层级，防止读到脏数据的的最低隔离层级就是READ COMMITTED ，它也是所有版本的Sql Server读操作时的默认隔离层级.顾名思义，这个层级只允许读取已经提交过的数据。这就是说如果更新一行的事务还没有结束时试图读取该行的操作将被Block（只能等待更新事务结束unblock）, 因为这个层级的读操作要求先加共享锁，而在有排它锁锁定该行的情况下是没法加共享锁的。当更新事务结束后排它锁被释放，该行接受读操作所请求的共享锁，读取该行，然后释放共享锁。下面同样通过一个小实验来说明：
a. 打开一个Sql 查询窗口并执行，即窗口1
BEGIN TRAN;

UPDATE dbo.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;

SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;
结果为：

productid unitprice

----------- ---------------------

2 20.00
b. 打开另一个Sql查询窗口并执行，即窗口2
　　SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;
　　　　　　　可以看到窗口2中的select一直没有执行完，其实它是被block了
c. 再回到窗口1执行　COMMIT TRAN;
d. 切换到窗口2你会发现被block的读操作也执行完成了，且输出了如下结果

　productid unitprice

----------- ---------------------

2 20.00
为了下面的实验方便我们把改产品的单价改回19.00

3. REPEATABLE READ Isolation Level
如果你想确保数据在你读取数据的过程中不会发生任何变化，那么又需要提升隔离层级到REPEATABLE READ。这个层级并不仅仅在读取数据时加共享锁，它会将这个锁保持到对应的事务结束。在整个事务期间该数据都是被保护的，不允许写操作。下面我们来做个小实验来说明这个层级的应用：
a. 打开一个Sql 查询窗口并执行，即窗口1
　　SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN;
SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;

b. 打开另一个Sql查询窗口并执行，即窗口2
　　　UPDATE dbo.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;

SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;
　　　　　　你会发现窗口2中的代码一直没有执行完成，被窗口1block
c. 回到窗口1并执行
　　　　SELECT productid, unitprice
FROM dbo.Products
WHERE productid = 2;

COMMIT TRAN;
　　　　　　　　第一个窗口中第一个查询和第二个查询的结果是一致的，说明该行在整个事务中是没有被修改的。
d. 切换回窗口2,可以发现更新语句完成了，更新后查询语句的的结果单价为20.00,为了下面的实验方便我们把改产品的单价改回19.00

4. SERIALIZABLE Isolation Level
REPEATABLE READ　可以确保productid为２的产品不被修改，但是如果想确保符合select条件的数据在读取事务的过程中不被插入，那么隔离层级就要提升至SERIALIZABLE了：
a. 打开一个Sql 查询窗口并执行，即窗口1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

SELECT productid, productname, categoryid, unitprice
FROM dbo.Products
WHERE categoryid = 1;
查出12条记录。
b. 打开一个Sql 查询窗口并执行，即窗口2
　　　INSERT INTO dbo.Products
(productname, supplierid, categoryid,
unitprice, discontinued)
VALUES('Product ABCDE', 1, 1, 20.00, 0);

SELECT productid, productname, categoryid, unitprice
FROM dbo.Products
WHERE categoryid = 1;
　　　　　　你会发现窗口2中的代码一直没有执行完成，被窗口1block　　　　　　　　　　
c. 切换回窗口1并执行以下Sql
SELECT productid, productname, categoryid, unitprice
FROM dbo.Products
WHERE categoryid = 1;
COMMIT TRAN;
查出的结果还是12条记录
d. 　切换回窗口2发现插入成功，插入后返回13条记录，为了下面的实验删除插入的新纪录

5. Snapshot Isolation Levels（SNAPSHOT 和READ COMMITTED SNAPSHOT）　　　　　
Sql server 2005开始兼容将记录的前一个版本存入tempdb的模式，基于记录版本技术，Sql server增加了对SNAPSHOT 和READ COMMITTED SNAPSHOT的支持。SNAPSHOT功能和SERIALIZABLE　类似，READ COMMITTED SNAPSHOT和READ COMMITTED类似。然而读操作时使用基于snapshot的隔离层级不需要加共享锁，它不会和请求排他锁的写操作冲突，但是它的缺点是需要将前一个版本存入tempdb, 这样会影响读写性能。这两种隔离层级这里就不举例说明了。

以上Sql在微软实例数据库Northwind中验证通过。Northwind可以从网上下载，也可以到http://download.csdn.net/source/2875718　下载建库脚本。

Isolation Level	Uncommitted Reads?	Non Repeatable Reads?	Lost Updates?	Phantom Reads?	Detects Update Conflicts?	Uses Row Versioning?
READ UNCOMMITTED	Yes	Yes	Yes	Yes	No	No
READ COMMITTED	No	Yes	Yes	Yes	No	No
READ COMMITTED SNAPSHOT	No	Yes	Yes	Yes	No	Yes
REPEATABLE READ	No	No	No	Yes	No	No
SERIALIZABLE	No	No	No	No	No	No
SNAPSHOT	No	No	No	No	Yes	Yes