SQL Server的悲观并发
SQL Server天生是按悲观模式设计, 也就是说SQL 默认没有实现MVCC(Multiversion concurrency control
—多版本并发控制机制). SQL Server默认的事务隔离级别Read Committed
模式下, 读写是会相互阻塞的. 对于有Oracle背景, 默认支持多并发一致读, 读写相互不阻塞的开发或者DBA来说, 很不习惯.
一个例子, 创建表t, 并且插入一行数据, 使用SET IMPLICIT_TRANSACTIONS on
关闭自动提交,
create table tttt (id int primary key);SET IMPLICIT_TRANSACTIONS on;insert into t values(1);复制
此时在另外一个会话查询表T, 会被阻塞
select * from ttt;复制
从云趣鹰眼的可以看到阻塞情况, 查询语句等待类型是共享所LCK_M_S
, 被插入语句阻塞. 这时SQL Server高并发时非常容易遇到一种锁等待, 严重影响应用系统的性能和扩展性.
暴力解决方案
可以通过with (nolock)
或者设置事务隔离级别为READ UNCOMMITTED
. 来避免查询被DML语句阻塞. 但是这种隔离级别允许查询语句读到脏数据, 比如可能读到已经插入但是没有提交的数据或者丢失更新的数据, 不适用于严肃的应用场景.
SQL Server的乐观并发
为了提高并发性, SQL Server从2005开始, 支持乐观并发, 可以实现类似Oracle的多版本读保证一致性. 乐观并发采用行版本存储技术, 当需要插入, 或者修改某一行时, 新值会被标识上XSN, 前一个版本的数据会存放到TempDB, 类似于Oracle的undo表空间. SQL Server新的版本会通过指针指向旧的版本数据, 类似Oracle的数据库中ITL结构.
乐观并发中包含两种快照隔离级别:
READ COMMMITED SNAPSHOT ISOLATION(RCSI)
: 返回当前语句开始时的结果, 可以通过以下语句开启RCSI, 这个语句当前所有事务终止之后才能执行成功:ALTER DATABASE yunqu SET READ_COMMITTED_SNAPSHOT ON;复制SNAPSHOT ISOLATION(SI)
: 返回当前事务开始时的结果, 可以通过以下语句开启SI, 这个语句当前所有事务终止之后才能执行成功:
ALTER DATABASE yunqu SET ALLOW_SNAPSHOT_ISOLATION ON;复制
修改数据库启用乐观并发之后, 还需要在回话级别设置事务隔离级别为snapsnot:
set transaction isolation level snapshot; select * from tt;复制
这时, 查询就不会被插入语句阻塞了.
使用乐观并发的主意事项
因为DML操作需要存储就版本到TempDB, 需要对TempDB做一些管理. 比如TempDB默认大小只有8MB, 按10%的比例增长. 这在数据增删改频繁高负载的应用中可能是不适合, 建议把TempDB大小设置为200~500MB.
SQL Server在新版本中默认事务隔离级别依然为悲观模式下的READ COMMITED. 并不存在一个全局的设置更改默认的事务隔离级别为快照隔离, 也无法通过
Logon trigger
等方法来使某一个用户默认使用快照隔离. 应用只能在建立连接之后运行set transaction isolation level snapshot
来主动启用乐观并发模式, 这是比较令人遗憾的, 这意味着所有的SQL Server应用除非在代码中手动配置, 否则无法使用乐观并发模式.
