数据库隔离等级

　　数据库隔离等级决定并发时用户读和写的行为，非常重要。用户用SELECT读取数据时会默认使用共享锁（shared lock ），修改数据时会使用排他锁（exclusive lock）。我们不能控制写数据时请求的锁和锁数据持续的时间，但是幸运的是，我们可以控制读数据时的行为，从而来影响写数据的行为。

　　从SQL SERVER 2005开始，有6个隔离等级，分别是READ UNCOMMITTED, READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE, SNAPSHOT和READ COMMITTED SNAPSHOT，其中，最后两个是2005新增的隔离等级。

　　设置隔离等级的语法大致如下：

　　SET TRANSACTION ISOLATION LEVEL <isolation name>。

　　SELECT ... FROM <table> WITH (<isolationname>);

　　第二种语法注意是不能有空格的，比如WITH (REPEATABLEREAD)，还有特定于这种语法使用的：NOLOCK=READUNCOMMITTED; HOLDLOCK=REPEATABLEREAD。

　　所有隔离等级从本质上来说是通过控制共享锁来达到各自的目的。

 

　　READ UNCOMMITTED

　　最低的一个隔离等级。在读取数据时不会请求共享锁。这样，就可以直接读取被排他锁锁住的数据，并发性最好。

　　这个等级会造成脏读，举例如下：

　　输入下面的脚本：

　　

BEGIN TRAN;

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

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

　　

　　如上所示，事物还未关闭，unitprice已经被更新为20，这时再打开一个连接，输入下面的脚本：

　　

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

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

　　

　　首先把隔离等级设为READ UNCOMMITTED，然后查询数据，查出来的值为20。

　　这时，假如对第一个连接中的事务进行回滚：

　　

ROLLBACK TRAN;

 

　　更新动作被取消，数据库中的实际值还是19，连接2里面读到的值就是脏读的结果。

 

　　READ COMMITTED

　　避免脏读的最低隔离等级，也是SQL SERVER数据库的默认隔离等级。

　　在这个等级中，只会读取事务已提交的数据，不会读取未提交的数据。在读取数据时会先请求在数据上加共享锁，如果有操作在更新这些数据，那么这些数据上已经有排它锁，读取必须等待排它锁释放后才能加共享锁，这样就达到了避免脏读的目的。

　　输入下面的脚本：

　　

BEGIN TRAN;

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

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

 

　　在另一个连接中输入：

　　

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

 

　　由于第一个连接的事物还未提交或回滚，所以第二个连接必须等待，直到排它锁释放。

　　SELECT语句在数据上加共享锁，查完数据后就会释放锁，即使在一个事务未提交或回滚的情况下，也是如此。

 

　　REPEATABLE READ

　　这个隔离等级可以解决不可重复读的问题。

　　可重复读可以考虑这样的场景，在一个事务中，从数据库读取了一个值，用于后续的判断，但是在判断之前，别的事务就把这个数据给更新成别的值，这样第一个事务中的值就不能在重复读取到了。

　　解决的本质是在一个事务中，当读取了数据后，并不立马释放共享锁，直到事务结束，这样可以阻止不是本事务的操作来影响数据。

　　输入脚本如下：

　　

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN;

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

 

　　首先将隔离等级设为REPEATABLE READ，然后开启事务，读取了一个值。接着，在另一个连接中：

　　

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

 

　　会发现，第二个连接的UPDATE动作被阻塞，因为第一个连接在数据上加的共享锁还未解除。　　　　　　　　　　　　　　　　　　

　　另外能避免的一个问题是丢失更新。场景是当两个事务都需要读取同一个值，然后经过业务计算来更新值。在REPEATABLE READ之下的隔离等级，最后完成的事务会赢得这场胜利。在REPEATABLE READ下，这种场景将导致死锁，从而避免了丢失更新的问题，当然，死锁也是我们要设法避免的。

 

　　SERIALIZABLE

　　堪称堡垒级别的隔离。

　　REPEATABLE READ不能避免的是幻读（phantom）。因为只会在查询的数据上加锁，如果这是有别的事务INSERT了一条新的记录，这样，在事务中做第二次读取时就会读到这个新的行。

　　SERIALIZABLE在REPEATABLE READ保留共享锁到事务结束的基础上，还会锁定读取的结果集，从而达到避免幻读的目的。

　　举例：

　　

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRAN

SELECT productid, productname, categoryid, unitprice
FROM Production.Products
WHERE categoryid = 1;

 

　　第二个连接：

　　

INSERT INTO Production.Products
(productname, supplierid, categoryid,
unitprice, discontinued)
VALUES('Product ABCDE', 1, 1, 20.00, 0);

 

　　可以看到，第二个连接执行的INSERT被阻塞，直到第一个连接中的事务完成才能新增数据。

 

　　SNAPSHOT

　　SQL SERVER 2005引进了把事务提交前的数据版本保存到tempdb的能力。基于这个能力，新增了两个隔离等级。

　　要开启这个，需要执行下面的语句：

　　

ALTER DATABASE XXX SET ALLOW_SNAPSHOT_ISOLATION ON;

　　

　　第一个连接：

　　

BEGIN TRAN;

UPDATE Production.Products
SET unitprice = unitprice + 1.00
WHERE productid = 2;
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;

 

　　第二个连接：

 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

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

　　第二个连接都到的值就是已提交的最后一个版本，也就是19。即使第一个连接中的事务提交，只要第二个连接中还是这个事务中去做查询，读到的值始终是19，而不会是20。如果在第二个连接中开启一个新的事务，那么读到的值才是20。原来tempdb中的19也就不再需要了，会有一个一分钟启动一次的清理线程从tempdb中清理掉这个过时版本。

　　SNAPSHOT能避免更新冲突，比如在第一个连接中取unitprice的值，是19，然后更新unitprice为20，如果没有别的事务来搀和，这个是成功的。现在假设在第一个连接读取了unitprice值后，由另外一个连接对unitprice做了更新，然后第一个连接去更新unitprice值，报：

 

Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot
isolation to access table 'Production.Products' directly or indirectly in database
'TSQLFundamentals2008' to update, delete, or insert the row that has been modified or
deleted by another transaction. Retry the transaction or change the isolation level for the
update/delete statement.

　　READ COMMITTED SNAPSHOT

　　也是基于数据版本。和上面一种的区别是，它会取到最后提交的版本。

　

ALTER DATABASE XXX SET READ_COMMITTED_SNAPSHOT ON;

 

　　这个标志开启后，数据库默认的隔离等级就是READ_COMMITTED_SNAPSHOT。

　　第一个连接：

 

BEGIN TRAN;

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

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

　　在这个连接中，值为20，但是如果开启第二个连接，查询unitprice的值，还是19。

　　这时我们把第一个连接的事务做COMMIT，接着在第二个连接中再次查询unitprice的值，这就是和SNAPSHOT的区别了，是20。

　　

　　最后是一张总图：

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