SQL Server 使用快照事务隔离避免死锁

使用基于行版本控制的隔离级别：2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制，可以将读与写操作之间发生的死锁几率降至最低：

SET ALLOW_SNAPSHOT_ISOLATION ON –事务可以指定 SNAPSHOT 事务隔离级别;

SET READ_COMMITTED_SNAPSHOT ON –指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项，没有加with nolock提示)，SELECT语句会对请求的资源加S锁(共享锁)；而开启了此选项后，SELECT不会对请求的资源加S锁。

在数据库中设置READ COMMITTED SNAPSHOT 或 ALLOW SNAPSHOT ISOLATIONON ON时，查询数据时不再使用请求共享锁，如果请求的行正被锁定(例如正在被更新)，SQL_Server会从行版本存储区返回最早的关于该行的记录(SQL_server会在更新时将之前的行数据在tempdb库中形成一个链接列表。

select name,user_access,user_access_desc,
 snapshot_isolation_state,snapshot_isolation_state_desc,
 is_read_committed_snapshot_on
from sys.databases;

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DBName SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE DBName SET MULTI_USER

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

参考：使用快照隔离 https://msdn.microsoft.com/zh-cn/library/tcbchxcb(v=vs.80).aspx

发表评论