开发一部系列培训数据库并发控制

开发一部系列培训数据库并开发一部系列培训数据库并发访问控制发访问控制 2 提 纲 数据库并发访问控制机制 如何提高数据库的并发处理能力 3 数据库并发访问控制机制 保持数据库一致性(C)的必要条件 通过在要访问的数据库对象上加锁来控制数据库对象的访问 4 数据库并发访问控制机制 锁粒度 行（ROW） 块（BLOCK）（仅针对MDC表） 数据分区（DATA PARTITION）（仅针对Range Partition表） 表（TABLE） 表空间（TABLESPACE） 缓冲池（BUFFERPOOL） 5 数据库并发访问控制机制 锁尺寸 因操作系统平台和目标对象上是否已经加锁而不同 在32位操作系统平台上 72Byte(目标对象上的首个锁) 36Byte(非目标对象上的首个锁) 在64位操作系统平台上 112Byte(目标对象上的首个锁) 56Byte(非目标对象上的首个锁) 6 数据库并发访问控制机制 锁模式 IN（Intent None）：加锁者可以读取目标表上的任何数据（包括未提交的数据），但不能更新。其他的并发应用可以读取或者更新该表的数据。锁定的对象可能为：块、数据分区、表、表空间 IS（Intent share）：加锁者可以读取锁定目标表中的数据，但不能更新。其他的并发应用可以读取或者更新该表的数据。锁定的对象可能为：块、数据分区、表、表空间 NS（Next key share）：加锁者和所有其他的并发应用可以读但不能更新锁定行。在应用的隔离级别是RS或者CS的地方，会在表行上申请NS锁代替S锁，NS锁模式不再用作下一键锁定，为了最小化CS或RS扫描时下一键锁定的影响，通常使用NS锁代替S锁。锁定的对象只能为行。 S（share）：加锁者和所有其他的并发应用可以读但不能更新锁定行。锁定对象可能为：行、块、数据分区、表 IX（Intent exclusive）：加锁者和所有其他的并发应用都可以读或者更新目标表中的数据。锁定对象可能为：块、数据分区、表、表空间 7 数据库并发访问控制机制 锁模式 SIX（share with intent exclusive）：加锁者可以读取或者更新目标表的数据，其他并发应用只能读取目标表的数据。锁定对象可能为：块、数据分区、表。 U（Update）：加锁者可以更新目标表中锁定的数据，其他并发应用可以读取该锁定的数据，但不可更新。锁定对象可能为：行、块、数据分区、表。 NW（Next key weak exclusive）：当在索引中插入一行时，会在下一行上申请一个NW锁。对于type-2类型的索引而言，只有下一行正在被RR隔离级别的扫描锁定时才会发生。加锁者可以读取但不能更新锁定的内容，除了与W锁和NS锁兼容之外，该模式与X锁类似。锁定对象只能为行。 X（Exclusive）：加锁者可以读取或者更新锁定的数据，其他应用只能通过脏读的方式读取该锁定内容。锁定对象可能为：行、块、数据分区、表、缓冲池 W（Weak Exclusive）：当在一张未定义type-2类型索引的表上插入一行时，会申请该锁；在唯一索引中插入一行时也会申请该锁。加锁者可以修改锁定行的内容，其他应用只能通过脏读的方式访问锁定内容。锁定对象只能为行。除了与NW锁兼容之外，该模式的锁与X相同。 8 数据库并发访问控制机制 锁模式 Z（Super Exclusive）：只有在一定的条件下才会申请该锁，比如当执行alter/drop表命令时，或者执行create/drop索引命令时，或者执行某种特殊类型的REORG操作时。其他并发应用不可以任何形式访问该表的数据。锁定对象可能为： 数据分区、表、表空间。 9 数据库的并发访问控制机制 State Being Requested nonenone ININ ISIS NSNS S S IXIX SIXSIX U U X X Z Z NWNW W W none yes yes yes yes yes yes yes yes yes yes yes yes IN yes yes yes yes yes yes yes yes yes no yes yes IS yes yes yes yes yes yes yes yes no no no no NS yes yes yes yes yes no no yes no no yes no S yes yes yes yes yes no no yes no no no no IX yes yes yes no no yes no no no no no no SIX yes yes yes no no no no no no no no no U yes yes yes yes yes no no no no no no no X yes yes no no no no no no no no no no Z yes no no no no no no no no no no no NW yes yes no yes no no no no no no no yes W yes yes no no no no no no no no yes no 锁兼容性 10 数据库的并发访问控制机制 锁转换 从低级别的锁模式转换到较高级别的锁模式 由同一应用进行申请 一个应用在同一时刻在一个对象上只能持有一种模式的锁 比如：更新过程中由U锁转换为X锁 11 数据库的并发访问控制机制 锁升级 当锁资源占用的内存总量=locklist*maxlocks/100时会引起锁升级 较细锁定粒度的锁升级到更粗粒度的锁 直至锁资源占用的内存量locklist*maxlocks/100 锁升级极大地扩充了数据的锁定范围，极易产生锁等乃至死锁 12 数据库并发访问控制机制 决定锁定粒度与锁定范围的主要因素 应用预编译或者绑定到数据库时使用的隔离级别 Uncommitted Read(UR)不加锁，可以读取脏数据 Cursor Stability(CS)只对当前扫描到的数据行加临时锁 Read Stability(RS)对扫描过的数据行加临时锁，对满足查询条件的记录加持久锁 Repeatable Read(RR)对扫描过的数据行加临时锁，对满足查询条件的记录以及紧邻查询条件的记录加持久锁 优化器选择的执行计划 IXSCAN/TBSCAN NLJOIN/HSJOIN/MSJOIN 表定义时指定的LOCKSIZE属性 LOCKLIST*MAXLOCKS/100的值 13 数据库并发访问控制机制 影响数据库并发的罪魁祸首 锁等 一般锁等（等待时间locktimeout） 死锁（并发执行的两个或者多个事务之间互相等待或者级联等待） 14 提 纲 数据库并发访问控制机制 如何提高数据库的并发处理能力 15 如何提高数据库的并发处理能力 在保证数据正确性的前提下采用尽可能低的隔离级别 WAS上的应用默认隔离级别为RS，如有特殊需求，可以调整默认隔离级别或者在SQL语句上添加WITH语句指定隔离级别 存储过程使用绑定选项确定隔离级别 缺省情况下使用注册变量DB2_SQLROUTINE_PREPOPTS指定的隔离级别 存储过程内部的SQL可以通过在语句级添加WITH语句执行隔离级别 16 如何提高数据库的并发处理能力 减少锁定时间 使用小事务 使锁定级别高的语句尽可能靠近事务结束语句 使用乐观锁（从9.5版本开始，DB2为乐观锁实现提供了很好的支持） 通过在初始选择列表上使用RID_BIT/RID()和ROW CHANGE TOKEN或者ROW CHANGE TIMESTAMP表达式来支持乐观并发控制，以允许将原始值作为搜索条件检查数据是否已经发生变化 -ROW CHANGE TOKEN(BIGINT)按页生成，同一页面上的记录该取值相同 -ROW CHANGE TIMESTAMP(TIMESTAMP)按行生成，但需要在目标表上专门定义类型为TIMESTAMP的列（generated always for each row on update as row change timestamp） 17 如何提高数据库的并发处理能力 乐观锁的例子 Create table t(i_id int,i_cola int,ts_update timestamp not null generated always for each row on update as row change timestamp Insert into t(i_id,i_cola) values(1,1),(2,2),(3,3),(4,4),(5,5); 使用说明： Select rid(t),row change timestamp,i_id,i_cola for t from t where i_id=1; -记录下rid和row change timestamp的值，假设为vi_rid和vts_update Update t set i_cola=new_val where rid(t)=vi_rid and row change timestamp for t=vts_update; -几点注意： 1.Select和update之间如果执行reorg操作，ri发生变化会导致更新失败 2.如果使用row change token方式，其他事务引起的同页面上数据的修改会导致更新失败 18 如何提高数据库的并发处理能力 通过设置如下注册变量启用延迟锁定 DB2SET DB2_SKIPINSERTED=ON DB2SET DB2_SKIPDELETED=ON DB2SET DB2_EVALUNCOMMITTED=ON 注：DB29.5之前，以上注册变量的缺省值为OFF,从9.5开始，默认开启延迟锁定。 19 如何提高数据库的并发处理能力 启用延迟锁定的影响 DB2注册变量生效范围（生效范围（CS/RS）生效条件（启用）生效条件（启用）注册变量列表注册变量列表DB2_SKIPINSERTEDDB2_SKIPDELETEDDB2_EVALUNCOMMITED忽略任何未提交的插入不管此数据是否满足该事务查询条件跳过任何未提交的删除不管此数据是否满足该事务查询条件在可能的情况下，它将进行表或索引访问扫描以延迟或避免行锁定，直到知道数据记录满足谓词求值为止如果同时启用DB2_SKIPINSERTED,那么忽略未提交的插入如果同时启用DB2_SKIPDELETED,那么忽略未提交的删除如果其他未提交事务更新前满足该事务查询条件如果其他未提交事务更新后满足该事务查询条件，那么发生锁等如果同时启用DB2_SKIPDELETED,那么忽略该数据如果未启用DB2_SKIPDELETED,那么发生锁等DB2注册变量20 如何提高数据库的并发处理能力 启用CUR_COMMIT(9.7开始支持) 读取当前已落实的数据（只对配置参数更新后产生的数据访问有效） 与WITH UR的相同点 都不会对目标数据对象加锁 与WITH UR的区别 With ur会读取到脏数据，但启用CUR_COMMIT后读取到的数据都是已经提交的数据 启用CUR_COMMIT后会占用更多的日志空间 21 如何提高数据库的并发处理能力 为SQL量身定做索引，引导优化器选择索引路径 以WHERE条件中区分度大的列为前导列建立索引 当数据量变化幅度较小时通过执行RUNSTATS收集准确的统计信息（db2look） 必要的情况下导入虚假的统计信息（上线初期，数据量变化幅度很大时需要） 对数据量变化幅度很大的表启用volatile属性 22 如何提高数据库的并发处理能力 通过HINT方式为表关联操作选择NLJOIN、IXSCAN的执行路径 HSJOIN/MSJOIN的处理机制决定了这两种关联方式必须经历TBSCAN的过程 db2set DB2_OPTPROFILE=yes select count(*) from (select t.i_cola,t3.i_cola from T inner join T3 on t.i_cola=t3.i_cola) /* */; 23 如何提高数据库的并发处理能力 如果没有特殊需要，使用缺省的locksize（row） 24 如何提高数据库的并发处理能力 如果内存充足，将LOCKLIST*MAXLOCKS/100设置的足够大 25 Q/A