深入理解 SQL Server 2008 的锁机制.docx

相比于 SQL Server 2005（比如快照隔离和改进的锁与死锁监视），SQL Server 2008 并没有在锁的行为和特性上做出任何重大改变。SQL Server 2008 引入的一个主要新特性是在表级控制锁升级行为的能力。新的LOCK_ESCALATION表选项允许你启用或禁用表级锁升级。这个新特性能够减少锁竞争并且改善并发性，特别是对于分区表（partitioned tables）。SQL Server 2008 的另一个改变是不再支持Locks configuration设定。同样不再被支持的还有timestamp数据类型，它已被rowversion数据类型取代。为什么需要锁？ 在任何多用户的数据库中，必须有一套用于数据修改的一致的规则。对于真正的事务处理型数据库，当两个不同的进程试图同时修改同一份数据时，数据库管理系统（DBMS）负责解决它们之间潜在的冲突。任何关系数据库必须支持事务的ACID属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、永久性（Durability）。ACID属性确保数据库中的数据更改被正确地收集到一起，并且数据将保持在与所采取动作相一致的状态。锁的主要作用是提供事务所需的隔离。隔离确保事务之间不会相互干扰，即，一个给定的事务不会读取或修改正在被另一个事务修改的数据。此外，锁提供的隔离性有助于保证事务间的一致性。没有锁，一致的事务处理是不可能的。SQL Server 中的事务隔离级别 隔离级别决定了一个事务中正被访问或修改的数据受保护并免于被他事务修改的程度。理论上，每个事务都应该完全与其他事务隔离开来。然而，出于可行性和性能方面的原因，实践中这几乎是不可能做到的。在并发环境中如果没有锁和隔离，可能发生以下4种情况： 丢失更新 - 在这种情况下，事务与事务之间没有隔离。多个事务能够读取同一份数据并且修改它。最后对数据集做出修改的事务将胜出，而其他所有事务所作的修改都丢失了。 脏读 - 在这种情况下，一个事务能够读取正被其他事务修改的数据。被第一个事务读取的数据是不一致的，因为另一个事务可能会回滚所作的修改。 不可重复读 - 这种情况有点类似于没有任何隔离，一个事务两次读取数据，但是在第二次读取发生前，另一个事务修改了该数据；因此，两次读取所得到的结果是不同的。因为读操作不能保证每次都是课重复进行的，这种情况被称作“不可重复读”。 幻读 - 这种情况类似于不可重复读。然而，不是先前被读取的实际行在事务完成前发生了改变，而是额外的行被添加到了表中，导致第二次读取返回了不同的行集合。 SQL Server 2008 支持6种隔离级别，分别是 Read Uncommitted Read Committed Repeatable Read Serializable Snapshot Read Committed Snapshot （详情请参考我的另一篇blog：SQL Server 2008 R2 事务与隔离级别实例讲解）锁管理器 解决不同用户进程间锁冲突的职责落到了SQL Server Lock Manager身上。SQL Server 自动地给进程分配锁，以保证资源的当前用户拥有该资源的一致视图，从某个特定操作的开始至结束。Lock Manager 负责决定适当的锁类型（如shared, exclusive, update）和锁粒度（如row, page,table），根据正在执行的操作类型和所影响的数据量。Lock Manager还管理试图访问同一资源的锁类型之间的兼容性，解决死锁，必要时升级锁到一个更高的级别。Lock Manager 为共享数据和内部系统资源管理锁。对于共享数据，Lock Manager 管理表以及数据页、文本页、叶级索引页上的行级锁、页级锁和表级锁。内部地，Lock Manager使用门闩（latch）来管理索引行和页上的锁控制对内部数据结构的访问，以及在某些情况下，用于取回单个的数据行。门闩提供了更好的系统性能，因为它不像锁那般资源密集。门闩也提供了比锁更好的并发性。门闩典型地用于像页拆分、索引行的删除、索引中行的移动等操作。锁与门闩之间最主要的区别在于，锁在整个事务存续期间都被持有，而门闩仅在需要它的操作存续期间被持有。锁用于保证数据的逻辑一致性，而门闩用于保证数据和数据结构的物理一致性。SQL Server 锁类型 锁在SQL Server中是自动处理的。Lock Manager 基于事务类型（如SELECT, INSERT, UPDATE, 或者DELETE)选择锁的类型.Lock Manager使用以下的锁类型： 共享锁 更新锁 独占锁 意向锁 架构锁 大容量更新锁 除了选择锁类型，Lock Manager还基于所执行语句的性质以及所影响的行数自动地调整锁粒度（如row, page, table）。共享锁 缺省地，SQL Server 为所有读操作应用共享锁。顾名思义，共享锁不是独占的。理论上，在任何时刻，一个资源上可以持有无限数量的共享锁。此外，默认情况下，一个进程仅仅当资源正被读取期间才会锁定该资源，这时也只有唯一的共享锁存在。比如SELECT * from authors，当查询开始时，先锁定authors表中的第一行；当第一行被读取以后，它上面的锁被释放，并且了第二行上的锁；第二行读到以后，它上面的锁被释放，同时获取了第三行上的锁；以此类推。按此方式，一个SELECT查询允许在读操作期间修改那些没有正在被读取的数据行。这增强了数据访问的并发性。共享锁不仅与其他共享锁兼容，也与更新锁兼容。共享锁不会阻止其他进程在一个给定的行或页上获取额外的共享锁或更新锁。任何时候事务多个事务或进程可以持有多个共享锁，这些事务不会影响数据的一致性。然而，共享锁确实会阻止独占锁的获取。当行或页上持有共享锁的时候，任何试图修改其数据的事务将被阻塞，直到 所有的共享锁被释放。更新锁 更新锁用于锁定用户进程想要修改的行或页。当一个事务试图修改某行时，它必须先读取该行以确保它正在修改合适的记录。假如事务先在资源上加了共享锁，要修改该记录，最终它将需要获取该资源上的独占锁，以防止任何其他事务修改同一记录。问题是，当多个事务试图同时修改同一资源的时候这可能导致死锁。如图所示。SQL Server中的更新锁就是用来防止此类死锁场景的。更新锁是部分独占的，就是说在任何时候任何资源上只能获取唯一的更新锁。然而，更新锁兼容于共享锁，即它们可以同时被同一资所获取。事实上，更新锁意味着一个进程想要修改某记录，并且将也想修改该记录的其他进程排除在外。然而，更新锁允许其他进程获取共享锁以便读取数据，直到UPDATE或DELETE语句完成被影响记录的定位。之后，进程尝试将每一个更新锁升级为独占锁。这时候，进程等待该记录上当前被持有的所有共享锁释放。当共享锁全部释放以后，共享锁就被升级为独占锁。接着执行数据修改，独占锁在事务的余下时间内一直被持有。独占锁 如前所述，当事务准备好要修改数据时，独占锁被分配给它。资源上的独占锁确保没有其他任何事务能妨碍被持有独占锁的事务锁定的数据。SQL Server在事务结束时释放独占锁。独占锁与其他的所类型不兼容。如果资源持有了独占锁，那么任何其他进程对该资源的读取或修改请求都将强制等待直到独占锁释放为止。同样地，如果其他进程当前持有该资源的读取锁（共享锁或更新锁），独占锁请求也被强制排队等待直到资源变得可用为止。意向锁 意向锁并不正真的构成一种锁定方式，而是充当一种机制，用以在较高的粒度级别上指示在较低（粒度）级别上所持有的锁类型。有3种类型的意向锁（分别对应于之前提到的3种锁类型）：共享意向锁、独占意向锁、更新意向锁。举个例子来说，某进程持有的表级共享意向锁意味着，该进程当前在该表的行或页级持有共享锁。意向锁的存在防止其他事务获取与现存的行或页级锁不兼容的表级锁的企图。意向锁提升了SQL Server锁的性能。它允许在表级别检查锁来决定在该表的行或页级持有的锁类型，而不是在表中的行或页级查遍多个锁。当监视锁活动时典型地你将看到3种类型的意向锁：意向共享锁（IS）、意向独占锁（IX）、意向独占共享锁（SIX）。IS锁表明，在低级别资源（行或页）上，进程当前持有或有意图持有共享锁。IX锁表明，在低级别资源上，进程当前持有或有意图持有独占锁。SIX锁出现在特殊情况下，当一个事务在资源上持有共享锁，后来又需要意向独占锁（IX），这时候，S锁被转换成SIX锁。架构锁 SQL Server 使用架构锁来保持表结构的完整性。不像其他提供数据隔离的锁类型，架构锁提供事务中对数据库对象如表、视图、索引的schema隔离。Lock Manager提供2种类型的架构锁：架构稳定性锁（Sch-S）- 当事务引用了索引或数据页时，SQL Server在对象上加Sch-S锁。这确保当其他进程仍然引用着该对象时，没有其他事务能够修改该对象的Schema，如删除索引或删除、修改存储过程或表。架构修改锁（Sch-M） - 当一个进程需要修改某对象的结构（如修改表，重编译存储过程）时， Lock Manager在对象上加Sch-M锁。在锁存在期间，没有其他任何事务能够引用该对象，直到（对象结构的）修改完成并提交为止。大容量更新锁（BU） 大容量更新锁是一种特殊类型的锁，仅用于使用bcp实用程序或者BULK INSERT命令向表中大容量复制数据时。仅仅当给bcp或BULK INSERT命令指定了TABLOCK提示，或者使用 sp_tableoption 设置了 table lock on bulk load 表选项时,BU锁才能用于大容量数据复制操作。大容量更新 (BU) 锁允许多个 bulk copy 进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。如果有任何其他进程在该表上持有锁，则不能给该表施加BU锁。SQL Server 锁粒度 所谓所粒度，从本质上说就是，为了给事务提供完全的隔离和序列化，作为查询或更新的一部分被锁定的数据的总量（的大小）。Lock Manager需要在资源的并发访问与维护大量低级别锁的管理开销之间取得平衡。比如，锁的粒度越小，能够同时访问同一张表的并发用户的数量就越大，不过维护这些锁的管理开销也越大。锁的粒度越大，管理锁需要的开销就越少，而并发性也降低了。下图说明了锁的大小与并发性之间的权衡取舍。当前，SQL Server通过在行或更高级别加锁来平衡性能和并发性。基于各种因素，如key的分布，行的数量，行的密度，查询参数（SARGs）等等，Query Optimizer内部地做出锁粒度选择，程序员不需要为此担心。SQL Server提供了大量T_SQL扩展，使你能从锁的角度来更好地控制查询行为。SQL Server 提供以下的锁级别: DATABASE - 无论何时当一个SQL Server 进程正在使用除master以外的数据库时，Lock Manager为该进程授予数据库级的锁。数据库级的锁总是共享锁，用于跟踪何时数据库在使用中，以防其他进程删除该数据库，将数据库置为脱机，或者恢复数据库。注意，由于master和tempdb数据库不能被删除或置为脱机，所以不需要在它们之上加锁。 FILE - 文件级的锁用于锁定数据库文件。 EXTENT - Extent锁用于锁定extents,通常仅在空间分配和重新分配的时候使用。一个extent由8个连续的数据页或索引页组成。Extent锁可以是共享锁也可以是独占锁。 ALLOCATION_UNIT - 使用在数据库分配单元上。 TABLE - 这种级别的锁将锁定整个表，包括数据和索引。何时将获得表级锁的例子包括在Serializable隔离级别下从包含大量数据的表中选取所有的行，以及在表上执行不带过滤条件的update或delete。 Heap or B-Tree (HOBT) - 用于堆数据页，或者索引的二叉树结构。 PAGE - 使用页级锁，由8KB数据或者索引信息组成的整个页被锁定。当需要读取一页的所有行或者需要执行页级别的维护如页拆分后更新页指针时，将会获取页级锁。 Row ID (RID) - 使用RID锁，页内的单一行被锁定。无论何时当提供最大化的资源并发性访问是有效并且可能时，将获得RID锁。 KEY - SQL Server使用两种类型的Key锁。其中一个的使用取决于当前会话的锁隔离级别。对于运行于Read Committed 或者 Repeatable Read 隔离模式下的事务，SQL Server 锁定与被访问的行相关联的的实际索引key。（如果是表的聚集索引，数据行位于索引的叶级。行上在这些你看到的是Key锁而不是行级锁。）若在Serializable隔离模式下，通过锁定一定范围的key值从而不允许新的行插入到该范围内，SQL Server防止了“幻读”。这些锁因而被称作“key-range lock”。 METADATA - 用于锁定系统目录信息（元数据）。 APPLICATION - 允许用户定义他们自己的锁，指定资源名称、锁模式、所有者、timeout间隔。 Serialization 与 Key-Range Locking 如前所述， SQL Server 通过key-range锁防止了“幻读”。下面将介绍key-range锁如何与各种锁模式一起工作。Key-Range Locking for a Range Search 在涉及范围查找的key-range锁的情况下，SQL Server 在查询的WHERE子句所包含的数据范围的索引页上加锁。（对于聚集索引，则是对表中的实际数据行加锁。）因为该区间被锁定了，不允许其他事务往那个区间内插入新的行。如下图所示。Key-Range Locking When Searching Nonexistent Rows 在涉及此种类型的锁的情况下，如果事务试图删除或读取数据库中不存在的行，那么在该事务的以后阶段，该查询也不应该找到任何行。如下图所示。行级锁与页级锁之比较 行级锁是否优于页级锁的的争论持续了多年，在某些圈子里至今仍在继续。许多人坚持认为如果数据库和应用程序经过良好的设计和优化，行级锁是不必要的。这种观点诞生于行级锁甚至还不存在的时候。（在SQL Server 7.0 之前，能够锁定的最小数据单元是页。）然而，那时候SQL Server 中页的大小只有2KB。随着页大小扩大到8KB，单个页中能够包含更多数量的行（是先前的4倍）。8KB页上的锁可能导致更多的页级竞争，因为不同进程请求同一个页上数据行的可能性变得更大了。使用行级锁将增加数据访问的可并发性。另一方面，行级锁比页级锁占用更多的资源（内存和CPU），因为表中的行比页数量更多。如果进程需要访问页上的所有行，锁定整个页比每行获取一个锁更加高效。这将减少Lock Manager需要管理的内存中锁结构的数量。哪一个更优 - 更好的并发性还是较低的管理开销？如前所述，这二者间需要平衡。当锁的粒度变小，并发性就会得到提升，但性能会因额外的开销而降低。随着锁粒度变大，性能因管理开销的降低而得到提升，但是并发性降低了。取决于应用程序、数据库设计和数据（量的大小），行级锁与页级锁哪个更合适得具体分析。SQL Server 在运行时自动地做出决一开始是锁定行、页还是整个表，基于查询的性质、表的大小、预计被影响的行的数量。一般地，SQL Server 更经常地尝试先应用行级锁而非页级锁，以便提供最佳的并发性。今天有了更快速的CPU和更大内存的支持，行级锁的管理开销不再像过去那样昂贵。然而，当查询进程和实际被锁定的资源数量超过一定的阀值，SQL Server可能会尝试从低级别锁升级至适当的更高级别。锁竞争与死锁 SQL Server应用程序性能问题的最可能的原因是糟糕的查询语句、糟糕的数据库和索引设计、以及锁竞争。前2个问题无论系统的用户多少都会导致糟糕的应用程序性能；而锁竞争导致的性能问题随着用户数量的增加而显现出来，随着事务越来越复杂或者运行时间越来越长而更加趋于复杂化。当一个事务请求的锁类型与该资源上现存的锁类型不兼容时，锁竞争就发生了。默认地，进程无限期地等待锁资源变得可用。如果客户端应用程序中来自 SQL Server 的响应明显不足，你应该警惕锁竞争（问题）。下图演示了一个锁竞争的例子。设置锁超时间隔 如果你不想让进程无限期等待锁变得可用， SQL Server 允许你使用SET LOCK_TIMEOUT命令设定锁超时间隔。你以毫秒为单位指定超时间隔。比如，如果你想让进程在锁变得可用前仅等待5秒，那么执行以下命令SET LOCK_TIMEOUT 5000如果请求锁资源超时的话，语句将会中止，你将得到以下Error Message：Server: Msg 1222, Level 16, State 52, Line 1Lock request time out period exceeded.查看当前 LOCK_TIMEOUT 设置，可以使用系统函数lock_timeout。select lock_timeout如果你希望当不能获得锁时进程立即中止，则 setLOCK_TIMEOUT 0如果你想要将timeout重新置为无限期，则 setLOCK_TIMEOUT -1最小化锁竞争 为了最大化并发性和应用程序性能，你应该尽可能最小化进程间的锁竞争。下面是一些一般性指导原则：尽可能然事务保持运行时间短和简洁。事务持有锁的时间越短，锁竞争发生的机会就越少；将不是事务所管理的工作单元锁必需的命令移出事务。将组成事务的语句作为一个的单独的批命令处理，以消除 BEGIN TRAN 和 COMMIT TRAN 语句之间的网络延迟造成的不必要的延迟。考虑完全地使用存储过程编写事务代码。典型地，存储过程比批命令运行更快。在游标中尽可早地Commit更新。因为游标处理比面向集合的处理慢得多，因此导致锁被持有的时间更久。使用每个进程所需的最低级别的锁隔离。比如说，如果脏读是可接受的并且不要求结果必须精确，那么可以考虑使用事务隔离级别0（Read Uncommitted）,仅在绝对必要时才使用Repeatable Read or Serializable隔离级别。在 BEGIN TRAN 和 COMMIT TRAN 语句之间，绝不允许用户交互，因为这样做可能锁被持有无限期的时间。最小化表中的“热点”。当表中的大多数Update活动发生在少量的页中时，热点出现了。死锁 当两个进程各自都在等在对方当前锁定的资源时，死锁就发生了。两个进程在获得所请求资源上的锁之前既不能前进，也不能释放当前持有的锁。SQL Server 中可能发生2种类型的死锁： 循环死锁 - 两个进程请求不同资源上的锁，每一个进程都需要对方持有的该资源上的锁，这时将发生循环死锁。如下图。 转换死锁 - 两个或多个进程都在事务中持有同一资源上的共享锁，并且都想把它升级为独占锁，但是，谁也没法升级直到其他的进程释放共享锁。 如图所示。 人们经常以为死锁发生在数据页级或数据行级。事实上，死锁经常发生在索引页级或索引键级。下图展示了由于索引键级的竞争引发的死锁场景。SQL Server自动地侦测何时死锁情况发生。SQL Server 中一个独立的进程叫做LOCK_MONITOR，大约每5秒钟检查一次系统是否存在死锁。避免死锁 遵循前文给出的最小化锁竞争指导原则，有助于消除死锁。此外，当设计应用程序是你还需要遵循下列指导原则：按照一致的顺序访问多个表的数据以避免循环死锁。最小化HOLDLOCK的使用，或者最小化运行于Repeatable Read 或者 Serializable 隔离模式下的查询。这将有助于避免转换死锁。明智而审慎地选择事物隔离级别。选择较低的隔离级别或许能减少死锁。Table Hints for Locking 前面提到过，你可以使用SET TRANSACTION ISOLATION LEVEL 命令为连接设置隔离级别。该命令为整个会话设定了全局的隔离级别，如果你想要为应用程序提供一致的隔离级别，这很有用。然而，有时候你也想要许为特定的查询或者单个查询中的不同表指定不同的隔离级别。SQL Server 允许你在 SELECT, MERGE, UPDATE, INSERT, 和 DELETE 语句中使用表提示来实现此目的。这样一来，你在会话级别改变了当前的隔离级别。用于改变表级锁隔离、粒度或者锁类型的表提示，通过 SELECT, UPDATE, INSERT, 和 DELETE 语句的 WITH 操作符提供。注意： 尽管许多表提示是可以组合使用的，但是，你不能一次在一个表上组合超过一个隔离级别或者锁粒度的提示。另外，NOLOCK, READUNCOMMITTED, 和 READPAST 提示不能用于 INSERT, UPDATE, MERGE, 或 DELETE 语句的目标表上。Transaction IsolationLevel Hints SQL Server 提供了许多提示用于在查询中改变默认的事务隔离级别。 HOLDLOCK - 在语句执行期间，或者在整个事务期间（如果语句在事务中的话）保持共享锁。该选项等同于Serializable 隔离级别。 NOLOCK - 使用此选项指定不对资源施加共享锁。它类似于在0隔离级别（Read Uncommitted）下运行查询。NOLOCK选项在对结果精度要求不严格的报表工作环境下很有用。 READUNCOMMITTED - 与指定 Read Uncommitted 隔离级别和NOLOCK提示完全一样。 READCOMMITTED - 与指定 Read Committed 隔离级别一样。 READCOMMITTEDLOCK - 当数据被读取时获得共享锁，读取完成时释放共享锁，不管是否设定了 READ_COMMITTED_SNAPSHOT 隔离级别。 REPEATABLEREAD - 与指定 Repeatable Read 隔离级别一样，类似于HOLDLOCK提示。 SERIALIZABLE - 与指定 Serializable 隔离级别一样，类似于HOLDLOCK提示。 READPAST - 让查询忽略被其他事务锁定的行或页，仅返回能够被读取的数据。只能用在运行于Read Committed 或 Repeatable Read 隔离级别下的事务中。 Lock Granularity Hints 用于改变锁粒度： ROWLOCK - 强制 Lock Manager 在资源上施加行级锁而非页级锁或表级锁。 PAGLOCK - 强制 Lock Manager 在资源上施加页级锁而非行级锁或表级锁。 TABLOCK - 强制 Lock Manager 在资源上施加表级锁而非行级锁或页级锁。 TABLOCKX - 强制 Lock Manager 在资源上施加表级独占锁而非行级锁或页级锁。 Lock Type Hints 用于改变SQL Server 使用的锁类型： UPDLOCK - 类似于HOLDLOCK，不过HOLDLOCK在资源上应用共享锁，而UPDLOCK是在事务期间应用更新锁。 XLOCK - 在事务期间在资源上应用独占锁。它阻止其他事务获取该资源上的锁。 乐观锁 许多应用程序中，客户端需要读取数据用于浏览，然后修改其中的一些行并将修改提交回SQL Server 数据库。读取数据和提交更改后的数据之间的时间间隔可能很长（假如用户读取数据后去吃午饭了）。在这类应用程序中，你不愿使用如SERIALIZABLE或HOLDLOCK锁模式来锁定数据，因为从用户读取数据到提交更新的期间，没有人能更改它。这违背了最小化锁竞争和死锁的原则-不允许事务中的用户交互。在多用户的OLTP环境下，由于所阻塞和锁竞争，无限期持有共享锁将对并发性和应用的整体性能有重大影响。另一方面，如果不在被读取的行