sql serer 快照和乐观已提交读的区别

http://www.cnblogs.com/OpenCoder/archive/2009/12/12/1622694.html

sql server 2005/2008的事务有两种是支持快照读取（官方叫：行版本管理器读取）,如果遇到冲突的时候快照会读取事务执行前的数据避免被阻塞

现假如已启动事务begin tran update academy set name='科技学院' where name='科技学院' --commit --测试后再执行这句

 

快照分为两种:

1、已提交读行版本管理器模式:

ALTER DATABASE TESTDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE SET TRANSACTION ISOLATION LEVEL READ COMMITTED begin tran select * from academy --可执行，查询将在快照数据上查找数据，并且如果冲突进程的事务更新提交后会立即查询到更新后的最新数据，所以会造成不可重复读 select * from academy with(updlock)--将被阻塞，原因是显示声明更新锁updlock（排它锁也一样），会在真实数据上申请更新锁（不是在快照数据上），申请更新锁显然就会被冲突进程的排它锁阻塞 update academy set name='数学学院' where name='数学学院'--在其余行(非更新行)是非排它锁或非更新锁时可执行，因为在已提交读模式下update会先遍历整个表查找要更新的行，在遍历的时候就会对遍历行临时加上更新锁，如果任意一行有排它锁或更新锁，就会造成阻塞(当然有个解决办法是在update条件涉及的列上使用聚集索引，直接从索引B树上查找要更新的行)，查完要更新的数据后，事务就会向进程申请释放非更新行的更新锁，接着申请将更新行的更新锁转换为排它锁 delete from academy where name='cx'--在其余行(非更新行)是非排它锁或非更新锁时可执行，同update insert into academy values(newid(),'new_subject')--可执行，插入数据在验证主键不重复后（判断重复是根据主键索引，不是根据查询），立即插入数据接着立刻申请排它锁 commit

 

2、快照模式:

ALTER DATABASE TESTDB SET ALLOW_SNAPSHOT_ISOLATION ON SET TRANSACTION ISOLATION LEVEL SNAPSHOT begin tran select * from academy --可执行，查询将在快照数据上查找数据，但是和已提交读的区别是冲突进程的事务更新提交后再执行本查询还是查询本事务开始前的快照数据，只要事务不结束就不会读取数据库中的真实数据，所以不会造成不可重复读 select * from academy with(updlock)--将被阻塞，原因是显示声明更新锁updlock（排它锁也一样），会在真实数据上申请更新锁（不是在快照数据上），申请更新锁显然就会被冲突进程的排它锁阻塞 update academy set name='数学学院' where name='数学学院'--可执行，update隐式的查询不会在真实数据上加锁，快照的Update查询和Select差不多都是在行版本上查询要更新的行，查到符合更新条件的数据后会立即申请更新锁，接着转换成排它锁，唯一的问题就是由于查询是在行版本上进行的，所以一旦修改了其他事务修改过的数据，那么就会报3960错误:快照隔离事务由于更新冲突而中止。您无法在数据库'TestDB'中使用快照隔离来直接或间接访问表 'dbo.academy'，以便更新、删除或插入已由其他事务修改或删除的行。请重试该事务或更改 update/delete 语句的隔离级别。 delete from academy where name='cx'--可执行，同update insert into academy values(newid(),'new_subject')--可执行，插入数据在验证主键不重复后（判断重复是根据主键索引，不是根据查询），立即插入数据接着立刻申请排它锁 commit

一点补充（以下原数据是指数据库内事务开始前的数据）：

update在事务内如果对原数据造成更改（如果更改后和原数据相比未作任何更改不算），那么会将原数据和更新后未提交的数据都暂时保存下来申请排它锁(但是请注意原数据在nolock或uncommitted查询下不会被查询到，nolock查询是针对事务的最新数据不管提交没有，nolock不会去查原数据)，接着如果事务回滚就删除更新数据，如果事务提交就删除原数据

insert在事务内在插入前会检查即将插入的数据的主键或者唯一键是否和原数据重复（判断重复不是在原数据上查询而是用的索引），如果不重复则插入数据并且对刚才插入的数据申请排它锁，如果找到原数据内有重复的键值，就会对原数据重复的数据行申请排它锁