数据库锁的概念.doc

1、精品锁锁定是网络数据库的一个非常重要的概念，主要用于确保多用户环境中数据库的完整性和一致性。各种大型数据库中使用的锁定的基本理论是一致的，但具体实现存在差异。目前，大部分数据库管理系统具有一定的自我调整、自我管理功能，因此，很多用户实际上不理解锁定理论和所用数据库中锁定的具体实现。Microsoft SQL Server(以下简称SQL Server)是中小型数据库管理系统，广泛使用，进一步强调了系统对锁定管理的重视。有了SQL请求，系统就会分析请求，在满足锁定条件和系统性能之间自动为数据库添加适当的锁定，同时系统在运行过程中自动优化以实现动态锁定。对于典型用户，系统的自动锁定管理机制基本上可

2、以满足使用要求，但是，如果对数据安全性、数据库完整性和一致性有特殊要求，则必须直接控制数据库的锁定和解锁，因此必须了解SQL Server的锁定机制并掌握数据库锁定方法。锁定的多粒度和锁定升级数据库锁定是一种软件机制，它表示防止其他用户修改影响用户的数据或导致数据库数据完整性和不一致性的用户(即进程会话)占用了特定资源。资源主要指用户可以操作的数据行、索引、数据表等。根据资源的不同，锁定表示多粒度概念，即可锁定的资源的层次结构。SQL Server可以锁定的资源粒度包括数据库、表、领域、页、键值(请参阅索引所在的行数据)和行标识符(即表中的一行数据)。多粒度锁定的重要用途是支持并发操作并确保数

3、据完整性。SQL Server根据用户的请求，在分析后自动为数据库添加相应的锁定。假设用户只操作一个表中的部分数据行，则系统可以只添加几个行锁定(RID)或页锁定，以尽可能支持多个用户的并发操作。但是，如果经常在用户事务处理中对一个表中的多个记录执行操作，则会在该表的许多记录行中添加行级锁定，从而导致数据库系统中的锁定数急剧增加，从而加重系统负载并影响系统性能。因此，数据库系统通常支持锁定升级。锁升级是指调整锁的粒度，用少量的较高粒度锁替换多个较小的粒度锁，从而减少系统负载。在SQL Server中，如果一个事务处理有更多锁定，并且达到了锁定升级阈值，则行级锁定和页锁定将自动升级为表级锁定。特

4、别要注意的是，SQL Server中锁定的升级阈值和锁定升级是由系统自动确定的，不需要用户设置。锁定模式和兼容性在数据库中设置锁定不仅可以对不同的资源设置锁定，还可以使用不同级别的锁定方法。也就是说，锁定有多种模式，SQL Server的锁定模式如下：1.共享锁定在SQL Server中，共享锁定用于所有只读数据操作。共享锁定是非独占的，多个并发事务可以读取它们锁定的资源。默认情况下，读取数据后，SQL Server会立即解除共享锁定。例如，执行查询 SELECT * FROM my_table 时，首先锁定第一页，读取第一页，然后解锁第一页，最后锁定第二页。这样，您可以在阅读操作过程中修改第

5、一个未锁定的页面。但是，事务隔离级别连接选项设置和SELECT语句的锁定设置都可以更改SQL Server的这些默认设置。例如， SELECT * FROM my_table HOLDLOCK 必须在整个查询期间保持表格的锁定，直到查询完成为止。2.修改锁定修正锁定用于锁定可在修正操作的初始化阶段修改的资源，从而避免使用共享锁定导致的死锁。使用共享锁定时，修改数据分为两个步骤，因为首先获取共享锁定，读取数据，然后在执行修改操作之前将共享锁定升级为独占锁定。这样，如果两个或多个事务同时对一个事务申请了共享锁定，则在修改数据时，所有这些事务都必须将共享锁定升级为独占锁定。此时，这些事务不会解除共享

