2008年04月12日 11:22:11
sqlserver事务处理全面解析一
|
1. 笔记
Read Committed与Repeatable Read的区别： 测试1： T1: begin tran set TRANSACTION ISOLATION LEVEL READ COMMITTED select * from ticket T2: update ticket set num =1 where name ='wuhan_To_beijing' select * from ticket commit tran 测试2： T1: begin tran set TRANSACTION ISOLATION LEVEL Repeatable Read select * from ticket T2: update ticket set num =1 where name ='wuhan_To_beijing' select * from ticket commit tran 执行T2的时候产生阻塞。 两次select的结果不一样，可重复读隔离级是为了避免在同一个事务中读出两个不同的值而设置的。为了达到这个目的，使查询语句获得的共享锁一直保持到事务结束。另外我们注意到同READ COMMITTED的区别是在活动状态它不是每一次获得RID、PAGE等锁后立即释放。 1.2. 幻像读 当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。 1.3. SQL Server 使用以下资源锁模式 锁模式 描述 1.3.1. 共享 (S) ： 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。共享 (S) 锁允许其它并发事务读取 SELECT 一个资源（其它相容锁见附件锁模式相容性）。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或可串行化，或者在事务生存周期内用锁定提示保留共享 (S) 锁。 1.3.2. 更新 (U) ： 用于可更新的资源中。防止多个事务同时读，而使锁定以及随后可能进行的资源更新时发生常见形式的死锁。更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。 (UPDLOCK ) 1.3.3. 排它 (X) ： 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。 1.3.4. 意向： 用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。 意向共享 (IS) ： 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。 意向排它 (IX) ： 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。 与意向排它共享 (SIX)：通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置一个 SIX 锁（允许并发 IS 锁），在当前所修改页上放置 IX 锁（在已修改行上放置 X 锁）。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。 1.3.5. 架构： 在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。 执行表的数据定义语言 (DDL) 操作（例如添加列或除去表）时使用架构修改 (Sch-M) 锁。当编译查询时，使用架构稳定性 (Sch-S) 锁。架构稳定性 (Sch-S) 锁不阻塞任何事务锁，包括排它 (X) 锁。因此在编译查询时，其它事务（包括在表上有排它 (X) 锁的事务）都能继续运行。但不能在表上执行 DDL 操作。 1.3.6. 大容量更新 (BU) ： 向表中大容量复制数据并指定了 TABLOCK 提示时使用。当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新 (BU) 锁。大容量更新 (BU) 锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。 1.3.7. 备份锁： 备份数据库时在整个数据库中加的锁。备份锁类型：BULK-OP-DB、BULK-OP-LOG。 1.3.8. 锁兼容性
架构修改 (Sch-M) 锁与所有锁模式都不兼容。 大容量更新 (BU) 锁只与架构稳定性 (Sch-S) 锁及其它大容量更新 (BU) 锁相兼容。 还有一些锁一直没有搞明白： 1.3.9. 键范围锁定 键范围锁定解决了幻像读并发问题，并支持可串行事务。键范围锁覆盖单个记录以及记录之间的范围，可以防止对事务访问的记录集进行幻像插入或删除。键范围锁仅用于代表在可串行隔离级别上操作的事务。 可串行性要求如果任意一个查询在一个事务中后面的某一时刻再次执行，其所获取的行集应与该查询在同一事务中以前执行时所获得的行集相同。如果本查询试图提取的行不存在，则在试图访问该行的事务完成之前，其它事务不能插入该行。如果允许另一个事务插入该行，则它将以幻像出现。 如果另一个事务试图插入驻留在锁定数据页上的行，页级锁定可以防止添加幻像行，并维护可串行性。但是，如果该行要添加到未被第一个事务锁定的数据页，应设定锁定机制防止添加该行。 键范围锁通过覆盖索引行和索引行之间的范围来工作（而不是锁定整个基础表的行）。因为第二个事务在该范围内进行任何行插入、更新或删除操作时均需要修改索引，而键范围锁覆盖了索引项，所以在第一个事务完成之前会阻塞第二个事务的进行。 1.3.9.1. 键范围锁模式 键范围锁包括范围组件和行组件，按范围-行格式指定：
行表示保护索引项的锁模式。 模式表示使用的组合锁模式。键范围锁模式由两部分组成。第一部分表示用于锁定索引范围 (RangeT) 的锁类型，第二部分表示用于锁定特定键 (K) 的锁类型。这两部分用下划线 (_) 连接，如 RangeT_K。
说明 内部空锁模式与所有其它锁模式相兼容。 键范围锁模式有一个兼容性矩阵，表示哪些锁与在重叠键和范围上获取的其它锁兼容。
当键范围锁与其它锁重叠时，创建会话锁。
在不同的复杂环境下（有时是在运行并发进程时），可以在一小段时间内观察到会话锁。 1.3.9.3. 可串行范围扫描、单独提取、删除和插入 键范围锁定确保以下四种方案是可串行的：
单独提取不存在的行 删除操作 插入操作 然而，在可以发生键范围锁定之前，必须满足下列条件：
对数据执行的操作必须使用索引范围访问。只有当查询处理程序（如优化器）选择访问数据的索引路径时，才激活范围锁定。 为了确保范围扫描查询是可串行的，每次在同一事务中执行的相同查询应返回同样的结果。其它事务不能在范围扫描查询中插入新行；否则这些插入将成为幻像插入。例如，下面的查询使用前面插图中的表和索引： SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C' 键范围锁放置在与数据行范围（名称在值 Adam 和 Dale 之间的行）对应的索引项上，以防止添加或删除满足上述查询条件的新行。虽然此范围中的第一个名称是 Adam，但是此索引项的 RangeS_S 模式键范围锁确保了以字母 A 开头的新名称（如 Abigail）不能添加在 Adam 之前。同样，Dale 索引项的 RangeS_S 键范围锁确保了以字母 C 开头的新名称（如 Clive）不能添加在 Carlos 之后。 说明 控制的 RangeS_S 锁数量为 n+1，此处 n 是满足查询条件的行数。 1.3.9.5. 单独提取不存在数据 如果事务中的查询试图选择不存在的行，则以后在相同的事务中发出这一查询时，必须返回相同的结果。不允许其它事务插入不存在的行。例如，对于下面的查询： SELECT name FROM mytable WHERE name = 'Bill' 键范围锁放置在对应于名称范围 Ben 到 Bing 之间的索引项上，因为名称 Bill 将插入到这两个相邻的索引项之间。RangeS_S 模式键范围锁放置在索引项 Bing 上。这样可以防止任何其它事务在索引项 Ben 和 Bing 之间插入插入值（如 Bill）。 1.3.9.6. 删除操作 在事务中删除值时，在事务执行删除操作期间不必锁定值所属的范围。锁定删除的键值直至事务结束足以保持可串行性。例如，对于下面的 DELETE 语句： DELETE mytable WHERE name = 'Bob' 排它 (X) 锁放置在对应于名称 Bob 的索引项上。其它事务可以在删除值 Bob 的前后插入或删除值。但是任何试图读取、插入或删除值 Bob 的事务将被阻塞，直到删除的事务提交或回滚为止。 可以使用下列三种基本锁模式执行范围删除：行锁、页锁或表锁。页、表或行锁定策略由查询优化器确定，或者可以由用户通过优化程序提示（如 ROWLOCK、PAGLOCK 或 TABLOCK）指定。在使用页锁或表锁的情况下，SQL Server 立即释放包含已删除行的索引页，并假定从页中删除了所有行。相反，使用行锁时，所有删除的行只是被标记为已删除；以后通过后台任务从索引页中删除它们。 1.3.9.7. 插入操作 在事务中插入值时，在事务执行插入操作期间不必锁定值所属的范围。锁定插入的键值直至事务结束足以维护可串行性。例如，对于下面的 INSERT 语句： INSERT mytable VALUES ('Dan') RangeI_N 模式键范围锁放置在对应于名字 David 的索引项上以测试范围。如果已授权锁定，则插入 Dan，并且排它 (X) 锁放置在值 Dan 上。RangeI_N 模式键范围锁仅对测试范围必需，而不在执行插入操作的事务期间保留。其它事务可以在插入值 Dan 的前后插入或删除值。但是，任何试图读取、插入或删除值 Dan 的事务将被阻塞，直到插入的事务提交或回滚为止。 1.3.9.8. 自定义索引的锁定 在大多数情况下，Microsoft® SQL Server™ 2000 动态锁定策略自动选择查询的最佳锁定粒度。在访问模式很好理解且一致的情况下，限制索引可用的锁定级别是很有益的。 例如，数据库应用程序使用的查找表在批处理进程中每周进行刷新。最有效的锁定策略是关闭页和行锁定，并允许所有并发读取器获得表上的共享 (S) 锁以减少开销。在每周的批处理更新时，更新进程可以使用排它 (X) 锁，然后更新整个表。 可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度。若要显示给定索引的当前锁定选项，请使用 INDEXPROPERTY 函数。可以禁止将页级锁、行级锁或二者的组合用于指定的索引。
例如，当已知表是争夺点时，禁止页级锁从而只允许行级锁会有好处。或者如果总是使用表扫描来访问索引或表，那么通过只允许表级锁而禁止页级锁和行级锁将会很有帮助。 重要 SQL Server 查询优化器自动作出正确的决定。建议您不要替代优化器作出的选择。禁止锁定级别反过来会影响表或索引的并发。例如，在由许多用户频繁访问的大型表上只指定表级锁会严重影响性能。用户在访问表之前必须等待释放表级锁。
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
一共有 1 条评论
我的邮箱：books_522008@yahoo.com.cn