SQL Server并发事务中的锁定

Sqlserver中的事务具有原子性(Atomicity)，一致性(Consistemcy)，隔离性(Isolation)，持久性(Durability)等特性。事务存在隔离级别的概念，有如下可设置的级别：

　　READ UNCOMMITTED

　　READ COMMITTED（默认）

　　REPEATABLE READ

　　SERIALIZABLE

使用如下指令DBCC Useroptions可查看当前的事务隔离级别：

Set Option	Value
isolation level	read committed

 1：READ COMMITTED

参考MSDN中IsolationLevel 枚举的说明：

ReadCommitted

Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.

先做一个测试：随意创建一个测试表,开启一个事务，插入一笔测试数据，不提交事务；

BEGIN TRAN TEST_TRAN1

INSERT INTO [NKey_T] 
VALUES('0009',1,0)
--ROLLBACK TRAN TEST_TRAN1
 

上面的NKey_T有一个名为Id的自增列；

新开一个查询窗口：执行查询：

SELECT TOP 1   *
FROM [NKey_T] 
WHERE FileId= 1 
ORDER BY Id DESC

此时 因为前一个事务中的Insert新增数据，且未提交或回滚，导致第2个查询语句阻塞，直到test_tran1结束。注意test_tran1中的insert 获取了独占锁，但只对受影响的新增数据起作用，

如果select top n查询不加order by 且 top n 查询不到新增的最后一笔数据，则查询不会阻塞。

 因为SQLServer 默认的隔离级别是读取提交后的数据（READ COMMITTED）,此时再对查询加一个改变：

SELECT TOP 1   *
FROM [NKey_T] WITH(NOLOCK)
WHERE FileId= 1 
ORDER BY Id DESC

注意在查询表后添加了 with(nolock)，其查询效果相当于 读取未提交数据READ UNCOMMITTED；此时即使此例子中的TEST_TRAN1事务未提交，也能把插入的0009查询出来:

这个就是“脏读”的理解：查询读取到了未提交事务中执行的数据，如果未提交事务回滚，此时的查询结果非真实的数据。

ReadUncommitted

A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored. 脏读是可能的，这意味着不会发出共享锁，也不会使用独占锁。

基于以上理解：在做类似先查询后更新状态的业务应用中，需要使用到独占锁，来保障并发执行每次都可以查询到更新状态之后的数据，例如：并发取数据库前N条记录某个状态下的数据（发送邮件，任务等）。而获取独占锁的方式是：只有发生了Insert,Update,Delete等操作，就会获取操作资源数据的独占锁。并发执行的任务只能依次等待锁释放后执行。

2： REPEATABLE READ

RepeatableRead

Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.

字面理解就是：可重复读，其目标是保障每次查询的结果一致。

Read committed级别会导致不可重复读或幻读（resulting in non-repeatable reads or phantom data.）

先看下面的例子：开启一个事务，先查询Id=4的记录，延时几秒后再次查询id=4的记录。 

--read committed
BEGIN TRAN
SELECT   *
FROM [NKey_T]
WHERE ID=4

WAITFOR DELAY '00:00:05'

SELECT   *
FROM [NKey_T]
WHERE ID=4

COMMIT TRAN

在并发读写数据库中，如果此时执行了Update 操作：例如再开一个查询窗口执行下面的update:

UPDATE NKey_T SET NKey='X004'  WHERE Id=4 

切回到前一个事务的执行结果：可以看到两次查询没有得到一致的结果：造成了“不可重复读取”的现象：一个事务对同一个查询的两次读取，到了不同的结果。

 

 测试发现执行前后结果都不同，试想：这样的结果若发生在生产环境中，应该是非常可怕的。

此时设置下事务的隔离级别：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 再分别执行上述过程。则可得到一致结果，update 语句会阻塞，直到前一个事务完成，否则一直等待或超时。

注意：Repeatable read 解决了不可重复读，但幻读仍存在：(but phantom rows are still possible )。例如：一个事务做Select，另一个事务Insert，这时查询事务第一次的查询结果和第二次的查询结果不一致，结果集会多或少记录的现象。

测试如下：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT   *
FROM [NKey_T]
WHERE FileId=1

WAITFOR DELAY '00:00:06'

SELECT   *
FROM [NKey_T]
WHERE  FileId=1

COMMIT TRAN

 开启新的查询回话：在延时时间6秒内执行

INSERT INTO NKEY_T
VALUES('00005',1,0) 结论：幻读在此隔离级别下仍存在；

3：Serializable

Serializable

A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

 此隔离级别最高，可以防止脏读、幻读、不可重复读发生，但并发支持最低。

此时一个事务中查询操作，另外一个事务的update ,insert 操作需要等待前一个事务的完成，否则阻塞等待。

Serializable级别的隔离并不是对所影响的行起作用，试验如下：

事务1：

SET TRANSACTION ISOLATION LEVEL Serializable
BEGIN TRAN
SELECT   *
FROM [NKey_T]
WHERE FileId=6

WAITFOR DELAY '00:00:07'

SELECT   *
FROM [NKey_T]
WHERE  FileId=6

COMMIT TRAN

事务2：

insert NKey_T values('7005',8,0)
select  * from NKey_T
where FileId=8

事务1查询FileId=6的条件记录，事务2插入FileId=8的记录，事务1先执行后，观察事务2的执行，发现事务2被阻塞，同update操作。Serializable级别的隔离可能会导致大量等待超时或死锁的问题。

4：实际需求中例子 

取前N(N=100)笔未执行的数据：其中存储过程的关键sql 语句如下：

begin tran
declare @tableTempOut table
			(
				[keyItem] varchar(100)
			)
			
			;with cte_code_update
			as
			(
				SELECT TOP(100)  Nkey,[Status]
				FROM [NKey_T] 
				 where FileId= @FileId
				 and [Status]=0
				 order by [NKey_T].Id
			 )
			 UPDATE  cte_code_update 
			 SET [Status]=1
			OUTPUT  Inserted.Nkey
			 INTO @tableTempOut  
commit tran

客户端开了100个现场并发执行上面的存储过程 ，导致死锁：

解决办法1：

;with cte_code_update
			as
			(
				SELECT TOP(100)  Nkey,[Status]
				FROM [NKey_T] with(UPDLOCK)
				 where FileId= @FileId
				 and [Status]=0
				 order by [NKey_T].Id
			 )
			 UPDATE  cte_code_update (TABLOCKX)
			 SET [Status]=1
			OUTPUT  Inserted.Nkey
			 INTO @tableTempOut  

解决办法2：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
			;with cte_code_update
			as
			(
				SELECT TOP(100)  Nkey,[Status]
				FROM [NKey_T] with(UPDLOCK)
				 where FileId= @FileId
				 and [Status]=0
				 order by [NKey_T].Id
			 )
			 UPDATE  cte_code_update 
			 SET [Status]=1
			OUTPUT  Inserted.Nkey
			 INTO @tableTempOut  
--其他sql脚本
commit tran

 在select * from 表 with(uplock)后，把共享读取变成了更新锁，此时其他并行查询，需要等待当前事务完成才可以执行查询。

试验如下：

begin tran
SELECT TOP(100)  Nkey,[Status]
				FROM [NKey_T] with(UPDLOCK)
				 where FileId= 1

上面的事务不提交，再开一个查询窗口：

执行查询：

SELECT TOP(100)  Nkey,[Status]
FROM [NKey_T] with(UPDLOCK)
where FileId= 1

如果第2个查询不加：with(updlock),则 为一个共享查询，此时可正常查询 。

<完>