sqlserver隔离级别

隔离级别用于决定并发事务如何读写数据的操作，同时对性能也有一定的影响作用，理解了事务隔离级别有助于理解事务的死锁。

如何确认隔离级别

• 确认当前会话的事务隔离级别（关注 isolation level）

DBCC USEROPTIONS

• SQL Server 支持以下几种隔离级别：

注：隔离级别越高，读操作的请求锁定就越严格，锁的持有时间久越长；所以隔离级别越高，一致性就越高，并发性就越低，同时性能也相对影响越大。

如何设置隔离级别

会话级别

• 设置会话层面的隔离级别，示例 READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

查询表级别

• 通过锁提示设置查询表层面的隔离级别（锁提示会覆盖当前事务的隔离级别设置），示例如下：

--.不发出共享锁，允许脏读，可提高并发
SELECT * FROM xxx WITH (NOLOCK)

--.使用行锁锁定需要的行，而不是锁定整个页或表
UPDATE xxx WITH (ROWLOCK) SET name = 'abc' WHERE id = 1

--.其他事务可以读取表，但不能更新和删除，且只有该进程可以更新
SELECT * FROM xxx WITH (UPDLOCK)

--.其他事务不能读取表，也不能更新和删除
SELECT * FROM xxx WITH (TABLOCKX)

--.使用行级排他锁
SELECT * FROM xxx WITH (ROWLOCK, XLOCK) WHERE id = 1001

--.使用更新锁并保持到事务结束
SELECT * FROM xxx WITH (UPDLOCK, HOLDLOCK) WHERE Stock < 10

--.表级共享锁，防止其他事务修改表
SELECT * FROM xxx WITH (TABLOCK, HOLDLOCK)

注：正确使用锁提示可以提升安全、优化并发，但过度使用可能导致严重的并发问题

存储过程级别

• 在存储过程的开头使用 SET TRANSACTION ISOLATION LEVEL 语句来设置隔离级别，示例 READ COMMITTED

CREATE PROCEDURE xxx
AS
BEGIN
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  -- 存储过程的代码
END

数据库级别

• 开启快照读：在保护事务不脏读未提交的数据修改的同时，尽量减少锁定争用（高并发推荐）

注：修改隔离级别需要关闭其他所有到该库的连接（并不要求设置单用户模式），但会造成阻塞而影响业务。

--.查询数据库状态
select name,snapshot_isolation_state,is_read_committed_snapshot_on from sys.databases

--.更改数据库为 快照隔离模式(乐观并发模型)
ALTER DATABASE [xxx] SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE [xxx] SET ALLOW_SNAPSHOT_ISOLATION ON

--.查询数据库状态
select name,snapshot_isolation_state,is_read_committed_snapshot_on from sys.databases

• 延迟持久性：针对sql2014及以后版本，以丢数据的风险提高数据导入性能，可以提升磁盘io性能，特别是报表服务器(效果明显)

--.查看延迟持久性状态
select name,delayed_durability,delayed_durability_desc from sys.databases

--.更改延迟持久性
ALTER DATABASE [xxx] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT

实例级别

• 不支持

附录

• 参考：SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• 参考：ALTER DATABASE SET (Transact-SQL)