MS-SQL 锁机制.pptx

MS SQL锁机制 2011 11 21 概要 锁定的概述锁模式分类锁粒度锁的持续时间锁的所有权查看锁锁定实例 一 锁的概述 为什么要引入锁锁是任何多用户数据库系统的一个至关重要的功能 悲观和乐观并发模型都用到了锁 只不过两种模型中 其他进程处理被锁定数据的方式不同 多个用户同时对数据库的并发操作时会带来以下数据不一致的问题 丢失更新A B两个用户读同一数据并进行修改 其中一个用户的修改结果破坏了另一个修改的结果 2 脏读A用户修改了数据 随后B用户读取该数据 但A用户回滚事务 未提交 此时B用户的数据与数据库内的数据产生不一致 3 不可重复读A用户读取数据 随后B用户读出该数据并修改 此时A用户再读取数据时发现前后两次的不一致 并发控制的主要方法是封锁 锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致 二 锁模式 锁的类型 共享锁数据被读取时有SQLServer自动获得 可在表 页面 索引键或单个行上持有共享锁 通常 共享锁只要数据读取完毕就释放 但这会可以通过使用查询提示或不同的事务隔离级别而改变 独占锁当数据INSERT UPDATE或DELTE操作修改时 SQLServer自动获得数据上的独占锁 在一个特定的数据源上 同一时刻只有一个进程可以获得独占锁 如果一个进程独占地锁定了请求的数据源 那么别的进程无法再此数据源上获得任何种类的锁 独占锁一直持有到事务结束 更新锁更新锁实际上并不是一种独立的锁 而是共享锁和独占锁的混合 当SQLServer执行数据修改操作却首先需要搜索表以找到需要修改的资源时会获得更新锁 不要被名字误导 更新锁并不只是用于UPDATE操作 对任何需要在进行实际修改之前搜索数据的修改操作使用更新锁 这样操作包括 首先更新及删除 带有聚集索引的表上进行插入操作 4 意向锁意向锁的含义是如果对一个结点加意向锁 则说明该结点的下层结点正在被加锁 对任一结点加锁时 必须先对它的上层结点加意向锁 例如 对任一元组加锁时 必须先对它所在的关系加意向锁 于是 事务T要对关系R1加X锁时 系统只要检查根结点数据库和关系R1是否己加了不相容的锁 而不再需要搜索和检查尺中的每一个元组是否加了X锁 下面介绍三种常用的意向锁 意向共享锁 IntentShareLock 简称IS锁 意向排它锁 IntentExclusiveLock 简称IX锁 共享意向排它锁 ShareIntentExclusiveLock 简称SIX锁 类型 IS锁 如果对一个数据对象加IS锁 表示它的后裔结点拟 意向 加S锁 例如 要对某个元组加S锁 则要首先对关系和数据库加IS锁 IX锁 如果对一个数据对象加IX锁 表示它的后裔结点拟 意向 加X锁 例如 要对某个元组加X锁 则要首先对关系和数据库加IX锁 SIX锁 如果对一个数据对象加SIX锁 表示对它加S锁 再加IX锁 即SIX S IX 例如对某个表加SIX锁 则表示该事务要读整个表 所以要对该表加S锁 同时会 更新个别元组 所以要对该表加IX锁 5 特殊锁模式包含三种额外的锁模式 架构稳定锁 架构修改锁和大容量更新锁 当查询被编译时 架构稳定锁会纺织其他进程获取架构修改锁 在表结构被修改时获得 在执行BULKINSERT命令或者使用bcp实用工具导入数据时会获取大容量更新锁 6 转换锁转换锁不会由SQLServer直接请求 而是从有一种模式转换到另一种模式所造成的 SQLServer支持的3类转换锁是SIX SIU和UIX 其中最常见的是SIX 如果事务持有一个资源上的共享锁 S 然后又需要一个IX锁 此时出现的锁模式就是SIX 例如 假设发出一下批命令 SETTRANSACTIONISOLATIONLEVELREPEATABLEREADBEGINTRANSELECT FROMMAINUSER UPDATEMAINUSERSETIDCARD 123456 WHEREUSERID 1 如果表恨到 SELECT语句会获得一个共享表锁 UPDATE语句获得单个独占锁以执行单个行的更新 而键级别的X锁意味着在页和表级别上存在IX锁 当一个进程在一张表上拥有共享锁并在该表的一行上拥有更新锁时 就出现了SIU锁 而当一个进程在一张表上拥有更新锁并在一行上拥有独占锁时就出现了UIX锁 7 键范围锁只在可序列化隔离级别中为了锁定一定范围内的数据而被获取 键范围锁只能在键上获取 其他大多数锁模式能够应用于几乎任何锁资源上 例如 共享锁和独占锁可以在表 页 行或键上获取 锁模式简表 三 锁粒度 行锁RID行标识符 用于单独锁定表中的一行 2 键锁KEY索引中的行锁 用于保护可串行事务中的键范围 SQLServer中最佳级别的锁 3 页锁PAGE8千字节 KB 的数据页或索引页 SQLServer中目前为止最佳性能的锁 4 表锁TABLE包括所有数据和索引在内的整个表 SQLServer中最粗略的逻辑锁 5 数据库DB这是一种会话 Session 锁 例如 它不涉及任何事务 仅仅是一个用户和数据库之间的链接 纺织由用户链接到数据库时 该数据库被卸载了 表 页 页 页 行 行 行 行 行 行 行 行 行 行 行 行 表上的SQLServer锁的粒度级别图 Sys dm tran locks视图对每个锁都进行追踪 并包含关于资源的信息 谁被锁定了 如行 键 或页 锁的模式及特定资源的标识符 该视图只是用来显示的动态视图 关于锁的实际信息存储在内部的SQLServer结构中 对我们完全不可见 四 锁的持续时间 锁的持续时间主要取决与锁的模式和当前起作用的事务隔离级别 SQLServer的默认隔离级别是已提交读 在该级别下 SQLServer会在读取和处理完锁定数据以后立刻释放共享锁 对于快照隔离级别 其行为也是相同的 SQLServer读完数据后立即释放共享锁 如果事务隔离级别是可重复度或者可序列化 共享锁就和独占锁的持续时间相同 也就是说 直到事务结束时才会被释放 对于任何隔离级别 独占锁都要一直持续到事务结束为止 无论事务是被提交还是被回滚 更新锁也会持续到事务结束 除非他被升级成独占锁 这种情况下的独占锁跟所有情况下的独占锁一样 总会保留到事务结束为止 除了改变事务隔离级别 还可以使用查询提示来控制锁得持续时间 五 锁的所有权 锁的持续时间也受到锁的所有权的直接影响 锁的所有权与申请锁的进程没有关系 可以将其想象成锁的 作用域 一共存在4种锁所有者 或者作用域 类型 事务 游标 事务工作空间和会话 可以通过sys dm tran locks试图中的request owner type列来查看锁所有者 之前讨论的大多数锁定处理的锁 其所有者都是事务 正如我们所见 根据事务的隔离级别和锁定模式 这类所可以拥有两个不同的持续时间 已提交读的共享锁的持续时间只是被锁定数据被读取的时间 由事务所拥有的全部其他锁的持续时间要一直持续到事务结束为止 六 查看锁 为了弄清系统当前正处于活动和等待状态的锁 最佳信息来源是sys dm tran locks视图 这个视图取代了sp lock存储过程 使用dmv视图可以灵活查询 并且可以与其他的dmv视图关联查询出当前锁对应的SQL语句 Sys dm tran locks中的所有列 名为lock owner address的列除外 都是以两种前缀中的一种打头 以resource 打头的列描述的资源是SQLServer正在申请该资源上的锁 以request 打头的字段描述的是正在申请锁的进程 只有当所有的resource 列内容相同时 才表示两个申请作用于同一个资源上 Sys dm tran locks视图具体字段说明如下 控制到SQLServer的连接发出的Transact SQL语句的锁定行为和行版本控制行为 SETTRANSACTIONISOLATIONLEVEL READUNCOMMITTED指定语句可以读取已由其他事务修改但尚未提交的行 READCOMMITTED不能读取已由其他事务修改但尚未提交的数据 这样可以避免脏读 REPEATABLEREAD不能读取已由其他事务修改但尚未提交的行 并且指定 其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据 SNAPSHOT指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本 事务只能识别在其开始之前提交的数据修改 SERIALIZABLE请指定下列内容 语句不能读取已由其他事务修改但尚未提交的数据 任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据 在当前事务完成之前 其他事务不能使用当前事务中任何语句读取的键值插入新行 七 锁定实例 下面通过几个例子演示了之前讨论的许多锁定类型和锁模式输出结果 示例1 默认级别下的SELECT 示例2 RepeatableRead隔离级别下的SELECT由于MainUser表上存在聚集索引 数据行就是叶级别的全部索引行 返回的两个数据行上的键锁 如果是有非聚集索引的话从index id上可以区分开来 聚集索引1 非聚集索引3 示例3 可序列化隔离级别下的SELECT可序列化隔离级别下持有的锁几乎可重复读隔离级别下持有的完全一样 主要区别在锁定模式上 部分模式RangeS S表示 除了键本身上的锁之外 还有一个键范围锁 第一部分 RangeS 是加在介于持有锁的键和索引中的前一个键之间的键范围上的锁 示例4 更新操作聚集索引叶级别中的两个数据行被X锁锁定 页和表则被IX锁定 在寻找更新的行时 SQLServer实际上获得的是更新锁 然而 当实际的更新操作完成时 这些锁被升级成X锁 因此当我们查看V DBLocks视图的时候 更新锁已经不见了 除非通过查询提示符提示强制使用更新锁 否则可能永远都无法从V DBLocks视图的输出结果或者对sys dm tran locks的直接观察中看到它们 示例5 在可序列化隔离级别下使用索引进行更新再次注意 键范围锁存在于用来寻找相关数据行的非聚集索引上 范围隔离本身只需要一个共享锁来防止插入 但是被搜索的键上拥有U锁 所以其他进程都不能更新它们 当实际的修改发生时 表本身 index id 1 的键上会获得独占锁 示例6 在可序列化隔离级别下不使用索引进行更新这里的锁与上一个例子中的那些锁相似 只不过所有的锁都是存在于表本身上 Index id 1 必须执行聚集索引扫描 整张表 以便所有键最初都获得RangeS U锁 当4个数据行最终都被修改后 键上的锁就被转换成RangeX X锁 从上面的结果中可以看到全部的RangeX X锁 但是空间有限 没有列出所有的RangeS U锁 示例7 行锁newProducts表上没有索引 所以实际符合搜索标准的行 RID 上存在一个独占锁 X锁 对于RID锁 description列实际上是以 文件号 页码 槽号 的形式来表示特定的行 IX锁出现在页和表上 锁升级 SQLServer可以自动将行锁 键锁或页锁适当升级为表锁或分区锁 这种升级可以保护系统资源并提高效率 例如 在一个查询获得很多行锁之后 锁级别会被升级 因此获得并持有单个锁可能比持有很多行锁更有意义 发生锁升级时 较小单元 行或页 上的很多锁被释放 并被较大单元上的一个锁取代 由于可用于锁结构的内存数量是有限的 所以有时候 升级是很有必要的 可以确保用于锁的内存保持在合理的限度内 SQLServer中就默认为升级到表锁 SQL2008可以用ALTERTABLE语句升级到单个分区 ALTERTABLE的LOCK ESCALATION选项可以指定总是升级到表级别 或之地就能够为即可升级到表级别 也可以升级到分区级别 也可以用来完全阻止升级 示例 ALTERTABLETransactionHistorySET LOCK ESCALATION AUTO 锁升级发生在以下情形 单个语句在一个对象上或者对象的一个分区上持有的锁的数量超过阀值 该阀值当前是5000个锁 但是未来的服务包中可能会改变 如果同意语句持有的锁是加在在多个对象上时不会发生锁升级的 比如 3000个锁在一个索引上 另外3000个在另一个索引上 那么总共6000个锁 但不会升级 锁资源占用的内存超过非AWE 32 或常规 64位 已启用内存的40 并且锁配置选项设置为0 控制锁升级 锁升级可潜在地导致在对象上的行锁或页锁的事务未来对索引或堆的并发访问 在发出新请求时 SQLServer不能将锁进行降级 所以锁升级并不总是对所有应用程序都是一个好主意 可以通过ALTERTABLE来禁止单个表锁升级 ALTERTABLETransactionHistorySET LOCK ESCALATION AUTO 死锁 当两个进程在等待一个资源 又相互阻止对方获得该资源时 就会发生死锁 一个真正的死锁是Catch 22 不加干涉的话 无论哪个进程都不能前进 发生死锁时 SQLSe