SQLserver 中的各种锁的说明

转载自：http://blog.csdn.net/lingxyd_0/article/details/6129437

 
 

  
  锁定提示                                 描述  
HOLDLOCK        将共享锁保留到事务完成，而不是在相应的表、行或数据页不再需要时就立即释放锁。HOLDLOCK        等同于        SERIALIZABLE。        
NOLOCK        不要发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于        SELECT        语句。        
PAGLOCK        在通常使用单个表锁的地方采用页锁。        
READCOMMITTED        用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL        Server        2000        在此隔离级别上操作。        
READPAST        跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集内），而不是阻塞该事务，使其等待其它事务释放在这些行上的锁。READPAST        锁提示仅适用于运行在提交读隔离级别的事务，并且只在行级锁之后读取。仅适用于        SELECT        语句。        
READUNCOMMITTED        等同于        NOLOCK。        
REPEATABLEREAD        用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。        
ROWLOCK        使用行级锁，而不使用粒度更粗的页级锁和表级锁。        
SERIALIZABLE        用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于        HOLDLOCK。        
TABLOCK        使用表锁代替粒度更细的行级锁或页级锁。在语句结束前，SQL        Server        一直持有该锁。但是，如果同时指定        HOLDLOCK，那么在事务结束之前，锁将被一直持有。        
TABLOCKX        使用表的排它锁。该锁可以防止其它事务读取或更新表，并在语句或事务结束前一直持有。        
UPDLOCK        读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK        的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。        
XLOCK        使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。可以使用        PAGLOCK        或        TABLOCK        指定该锁，这种情况下排它锁适用于适当级别的粒度
 
 
 
 


 
 
 
 


 
 
 
 


 
 
 
 

  
  

   
   
死锁
  
  
  
  

   
   
多个会话同时访问数据库一些资源时，当每个会话都需要别的会话正在使用的资源时，死锁就有可能发生。 死锁在多线程系统中都有可能出现，并不仅仅局限于于关系数据库管理系统。
  
  
  
  

   
   
锁的类型
  
  
  
  

   
   
一个数据库系统在许多情况下都有可能锁数据项。其可能性包括：
   
   
Rows—数据库表中的一整行
Pages—行的集合（通常为几kb）
Extents—通常是几个页的集合
Table—整个数据库表
Database—被锁的整个数据库表
   
   
除非有其它的说明，数据库根据情况自己选择最好的锁方式。不过值得感谢的是，SQL Server提供了一种避免默认行为的方法。这是由锁提示来完成的。
  
  
  
  

   
   
锁提示
  
  
  
  

   
   
Tansact－SQL提供了一系列不同级别的锁提示，你可以在SELECT,INSERT,UPDATE和DELETE中使用它们来告诉SQL Server你需要如何通过重设锁。可以实现的提示包括：
   
   
FASTFIRSTROW—选取结果集中的第一行，并将其优化
HOLDLOCK—持有一个共享锁直至事务完成
NOLOCK—不允许使用共享锁或独享锁。这可能会造成数据重写或者没有被确认就返回的情况； 因此，就有可能使用到脏数据。这个提示只能在SELECT中使用。
PAGLOCK—锁表格
READCOMMITTED—只读取被事务确认的数据。这就是SQL Server的默认行为。
READPAST—跳过被其它进程锁住的行，所以返回的数据可能会忽略行的内容。这也只能在SELECT中使用。
READUNCOMMITTED—等价于NOLOCK.
REPEATABLEREAD—在查询语句中，对所有数据使用锁。这可以防止其它的用户更新数据， 但是新的行可能被其它的用户插入到数据中，并且被最新访问该数据的用户读取。
ROWLOCK—按照行的级别来对数据上锁。SQL Server通常锁到页或者表级别来修改行， 所以当开发者使用单行的时候，通常要重设这个设置。
SERIALIZABLE—等价于HOLDLOCK.
TABLOCK—按照表级别上锁。在运行多个有关表级别数据操作的时候，你可能需要使用到这个提示。
UPDLOCK—当读取一个表的时候，使用更新锁来代替共享锁，并且保持一直拥有这个锁直至事务结束。 它的好处是，可以允许你在阅读数据的时候可以不需要锁，并且以最快的速度更新数据。
XLOCK—给所有的资源都上独享锁，直至事务结束。 微软将提示分为两类：granularity和isolation-level。Granularity提示包括PAGLOCK, NOLOCK, ROWLOCK和TABLOCK。而isolation-level提示包括HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD和SERIALIZABLE。
 
可以在Transact-SQL声明中使用这些提示。它们被放在声明的FROM部分中，位于WITH之后。WITH声明在SQL Server 2000中是可选部分，但是微软强烈要求将它包含在内。这就使得许多人都认为在未来的SQL Server发行版中，就可能会包含这个声明。下面是提示应用于FROM从句中的例子： [ FROM { < table_source > } [ ,...n ] ] < table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] < table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
  
  

   
   
词汇表
  
  
  
  

   
   
