行版本控制的隔离是SQL Server 2005一个新的隔离框架。使用行版本控制的隔离可以在大量并发的情况下，显著减少所得产生，并且与NoLock相比，它又可以显著降低肮脏读，幻影，丢失更新等现象的发生(READ_COMMITTED_SNAPSHOT)
当在基于行版本控制的隔离下运行的事务读取数据时，读取操作不会获取正被读取的数据上的共享锁（S 锁），因此不会阻塞正在修改数据的事务。另外，锁定资源的开销随着所获取的锁的数量的减少降至最低。使用行版本控制的已提交读隔离和快照隔离可以提供副本数据的语句级或事务级读取一致性。
行版本控制隔离：优势
使用行版本控制的隔离级别具有以下优点：
·读取操作检索一致的数据库快照。
·SELECT 语句在读取操作过程中不锁定数据（读取器不阻塞编写器，编写器也不阻塞读取器）。
·SELECT 语句可以在其他事务更新行时访问最后提交的行值，而不阻塞应用程序。
·死锁的数量减少。
·事务所需的锁的数量减少，这减少了管理锁所需的系统开销。
·锁升级的次数减少。
行版本控制隔离：原理
SQL Server 2005的行版本控制原理上很简单，就是在库表中每一行的记录上都悄悄的增加了一个类时间戳列（行版本列）。
当使用行版本控制的隔离时，SQL Server 2005 Database Engine 向使用行版本控制操作数据的每个事务分配一个事务序列号 (XSN)。事务在执行 BEGIN TRANSACTION 语句时启动。但是，事务序列号在执行 BEGIN TRANSACTION 语句后的第一次读/写操作时开始增加。事务序列号在每次分配时都增加 1。
当事务执行时，SQL Server根据行版本列，来提供的行的相应版本。
而SQLServer将维护所有在数据库中执行的数据修改的逻辑副本（版本）。特定的事务每次修改行时，数据库引擎 实例都存储以前提交的 tempdb 中行的图像版本。每个版本都标记有进行此更改的事务的事务序列号。已修改行的版本使用链接列表链接在一起。最新的行值始终存储在当前的数据库中并链接至版本存储区 tempdb 中存储的版本。（修改大型对象 (LOB) 时，只有已更改的片段才会复制到 tempdb 中的版本存储区, 对于短期运行的事务，已修改行的版本将可能保存在缓冲池中，而不会写入 tempdb 数据库的磁盘文件中。如果只是临时需要副本行，它将只是简单地从缓冲池中删除而不会引发 I/O 开销。）
MSDN上一再强调要关注提升tempdb的地位，不然因为tempdb的性能导致整个数据库性能下降，可就是无妄之灾了。
行版本控制隔离：种类
行版本控制分为两种已提交读快照隔离级别（READ_COMMITTED_SNAPSHOT）和快照隔离级别（ALLOW_SNAPSHOT_ISOLATION），他们属于行版本控制的隔离的范畴，但是实际上他们却有方方面面的不同。
下面是MSDN对他们的比较。
读取数据时的行为
SNAPSHOT隔离就像真实的快照，它会无视涉及行的变化。
在SNAPSHOT隔离下运行的事务将读取数据，然后由另一事务修改此数据。SNAPSHOT事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。但是，当快照事务尝试修改已由其他事务修改的数据时，SNAPSHOT事务将生成错误并终止.
READ_COMMITTED_SNAPSHOT类似与以前的READ_COMMITTED隔离级别，
也就是说与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。然而，与快照事务不同的是，已提交读将执行下列操作：
·在其他事务提交数据更改后，读取修改的数据。
·能够更新由其他事务修改的数据，而快照事务不能。
修改数据时的行为
在使用行版本控制的已提交读事务中，使用阻塞性扫描（其中读取数据值时将在数据行上采用更新锁（U 锁）完成选择要更新的行。这与不使用行版本控制的已提交读事务相同。如果数据行不符合更新标准，在该行上将释放更新锁并且将锁定下一行并对其进行扫描。
在快照隔离下运行的事务对数据修改采用乐观方法：直到数据被修改时才获取数据上的锁。不需要获取锁就可以选择要更新的行。当数据行符合更新标准时，快照事务将验证未被并发事务（在快照事务开始后提交）修改的数据行。如果数据行已在快照事务以外修改，则将出现更新冲突，同时快照事务也将终止。更新冲突由数据库引擎 处理，无法禁用更新冲突检测。
Reference: http://edu.itbulo.com/200612/110943.htm
Part 2： sqlserver的锁
这里我们说的都是没有激活快照隔离级别的情况下，得到的结果。
实际上在sqlserver2005支持行版本隔离以前，我是无法理解sqlserver对read committed隔离级别的支持的，我的理解就是一个事务如果正在修改某行数据（暂时不论是聚集表还是heap）的时候，另外一个事务如果要读取该行记录，应该是可以读取的，只不过读到的是已经持久到数据库中的数据(我在mysql上测试就得到了这样的结论)。
但是实际情况不是这样的，实际情况是另外那个读取的事务会被阻塞，直到更新的事务完成。为 资源上什么会这样呢？ 因为第一个更新的事务会在该行记录（如果是聚集表，那么就是在key上，后面会详细说明聚集表和heap）上施加一个X锁，第二个事务读的时候会需要获得一个S锁，因为S锁和X锁是不兼容的，所以第二个事务必须等到第一个事务结束，所以会被阻塞。
在sqlserver中，一个事务在执行到某个sql语句的时候会要求获得相应的锁，然后会在事务结束（提交或者回滚）之后才释放这些锁。但是有个例外，如果事务的隔离级别是read committed或者read uncommitted，那么事务获得S锁会在读取了数据之后马上就释放。这里假设两个事务，它们都是先读取一条记录，然后更新这条记录， 在read committed下，这两个事务不会死锁，但是如果是repeatable read的话，就可能死锁。因为，在repeatable read的隔离级别下，事务读取记录后不会释放S锁，而是在事务结束后才会释放S锁，这样的话，假如第一个事务先读取了这条记录，然后第二个事务也读取了这条记录， 这个时候第一个事务要更新，更新的话需要获得X锁，而X锁和S锁是不兼容的，就表示第一个事务要等待第二个事务释放S锁，而同时第二个事务也要更新这条记录，它又等待第一个事务释放S锁，这样两个事务就死锁了。
为了解决这种死锁问题，sqlserver引入了更新锁U锁，U锁与S锁是兼容的，但是与U锁和X锁是不兼容的。U锁是用来处理读取数据进行更新这种情况，即先读取出数据，然后进行更新。还是说先前那个例子，两个事务都是先读取，但是在读取的时候必须显示说明申请UPDlock（select * from TABLE with updlock where ...），这样第一条记录在读取记录后就获得了该记录的U锁，第二个事务如果也要获得U锁，那么会被阻塞，但是能够获得S锁，然后第一个事务获得U锁后，就可以执行更新，这就避免了死锁。
sqlserver会采取如下两种方式之一来组织数据(http://msdn.microsoft.com/zh-cn/library/ms180978(SQL.90).aspx)：
- 聚集表是有聚集索引的表。
数据行基于聚集索引键按顺序存储。聚集索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。索引中每个级别的页（包括叶级别的数据页）链接在一个双向链接的列表中。但是，通过使用键值来执行从一个级别到另一级别的导航。有关详细信息，请参阅聚集索引结构。 - 堆(heap)是没有聚集索引的表。
数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。数据页不在链接列表内链接。有关详细信息，请参阅堆结构。
- 非聚集索引的基础表的数据行不按非聚集键的顺序排序和存储。
- 非聚集索引的叶层是由索引页而不是由数据页组成。
sqlserver中，索引页是按B树结构组织的，B树的每一个节点就是一个索引页。顶端节点称为根节点，根节点和叶节点之间的任何索引级别称为中间 级。
在聚集索引中，叶节点包含基础表的数据页。根节点和中间级别节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向B树上某一中 间级叶或叶级索引上的某个数据行。每级索引的页均被链接在双向的链接列表中。
非聚集索引结构
聚集索引结构中，B树已经包含数据页（基础表包含在B树中？），而非索引结构中，索引与数据是分开的，非聚集索引中每个索引行都包含非聚集索引键值和行定位符。行定位符指向聚集索引或者堆中包含该键值的数据行。
什么是B树？B树就是常说的二叉搜索树，有以下三个特点：
- 所有非叶子节点至多拥有两个子节点
- 每个节点存储一个关键字
- 非叶子节点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树。
sqlserver在细粒度的资源上获得锁之后，一般会在更大粒度的资源上施加意向锁。比如如果在RID上获得了一个X锁，那么在该RID所属的KEy，和页上会施加IX锁，这样其他的事务访问这个页的时候，就知道该页下面有一个组件（key，rid）已经被其他事务施加了X锁。
什么是页(page)？
SQL Server 中数据存储的基本单位是页（Page）。数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 每次读取或写入数据的最少数据单位是数据页。
注意：日志文件不是用这种方式存储的，而是一系列日志记录。
每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。
在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。
Part 3： 实验
-- 激活sqlserver2005的快照隔离级别
--ALTER DATABASE NorthWind SET READ_COMMITTED_SNAPSHOT ON;
--ALTER DATABASE NorthWind SET ALLOW_SNAPSHOT_ISOLATION ON;
事务1：
set transaction isolation level read committed
--set transaction isolation level REPEATABLE READ
--set transaction isolation level SNAPSHOT
BEGIN TRANSACTION
select * from account where id=1;
--insert into account(id,name,age) values(6, 'liliming-6',30);
update account set age=age+1 where id=1;
WAITFOR DELAY '00:00:10'
-- 显示该事务获得的锁，只能在sqlserver2005或以后版本，并且创建的数据库兼容级别是sql90
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
--WAITFOR DELAY '00:00:10'
SELECT * FROM account;
COMMIT
事务2：
set transaction isolation level read committed
--set transaction isolation level REPEATABLE READ
--set transaction isolation level SNAPSHOT
BEGIN TRANSACTION
select * from account;
--insert into account(id,name,age) values(6, 'liliming-6',30);
--update account set age=age+1 where id=1;
--WAITFOR DELAY '00:00:5'
-- 显示该事务获得的锁，只能在sqlserver2005或以后版本，并且创建的数据库兼容级别是sql90
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
COMMIT
如果是聚集表，那么锁的最小粒度是key，因为数据页就是保存在索引结构中，如果是heap，那么锁的最小粒度为rid，即数据行，因为索引结构和数据页是分开的。
执行顺序：
先启动事务1,等待几秒钟，保证事务1已经执行了更新并且开始等待，这个时候执行事务2
--未激活快照隔离级别
结果1：数据库没有激活快照隔离级别
事务2会被阻塞，直到事务1结束。 因为事务1在更新id=1的记录，而事务2查询的结果集包含id=1的记录，就是说事务2需要获得所有满足条件的记录的S锁，但是事务1在id=1的记录上（聚集表则是key，heap为rid）已经获得了X锁，X锁和S锁不兼容，所以事务2会等待直到事务1结束。
结果2：数据库没有激活快照隔离级别
变体：事务1的update更改为insert
事务2会被阻塞，直到事务1结束。 因为事务1在insert后获得该记录的X锁，虽然还没有提交，但是事务2仍然会尝试获得该记录的S锁，所以会被阻塞（行记录只有一个版本，一切都由锁来控制）。
结果3：数据库没有激活快照隔离级别
变体：事务1和事务2都执行insert，并且插入的行id是一样的
事务2会被阻塞。sqlserver应该会判断动作类型，由于是insert，所以第二个事务会等待第一个事务提交，然后才会跑主键冲突。如果并不阻塞，而直接就抛出这个异常，那么很可能，稍后事务1会回滚，这样的话抛出冲突就毫无道理了。
--激活快照隔离级别
结果4：
事务2不会被阻塞，会获得先前已经提交的数据。
结果5：
变体：事务1和事务2都执行insert，并且插入的行id是一样的
事务2会被阻塞，最后抛出主键冲突。
参考：
- http://ramonli.blogspot.com/2007/01/understanding-transaction-isolation.html
No comments:
Post a Comment