数据库事务锁机制分析

5．意向锁
意向锁说明SQL Server有在资源的低层获得共享锁或独占锁的意向。例如，表级的共享意向锁说明事务意图将独占锁释放到表中的页或者行。意向锁又可以分为共享意向锁、独占意向锁和共享式独占意向锁。共享意向锁说明事务意图在共享意向锁所锁定的低层资源上放置共享锁来读取数据。独占意向锁说明事务意图在共享意向锁所锁定的低层资源上放置独占锁来修改数据。共享式独占锁说明事务允许其他事务使用共享锁来读取顶层资源，并意图在该资源低层上放置独占锁。
因为此进程现在已有一个独占的表锁，所以可以保证没有其他事务会修改任何数据行，能进行重复读，且避免了由于holdlock所引起的潜在性死锁。但是，要避免死锁，不可能不付出代价。在使用表锁定来尽可能地减少死锁的同时，也增加了对表锁定的争用。因此，在实现这种方法之前，你需要权衡一下：避免死锁是否比允许并发地对表进行访问更重要。
update titles set price＝price*1.10
where price<(select avg(price)from titles)
commit tran
在这个事务中，重要的是没有其他进程修改表中任何行的price，或者说在事务结束时检索的值与事务开始时检索的值不同。这里的where子句看起来很奇怪，但是不管你相信与否，这是迄今为止优化器所遇到的最完美有效的where子句，尽管计算出的结果总是false。当优化器处理此查询时，因为它找不到任何有效的SARG，它的查询规划就会强制使用一个独占锁定来进行表扫描。此事务执行时，where子句立即得到一个false值，于是不会执行实际上的扫描，但此进程仍得到了一个独占的表锁定。
锁冲突及其防止办法
在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。
在SQL Server中，系统能够自动定期搜索和处理死锁问题。系统在每次搜索中标识所有等待锁定请求的进程会话，如果在下一次搜索中该被标识的进程仍处于等待状态，SQL Server系统中建议让系统自动管理锁，该系统会分析用户的SQL语句要求，自动为该请求加上合适的锁，而且在锁的数目太多时，系统会自动进行锁升级。如前所述，升级的门限由系统自动配置，并不需要用户配置。
在实际应用中，有时为了应用程序正确运行和保持数据的一致性，必须人为地给数据库的某个表加锁。比如，在某应用程序的一个事务操作中，需要根据一编号对几个数据表做统计操作，为保证统计数据时间的一致性和正确性，从统计第一个表开始到全部表结束，其他应用程序或事务不能再对这几个表写入数据，这个时候，该应用程序希望在从统计第一个数据表开始或在整个事务开始时能够由程序人为地（显式地）锁定这几个表，这就需要用到手工加锁（也称显式加锁）技术。
（5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。
（6）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。
(上接第D21版) 当搜索检测到锁定请求环时，系统将根据各进程会话的死锁优先级别来结束一个优先级最低的事务，此后，系统回滚该事务，并向该进程发出1205号错误信息。这样，其他事务就有可能继续运行了。死锁优先级的设置语句为：
SET DEADLOCK_PRIORITY { LOW | NORMAL}
理解了死锁的概念，在应用程序中就可以采用下面的一些方法来尽量避免死锁了：
（1）合理安排表访问顺序。
（2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。
（3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。
2．修改锁
修改锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为独占锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个事务申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为独占锁。这时，这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。如果一个数据在修改前直接申请修改锁，在数据修改的时候再升级为独占锁，就可以避免死锁。修改锁与共享锁是兼容的，也就是说一个资源用共享锁锁定后，允许再用修改锁锁定。
3．独占锁
独占锁是为修改数据而保留的。它所锁定的资源，其他事务不能读取也不能修改。独占锁不能和其他锁兼容。
4．结构锁
结构锁分为结构修改锁（Sch-M）和结构稳定锁（Sch-S）。执行表定义语言操作时，SQL Server采用Sch-M锁，编译查询时，SQL Server采用Sch-S锁。
如果要求在一个事务中具有读取的可重复能力，就要考虑以这种方式来编写事务，以获得资源的独占锁定，然后再去读数据。例如，如果一个事务需要检索出titles表中所有书的平均价格，并保证在update被应用前，结果不会改变，优化器就会分配一个独占的表锁定。考虑如下的SQL代码：
begin tran
（7）使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。
（8）考虑使用乐观锁定或使事务首先获得一个独占锁定。一个最常见的死锁情况发生在系列号生成器中，它们通常是这样编写的：
采用多粒度锁的重要用途是用来支持并发操作和保证数据的完整性。SQL Server根据用户的请求，做出分析后自动给数据库加上合适的锁。假设某用户只操作一个表中的部分行数据，系统可能会只添加几个行锁（RID）或页面锁，这样可以尽可能多地支持多用户的并发操作。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在数据库系统中，一般都支持锁升级(lock escalation)。所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQL Server中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。特别值得注意的是，在SQL Server中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。
锁的多粒度性以及锁升级
数据库中的锁是指一种软件机制，用来指示某个用户（也即进程会话，下同）已经占用了某种资源，从而防止其他用户做出影响本用户的数据修改或导致数据库数据的非完整性和非一致性。这儿所谓资源，主要指用户可以操作的数据行、索引以及数据表等。根据资源的不同，锁有多粒度（multigranular）的概念，也就是指可以锁定的资源的层次。SQL Server中能够锁定的资源粒度包括：数据库、表、区域、页面、键值（指带有索引的行数据）、行标识符（RID，即表中的单行数据）。
update titles set title_idid＝title_id ．
where 1＝2
if (selectavg(price)fromtitles)>$15
begin
/* perform some additional processing */
end
锁是网络数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。各种大型数据库所采用的锁的基本理论是一致的，但在具体实现上各有差别。目前，大多数数据库管理系统都或多或少具有自我调节、自我管理的功能，因此很多用户实际上不清楚锁的理论和所用数据库中锁的具体实现。
Microsoft SQL Server（以下简称SQL Server）作为一种中小型数据库管理系统，已经得到了广泛的应用，该系统更强调由系统来管理锁。在用户有SQL请求时，系统分析请求，自动在满足锁定条件和系统性能之间为数据库加上适当的锁，同时系统在运行期间常常自动进行优化处理，实行动态加锁。对于一般的用户而言，通过系统的自动锁定管理机制基本可以满足使用要求，但如果对数据安全、数据库完整性和一致性有特殊要求，就必须自己控制数据库的锁定和解锁，这就需要了解SQL Server的锁机制，掌握数据库锁定方法。
6．批量修改锁
批量复制数据时使用批量修改锁。可以通过表的TabLock提示或者使用系统存储过程sp_tableoption的“table lock on bulk load”选项设定批量修改锁。
另外，SQL Server命令语句操作会影响锁定的方式，语句的组合也同样能产生不同的锁定，详情如下表：
begin tran
select new_id from keytab holdlock
update keytab set new_id＝new_id+l
commit tran
如果有两个用户在同时运行这一事务，他们都会得到共享锁定并保持它。当两个用户都试图得到keytab表的独占锁定时，就会进入死锁。为了避免这种情况的发生，应将上述事务重写成如下形式：
（4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外， 避免在前台交易高峰时间运行后台任务。