会话 (session)
   
   
English Query 中由 English Query 引擎执行的操作序列。会话在用户登录时开始，在用户注销时结束。 会话期间的所有操作构成一个事务作用域，并受由登录用户名和密码决定的权限的支配。 堆表 (heap table)
   
   
如果一个表没有索引，数据行以随机的顺序存储，这种结构称为堆。这种表称为堆表。 意向锁 (intent lock)
   
   
放置在资源层次结构的一个级别上的锁，以保护较低级别资源上的共享或排它锁。例如，在 SQL Server 2000 数据库引擎任务应用表内的共享或排它行锁之前，在该表上放置意向锁。如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。 排它锁(exclusive lock)
   
   
一种锁，它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作（INSERT、UPDATE 或 DELETE）过程中始终应用排它锁。 隔离级别 (isolation level)
   
   
控制隔离数据以供一个进程使用并防止其它进程干扰的程度的事务属性。设置隔离级别定义了 SQL Server 会话中所有 SELECT 语句的默认锁定行为。 扩展(盘)区 (extent)
   
   
每当 SQL Server 对象（如表或索引）需要更多空间时分配给该对象的空间的单元。在 SQL Server 2000 中，一个扩展是八个邻接的页。 锁粒度(lock granularity)
   
   
SQL Server中数据以8KB为一页(page)的单位保存，连续的8个页组成一个扩展(extent)。创建数据库时， 按这种方式来分配磁盘空间。当数据库容量增加时，意味着要创建更多的页和扩展。按照数据的存储结构 (row,page,extent)进行加锁，就是锁粒度。
   
   
SQL Server 2000里，最低的锁粒度是行(row)锁。SQL Server可以单独锁行，数据页，扩展，表。 假设在UPDATE操作中只影响一行记录，SQL Server会将该行记录锁定，其他用户只有等该行记录的 更新操作完毕后才能修改。另一方面，对于没有锁定的行记录，其他用户是可以进行修改的。 因此行级锁对于并发是最佳的。
   
   
现在假设UPDATE操作影响1000行记录，SQL Server是否一次锁定一行？那就意味着如果有一个这样的选项，在 内存允许前提下，需要1000个锁。实际上，SQL Server会根据这些数据是否分布在连续的页，来决定是否用 几个页面锁，或者扩展锁，或者是表锁。如果SQL Server加了页面锁，那么这些页面上的记录其它用户就无法 访问或者修改，即使页面上有些数据并非属于这1000行记录。这就是一种追求并发性能和资源消耗之间的平衡策略。
   
   
SQL Server对锁需要的资源十分敏感，也就是说，SQL Server查询优化器检测到可用内存较低时，就会使用页锁来 替代多个行锁。同样，在内存消耗更低的判断下，会优先选择表锁而几个扩展锁。 锁信息的标识
   
   
锁类型:
   
   
RID :行标识符。用于在表中单独锁定一行。
KEY :键, 索引内部的行锁。用于保护可串行事务中的键范围。
PAG :数据或索引页。
EXT :相邻的八个数据页或索引页构成的一组。
TAB :包括所有数据和索引在内的整个表。
DB :数据库。
   
   
 
   
   
^_^，说是简单介绍，其实我觉得已经对锁介绍也蛮多了，也许有写得不对的地方，有心人帮忙指点一下。词汇的中文翻译 是从SQL Server联机帮助(books online)上搬用的。下面开始正文，好歹人家也是发表在堂堂DBA大网站上的Article，呵呵。
  
  
  
  

  
  
 
  
  

  
  

   
   
使用SQL Server 6年多了，在下自认为对SQL Server还是比较熟悉的，而且我喜欢将SQL Server内部的一些 东西搞清楚。
   
   
当我在教一门SQL Server编程课程时，我注意到微软MSDN中提到了锁兼容性，在MSDN 列举了一个兼容性关系的表格。
   
   
看过这张关系表格，我就想知道是否存在用于更新的意向锁(Intent Update lock)？于是我开始阅读相关的资料。 这篇文章也是我研究的结果。这篇文章的适用读者是那些对隔离级别(isolation level)，意向锁，死锁和锁粒度有所了解的。 如果你对这些领域还不了解，那么我建议你在读这篇文章前，应该先去了解和阅读相关资料。
   
   
希望这篇文章能够加深你对SQL Server锁的理解，也许有些技巧还能够在SQL Server编程中带来帮助。
   
   
必须指出，即使不知道锁是如何工作的，你也能长时间愉快地使用SQL Server，并且能创建高质量的代码和数据库设计。 不过如果你象我那样喜欢探究事情的内部机理，或者你的工作需要你掌握一些性能方面的知识，我很乐意能教你一些有用的东西。
  
  
  
  

   
   
更新锁(Update Locks)
  
  
  
  

   
   
死锁的典型情况是SPID X锁住了资源A，并在等待对资源B进行加锁，而SPID Y锁住了资源B，在等待对资源A加锁，如此就 形成了死锁。如果不理解，查询 MSDN 或者相关的资料。
   
   
现在来假想更多情形下的死锁。假设：SPID X在资源A上加了共享锁，SPID Y也在资源A上加了共享锁，因为是共享锁， 所以这样没有问题。现在X想把共享锁升级为排它锁(exclusive lock)以用于更新资源。X就必须等Y释放共享锁才能办到， 当X在等待时，Y也想做同样的事情。这样，X在等Y释放，Y同时在等待X释放，死锁产生了。这种死锁被称为 转换死锁(conversion deadlock)。
   
   
这种情况会很常见，为避免这种死锁，就引入了更新锁机制。更新锁允许连接读取资源，同时宣告它因为要编辑数据而要开始 锁住资源了。SQL Server并无法提前知道一个事务要把共享锁转换成排它锁了，当然有一个情况特殊，即只在一个SQL语句中 完成读取然后更新的操作，比如说UPDATE XXX (SELECT YYY ....)这种类型。对于一般的SELECT语句，我们必须显示地 使用UPDLOCK提示。