SQL锁机制高级篇-------一

HDTV 2009-12-18

展开全文

SQL锁机制高级篇-------一

在看这篇文章(翻译)之前，简单介绍一下锁，顺便也带出几个专用词汇的翻译。

什么是锁

SQL Server 2000使用锁来实现多用户同时修改数据库同一数据时的同步控制。

死锁

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

锁的类型

一个数据库系统在许多情况下都有可能锁数据项。其可能性包括：

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-Performance.com
文章链接：Advanced SQL Server Locking

Advanced SQL Server Locking

by Andrés Taylor
8 December 2003

I thought I knew SQL Server pretty well. I've been using the product for more than 6 years now, and I like to know my tools from the inside out. ......