6、锁定，而是等待对方释放，从而导致死锁。如果在修改之前数据直接申请了锁定修改，则在修改数据时升级到独占锁定可以防止死锁。修改锁定与共享锁定兼容。也就是说，在使用共享锁定锁定资源后，可以使用修改锁定锁定。3.单独锁定独占锁定保留用于修改数据。锁定的资源。其他事务无法读取或修改。独占锁定与其他锁定不兼容。4.结构锁定结构锁定分为结构修改锁定(Sch-M)和结构稳定锁定(Sch-S)。SQL Server在执行表定义语言操作时使用Sch-M锁定，SQL Server在编译查询时使用Sch-S锁定。5.意向锁表明SQL Server愿意在资源的较低级别获取共享锁定或独占锁定。例如，表格层级的共用意图锁定

7、说明了传递将独占锁定解除至表格页面或列的意图。意向锁可以分为共享意向锁、单独意向锁和共享单独意向锁。共享目的锁定说明了事务处理试图通过在共享目的锁定锁定的低级资源上放置共享锁定来读取数据的意图。独占目的锁定表示事务通过在共享目的锁定锁定的低级资源上放置独占锁定，来修改数据的意图。共享独占锁定说明事务允许其他事务使用共享锁定读取顶级资源，并尝试在该资源的较低级别上放置独占锁定。6.成批修改锁定批量复制数据时使用批量修改锁定。可以使用表的TabLock提示符或系统存储过程sp_tableoption的table lock on bulk load选项设置批量修改锁定。此外，SQL Server命令

8、语句操作会影响锁定方法，您可以组合语句以创建其他锁定，如下表所述：锁定冲突和预防措施在数据库系统中，死锁将生成锁定请求循环，该循环导致多个用户(进程)分别锁定一个资源并请求锁定彼此已锁定的资源，从而导致多个用户(进程)等待释放锁定的资源。在SQL Server中，系统可以自动定期检测和处理死锁问题。系统标识在每次搜索中等待锁定请求的所有进程会话，如果在下次搜索中标识的进程处于等待状态，SQL Server将启动重复死锁搜索。(早期版本D21)在搜索中检测到锁定请求循环时，系统根据每个进程会话的死锁优先级终止优先级最低的事务，然后系统回滚事务并将1205错误消息发送到进程。这样，其他事务可以继续

9、运行。死锁优先级的设置语句如下：set dead lock _ priority low | normal 其中，LOW表示进程会话的优先级较低，在出现死锁时，可以首先停止该进程的事务。您也可以设定每个处理作业的LOCK_TIMEOUT选项，设定处理作业处于锁定要求状态的等待时间上限。此设置的语句：set lock _ time out time out _ period 其中timeout_period以毫秒为单位。理解死锁的概念后，您可以使用以下几种方法在应用程序中避免死锁：(1)合理安排表访问顺序。(2)尽量避免事务的用户干预，尽量减少一个事务的工作量。(3)使用脏读技术。脏读取不会对正

10、在访问的表设置锁定，从而防止锁定冲突。客户端/服务器应用程序环境通常不允许某些事务读取脏数据，但在某些条件下可能会读取脏数据。(4)数据访问时域离散方法。数据访问时域离散法是指在客户机/服务器结构中使用多种控制手段来控制对数据库或数据库中对象的访问期限。主要通过以下方式合理安排后台事务执行时间，并使用工作流管理集成后台事务：工作流在管理任务时限制同一类型任务的线程数(通常限制为一个)，从而防止资源过剩。合理计划不同作业运行的时间、时间，以避免同时运行多个后台作业，并防止在前台事务最多的时间运行后台作业。(5)数据存储空间离散方法。数据存储空间分布方法是通过多种方法将数据从一个表逻辑分布到多个单

11、独的空间，以提高对表的访问性能。主要通过以下方式实现：将大表按行或列分解为多个小表：第二，按其他用户组分解。(6)尽可能使用低级隔离。隔离级别是指隔离多用户事务以确保数据库数据完整性和一致性的程度，SQL92定义了四个隔离级别：未提交的读、已提交的读、可重复的读和串行。选择串行等高级别的隔离时，系统可以更好地隔离，从而更大程度地保证数据的完整性和一致性，但由于事务之间的冲突，出现死锁的可能性大大增加，对系统性能有很大影响。(7)使用Bound Connections。Bound connections允许两个或多个事务连接共享事务和锁定，事务连接请求锁定，就像其他事务申请锁定一样，因此可以在不

