sql server锁机制

作者：宋立桓[导读: 各种大型数据库所采用的锁的基本理论是一致的，但在具体实现上各有差别。SQL Server更强调由系统来管理锁。在用户有SQL请求时，系统分析请求，自动在满足锁定条件和系统性能之间为数据库加上适当的锁，同时系统在运行期间常常自动进行优化处理，实行动态加锁。对于一般的用户而言，通过系统的自动锁定管理机制基本可以满足使用要求，但如果对数据安全、数据库完整性和一致性有特殊要求，就需要了解SQL Server的锁机制，掌握数据库锁定方法。]  

    锁是数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。 我们知道，多个用户能够同时操纵同一个数据库中的数据，会发生数据不一致现象。即如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。这些问题包括：丢失更新、脏读、不可重复读和幻觉读：1．当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。例如，两个编辑人员制作了同一文档的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。2. 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。例如，一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。3．不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。4．幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。所以，处理多用户并发访问的方法是加锁。锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上。为了控制锁定的资源，应该首先了解系统的空间管理。在SQL Server 2000系统中，最小的空间管理单位是页，一个页有8K。所有的数据、日志、索引都存放在页上。另外，使用页有一个限制，这就是表中的一行数据必须在同一个页上，不能跨页。页上面的空间管理单位是盘区，一个盘区是8个连续的页。表和索引的最小占用单位是盘区。数据库是由一个或者多个表或者索引组成，即是由多个盘区组成。放在一个表上的锁限制对整个表的并发访问；放在盘区上的锁限制了对整个盘区的访问；放在数据页上的锁限制了对整个数据页的访问；放在行上的锁只限制对该行的并发访问。

