事务管理（二）——SQL SERVER的事务管理

afox123

1.3 SQL SERVER 的事务管理
1.3.1 SQL SERVER 2005的并发控制模型
  SQL SERVER 提供了悲观并发控制模式和乐观并发控制模式。
  悲观并发控制模式假定系统中存在足够多的数据修改操作以致任何确定的读操作都可能会受到由别的用户所制造的数据修改的影响。换言之，数据库系统具有悲观的行为并且假定冲突是会发生的。采用了锁机制实现占有被锁定的资源，在悲观并发环境中，读者（reader）和写者（writer）之间是会互相阻塞的。
  乐观并发控制模式假定系统中存在非常少的相冲突的数据修改操作，以致任何单独的事务都不太可能修改其他事务正在修改的数据。乐观并发控制的默认行为是采用行版本控制使数据读者能够看到修改操作发生以前的数据状态。在乐观并发环境中读者和写者之间不会互相阻塞。写者之间会发生阻塞，而这也就是造成冲突的原因了。当冲突发生时，SQL Server 会生成一个错误消息，但这需要由上层的应用程序来响应此错误。

1.3.2 SQL SERVER 2005的事务隔离级别
隔离级别脏读不可重复读幻影读并发控制模型
未提交读YesYesYes悲观
已提交读NoYesYes悲观
已提交读（快照）NoYesYes乐观
可重复读NoNoYes悲观
快照NoNoNo乐观
可串行化NoNoNo悲观

1.3.3 SQL SERVER的乐观模式
  乐观锁的主要缺点是当发生冲突的时候，SQLSERVER会抛异常给应用程序处理，这样的代价是增加处理的复杂性。一般应用程序会要求重新执行事务，那么在高冲突的系统中，因为不断的重复执行而影响系统的性能。因此乐观锁一般使用在并发冲突很少的系统中。这样就可以提供较好的并发性。
  快照和已提交读快照的主要区别在于：已提交读快照只是在更新的时候将比较快照和原始数据进行版本比较。而快照则不仅在更新时候进行快照比较，而且在多次读的事务中比较所读取数据的版本。
        
1.3.4 SQL SERVER的悲观模式
  SQLSERVER的悲观锁模式使用锁来实现了并发控制。对于SQLServer采用自旋锁的方式来解决互斥访问。对于锁需要从以下四个方面进行理解。
1）锁的模式：包括共享锁，排他锁，更新锁，意向锁，架构锁，大量更新锁
2）锁的粒度：行、分页、索引键、索引键的范围、扩展或是整张表
3）锁的持续时间：事务的不同隔离级别就是通过锁定的时间范围来实现的。例如对于READ_UNCOMMIT排他锁在更新完成后立即释放，而对于READ_COMMIT则排他锁一直会持续到事务结束才释放。
4）锁的所有权：锁可以被事务，Session和游标所持有。

A. SQL Server 的锁定模式
缩写锁定模式描述
SShared允许其他用户读取但不能修改被锁定资源
XExclusive防止别的进程修改或者读取被锁定资源的数据（除非该进程设定为未提交读隔离级别）
UUpdate防止其他进程获取更新锁或者排他锁；在搜索数据并修改时使用
ISIntent shared表示该资源的一个组件被一个共享锁锁定住了。这类锁只能在表级或者分页级才能被获取
IUIntent update表示该资源的一个组件被一个更新锁锁定住了。这类锁只能在表级或者分页级才能被获取
IXIntent exclusive表示该资源的一个组件被一个排他锁锁定住了。这类锁只能在表级或者分页级才能被获取
SIXShared with intent exclusive表示一个正持有共享锁的资源还有一个组件（一个分页或者一行记录）被一个排他锁锁定住了
SIUShared with intent update表示一个正持有共享锁的资源还有一个组件（一个分页或者一行记录）被一个更新锁锁定住了
UIXUpdate with intent exclusive表示一个正持有更新锁的资源还有一个组件（一个分页或者一行记录）被一个排他锁锁定住了
Sch-SSchema stability表示一个使用该表的查询正在被编译
Sch-MSchema modification表示表的结构正在被修改
BUBulk update在一个大容量复制操作将数据导入表并且应用了TABLOCK 查询提示时使用（手动或者自动皆可）

SQL SERVER 锁的粒度
资源类型资源描述（Resource_Description）例子
DATABASE无；每个被锁资源的resource_database_ID 字段都指明了数据库
OBJECT对象ID （可以是任何数据库对象，不一定就是表），其数值是由resource_associated_entity_id 字段反馈的69575286
EXTENT扩展（extent）的第一个分页的文件号:页号1:96
PAGE实际表或者索引分页的文件号:分页号1:104
KEY由所有键的组成部分及定位符得到的哈希值。对于一个建在堆上的非聚集索引（c1 和c2 是索引列），哈希将包含来自c1，c2以及RID 的贡献ac0001a10a00
ROW实际数据行的文件号:页号:槽号1:161:3
APPLICATION一个连接串由以下部分组成有权访问该锁的数据库主体、锁名的前32个字符以及根据该锁全名得到的哈希值0:[ProcLock]:(8e14701f)

1.3.5 锁的使用实例
  SQLSERVER2005可以使用sys.dm_tran_locks来查看系统的的锁定状态。下面将通过实例来查看系统中的锁的具体的使用情况。
使用附件中的SQL 语句建立表LOCK_TEST_NO_INDEX , LOCK_TEST_INDEX.
实例中test_table_prepare.sql文件定义了进行测试的数据表，以及相应的测试数据。
文件lock_test.sql 中将观察各种不同的隔离级别下，SQLSERVER对于所使用的锁。
文件XLOCK_Deadlock1.sql一种常见的因为X锁而引发的死锁。
文件SLOCK_Deadlock.sql 一种常见的因为S锁而引发的死锁。

1.3.6 死锁总结
  数据库在不同的隔离级别下会出现不同类型的死锁。
在使用乐观模式的情况下数据库不会有死锁的发生，但是会出现数据更新的失败。
在使用悲观模式的情况下
1）READ UNCOMMITTED隔离级别下不会出现死锁。
2）READ COMMITTED隔离级别下可能出现排他锁的死锁。
3）REPEATABLE READ隔离级别和SERIALIZABLE隔离级别下可能出现共享锁引发死锁和排他锁引发的死锁。

1.3.7 SQL Server2005使用Profile监测死锁
启动SQL Server Profiler工具(在Microsoft SQL Server Management  Studio的工具菜单上就发现它)，创建一个Trace。 然后启动该Trace

执行实例中的死锁的实例程序。当执行完成后可以得到如下的视图。


1.3.8死锁避免
  关键是定义有效的事务隔离级别。系统是一个以读取为主的系统那么可以考虑使用乐观模式进行控制。对于更新较多的系统，尽量使用较低READ COMMITED隔离级别避免共享锁引发的死锁。并且在应用程序级别对于特殊事务进行REPEATABLE READ（少数事务才会实现该隔离级别，所以不应对整个数据库设定该隔离级别）的实现。
  在程序中避免排他锁的死锁发生，提高事务的执行效率，缩短锁占有时间。将无关的操作不要放到事务中执行。尤其是一些效率较低的处理过程。例如应用程序中的FOR循环处理逻辑，应用程序调用邮件服务器，发送AUTO-MAIL， 应用程序的文件操作等处理比较慢的操作应该应可能的从事务中分离出来。