12、发生锁定冲突的情况下共享数据。(8)考虑使用乐观锁定，或首先获取事务单独锁定。最常见的死锁之一通常发生在序列编号生成器中，编写如下：Begin tranSelect new_id from keytab holdlockUpdatekeytab set new _ id=new _ id lCommit tran如果两个用户同时运行事务，则它们都接收共享锁定并保留事务。如果两个用户都尝试获取keytab表的独占锁定，则会发生死锁。要避免出现这种情况，必须以以下形式重写上述事务处理：Begin tranUpdate keytab set new_id=new_id lSelect new_id

13、from keytabCommit tran以这种方式复盖仅允许一个事务获得keytab的独占锁定，其他进程必须等到第一个事务完成后才能避免死锁，即使执行时间增加也是如此。如果一个事务需要可读重复功能，则在读取数据之前，必须考虑以这种方式写入事务以获取资源的独占锁定。例如，如果事务处理需要检索titles表中所有书籍的平均价格，并且在应用update之前不更改结果，则优化程序将分配独占表锁定。请考虑以下SQL代码：Begin tranupdate titles set title _ idid=title _ id。Where 1=2if(select avg(price)from title

14、s)$ 15Begin/* perform some additional processing */Endupdate titles set price=price * 1.10where price(select avg(price)from titles)Commit tran在此事务处理中，没有其他进程修改表中某一行的price，或者在事务处理结束时检索的值与在事务处理开始时检索的值不同，这一点很重要。这里的where子句看起来很奇怪，但是不管你信不信，到目前为止优化程序遇到的最完美有效的where子句计算出的结果总是false。由于优化程序找不到有效的SARG，因此处理此查询时，查询

15、计划使用独占锁定强制执行表扫描。执行此交易时，where子句会立即取得false值，因此不会执行实际的扫描，但处理作业仍会保留独占表格锁定。此进程具有独占表锁定，从而允许其他事务在不修改数据行的情况下重新读取数据行，并防止holdlock导致的潜在死锁。但是为了避免僵局，不能不付出代价。使用表锁定将死锁降至最低，同时增加对表锁定的争用。因此，在实施此方法之前，必须权衡防止死锁比允许同时访问表更重要。手动锁定建议在SQL Server系统上自动管理锁定。该系统分析用户的SQL语句要求，自动将相应的锁定添加到该请求，如果锁定数太多，则自动执行锁定升级。如前所述，升级的阈值由系统自动配置，不需要用户

16、配置。在实际应用程序中，有时需要人为地解锁数据库中的表，以确保应用程序正常运行并保持数据的一致性。例如，如果在一个应用程序的事务处理操作中，根据多个数据表的数值执行统计操作，并且为了确保统计时间的一致性和准确性，从第一个表的开始到整个表的结束，其他应用程序或事务处理无法再向该表写入数据，则该程序必须在从第一个数据表开始或整个事务处理开始时手动使用锁定(显式锁定)技术。SQL Server的SQL语句(SELECT、INSERT、DELETE和UPDATE)支持显式锁定。这四个语句类似于显式锁语法，以下是以SELECT语句为例的语法：选择自WITH其中，表示在语句执行时必须添加到表中的锁类型。指定的锁定类型包括：1.hold lock :不会在语句执行结束后立即释放添加的锁定，而是在表中保留共享锁定，直到整个事务处理结束。2.no lock:不添加共享锁定和锁定。应用此选项后，可以读取未提交读取的数据或“脏数据”。此选项仅适用于SELECT语句。3.PAG lock:指定添加页面锁定(否则，通常可能会添加表格锁定)。4.read committed:将事务处理设置为读提交隔离级别。5.reapast :跳过锁定的数据行。此选项可让交易在读取资料时略过其他交易锁定的资料列，而不是直到其他交易解除锁定为止。reapast仅适用于READ C