SQL Server 2000 具有多粒度锁定，允许一个事务锁定不同类型的的资源。为了使锁定的成本减至最少，SQL Server 自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其它事务对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。SQL Server 可以锁定行、页、扩展盘区、表、库等资源。行是可以锁定的最小空间, 行级锁占用的数据资源最少，所以在事务的处理过程中，允许其他事务继续操纵同一个表或者同一个页的其他数据，大大降低了其他事务等待处理的时间，提高了系统的并发性。页级锁是指在事务的操纵过程中，无论事务处理数据的多少，每一次都锁定一页，在这个页上的数据不能被其他事务操纵。在SQL Server 7.0以前，使用的是页级锁。页级锁锁定的资源比行级锁锁定的数据资源多。在页级锁中，即使是一个事务只操纵页上的一行数据，那么该页上的其他数据行也不能被其他事务使用。因此，当使用页级锁时，会出现数据的浪费现象，也就是说，在同一个页上会出现数据被占用却没有使用的现象。在这种现象中，数据的浪费最多不超过一个页上的数据行。表级锁也是一个非常重要的锁。表级锁是指事务在操纵某一个表的数据时，锁定了这个数据所在的整个表，其他事务不能访问该表中的其他数据。当事务处理的数据量比较大时，一般使用表级锁。表级锁的特点是使用比较少的系统资源，但是却占用比较多的数据资源。与行级锁和页级锁相比，表级锁占用的系统资源例如内存比较少，但是占用的数据资源却是最大。在表级锁时，有可能出现数据的大量浪费现象，因为表级锁锁定整个表，那么其他的事务都不能操纵表中的其他数据。盘区锁是一种特殊类型的锁，只能用在一些特殊的情况下。簇级锁就是指事务占用一个盘区，这个盘区不能同时被其他事务占用。例如在创建数据库和创建表时，系统分配物理空间时使用这种类型的锁。系统是按照盘区分配空间的。当系统分配空间时，使用盘区锁，防止其他事务同时使用同一个盘区。当系统完成分配空间之后，就不再使用这种类型的盘区锁。特别是，当涉及到对数据操作的事务时，不使用盘区锁。数据库级锁是指锁定整个数据库，防止任何用户或者事务对锁定的数据库进行访问。数据库级锁是一种非常特殊的锁，它只是用于数据库的恢复操作过程中。这种等级的锁是一种最高等级的锁，因为它控制整个数据库的操作。只要对数据库进行恢复操作，那么就需要设置数据库为单用户模式，这样系统就能防止其他用户对该数据库进行各种操作。行级锁是一种最优锁，因为行级锁不可能出现数据既被占用又没有使用的浪费现象。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在SQL Server中，还支持锁升级(lock escalation)。所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQL Server中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。特别值得注意的是，在SQL Server中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。       在SQL  Server数据库中加锁时，除了可以对不同的资源加锁，还可以使用不同程度的加锁方式，即锁有多种模式，SQL Server中锁模式包括：     1．共享锁     SQL Server中，共享锁用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。默认情况下，数据被读取后，SQL Server立即释放共享锁。例如，执行查询“SELECT  *  FROM  AUTHORS”时，首先锁定第一页，读取之后，释放对第一页的锁定，然后锁定第二页。这样，就允许在读操作过程中，修改未被锁定的第一页。但是，事务隔离级别连接选项设置和SELECT语句中的锁定设置都可以改变SQL Server的这种默认设置。例如，“ SELECT  *  FROM  AUTHORS  HOLDLOCK”就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。     2．更新锁     更新锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为排它锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个事务申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为排它锁。这时，这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。如果一个数据在修改前直接申请更新锁，在数据修改的时候再升级为排它锁，就可以避免死锁。3．排它锁     排它锁是为修改数据而保留的。它所锁定的资源，其他事务不能读取也不能修改。   4．结构锁     执行表的数据定义语言 (DDL) 操作（例如添加列或除去表）时使用架构修改 (Sch-M) 锁。当编译查询时，使用架构稳定性 (Sch-S) 锁。架构稳定性 (Sch-S) 锁不阻塞任何事务锁，包括排它锁。因此在编译查询时，其它事务（包括在表上有排它锁的事务）都能继续运行。但不能在表上执行 DDL 操作。5．意向锁     意向锁说明SQL Server有在资源的低层获得共享锁或排它锁的意向。例如，表级的共享意向锁说明事务意图将排它锁释放到表中的页或者行。意向锁又可以分为共享意向锁、独占意向锁和共享式独占意向锁。共享意向锁说明事务意图在共享意向锁所锁定的低层资源上放置共享锁来读取数据。独占意向锁说明事务意图在共享意向锁所锁定的低层资源上放置排它锁来修改数据。共享式排它锁说明事务允许其他事务使用共享锁来读取顶层资源，并意图在该资源低层上放置排它锁。     6．大容量更新锁  当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新 锁。大容量更新锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。    SQL Server系统中建议让系统自动管理锁，该系统会分析用户的SQL语句要求，自动为该请求加上合适的锁，而且在锁的数目太多时，系统会自动进行锁升级。如前所述，升级的门限由系统自动配置，并不需要用户配置。 在实际应用中，有时为了应用程序正确运行和保持数据的一致性，必须人为地给数据库的某个表加锁。比如，在某应用程序的一个事务操作中，需要根据一编号对几个数据表做统计操作，为保证统计数据时间的一致性和正确性，从统计第一个表开始到全部表结束，其他应用程序或事务不能再对这几个表写入数据，这个时候，该应用程序希望在从统计第一个数据表开始或在整个事务开始时能够由程序人为地（显式地）锁定这几个表，这就需要用到手工加锁（也称显式加锁）技术。可以使用 SELECT、INSERT、UPDATE 和 DELETE 语句指定表级锁定提示的范围，以引导 Microsoft  SQL Server 2000 使用所需的锁类型。当需要对对象所获得锁类型进行更精细控制时，使用表级锁定提示更改默认的锁定行为。所指定的表级锁定提示有如下几种：    1． HOLDLOCK: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。     2． NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。     3． PAGLOCK：指定添加页锁（否则通常可能添加表锁）。   4． READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。。   5． READPAST: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作。    6． READUNCOMMITTED：等同于NOLOCK。     7． REPEATABLEREAD：设置事务为可重复读隔离性级别。   8． ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。    9． SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。  10． TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。     11． TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。   12． UPDLOCK ：指定在读表中数据时设置更新 锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。    死锁问题在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。这种死锁是最典型的死锁形式, 例如在同一时间内有两个事务A和B，事务A有两个操作：锁定表part和请求访问表supplier；事务B也有两个操作：锁定表supplier和请求访问表part。结果，事务A和事务B之间发生了死锁。    死锁的第二种情况是，当在一个数据库中时，有若干个长时间运行的事务执行并行的操作，当查询分析器处理一种非常复杂的查询例如连接查询时，那么由于不能控制处理的顺序，有可能发生死锁现象。    在SQL Server中，系统能够自动定期搜索和处理死锁问题。系统在每次搜索中标识所有等待锁定请求的进程会话，如果在下一次搜索中该被标识的进程仍处于等待状态，SQL Server就开始递归死锁搜索。当搜索检测到锁定请求环时，SQL Server 通过自动选择可以打破死锁的线程（死锁牺牲品）来结束死锁。SQL Server 回滚作为死锁牺牲品的事务，通知线程的应用程序（通过返回 1205 号错误信息），取消线程的当前请求，然后允许不间断线程的事务继续进行。SQL Server 通常选择运行撤消时花费最少的事务的线程作为死锁牺牲品。另外，用户可以使用 SET 语句将会话的 DEADLOCK_PRIORITY 设置为 LOW。DEADLOCK_PRIORITY 选项控制在死锁情况下如何衡量会话的重要性。如果会话的设置为 LOW ，则当会话陷入死锁情况时将成为首选牺牲品。    理解了死锁的概念，在应用程序中就可以采用下面的一些方法来尽量避免死锁了： （1）合理安排表访问顺序。 （2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些, 保持事务简短并在一个批处理中。 （3）数据访问时域离散法, 数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外， 避免在前台交易高峰时间运行后台任务。 （4）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。 （5）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。 （6）使用绑定连接,  绑定连接允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。   

    总之，了解SQL Server的锁机制，掌握数据库锁定方法, 对一个合格的DBA来说是很重要的。

原文转自：http://www.ltesting.net