开发一部系列培训-数据库并发控制.ppt
开发一部系列培训数据库并开发一部系列培训数据库并发访问控制发访问控制提 纲数据库并发访问控制机制如何提高数据库的并发处理能力数据库并发访问控制机制保持数据库一致性(C)的必要条件通过在要访问的数据库对象上加锁来控制数据库对象的访问数据库并发访问控制机制锁粒度行（ROW）块（BLOCK）（仅针对MDC表）数据分区（DATA PARTITION）（仅针对Range Partition表）表（TABLE）表空间（TABLESPACE）缓冲池（BUFFERPOOL）数据库并发访问控制机制锁尺寸因操作系统平台和目标对象上是否已经加锁而不同在32位操作系统平台上72Byte(目标对象上的首个锁)36Byte(非目标对象上的首个锁)在64位操作系统平台上112Byte(目标对象上的首个锁)56Byte(非目标对象上的首个锁)数据库并发访问控制机制锁模式IN（Intent None）：加锁者可以读取目标表上的任何数据（包括未提交的数据），但不能更新。其他的并发应用可以读取或者更新该表的数据。锁定的对象可能为：块、数据分区、表、表空间IS（Intent share）：加锁者可以读取锁定目标表中的数据，但不能更新。其他的并发应用可以读取或者更新该表的数据。锁定的对象可能为：块、数据分区、表、表空间NS（Next key share）：加锁者和所有其他的并发应用可以读但不能更新锁定行。在应用的隔离级别是RS或者CS的地方，会在表行上申请NS锁代替S锁，NS锁模式不再用作下一键锁定，为了最小化CS或RS扫描时下一键锁定的影响，通常使用NS锁代替S锁。锁定的对象只能为行。S（share）：加锁者和所有其他的并发应用可以读但不能更新锁定行。锁定对象可能为：行、块、数据分区、表IX（Intent exclusive）：加锁者和所有其他的并发应用都可以读或者更新目标表中的数据。锁定对象可能为：块、数据分区、表、表空间数据库并发访问控制机制锁模式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相同。数据库并发访问控制机制锁模式Z（Super Exclusive）：只有在一定的条件下才会申请该锁，比如当执行alter/drop表命令时，或者执行create/drop索引命令时，或者执行某种特殊类型的REORG操作时。其他并发应用不可以任何形式访问该表的数据。锁定对象可能为：数据分区、表、表空间。数据库的并发访问控制机制State Being RequestednonenoneININISISNSNSS SIXIXSIXSIXU UX XZ ZNWNWW WnoneyesyesyesyesyesyesyesyesyesyesyesyesINyesyesyesyesyesyesyesyesyesnoyesyesISyesyesyesyesyesyesyesyesnonononoNSyesyesyesyesyesnonoyesnonoyesnoSyesyesyesyesyesnonoyesnonononoIXyesyesyesnonoyesnonononononoSIXyesyesyesnononononononononoUyesyesyesyesyesnononononononoXyesyesnonononononononononoZyesnononononononononononoNWyesyesnoyesnononononononoyesWyesyesnonononononononoyesno锁兼容性数据库的并发访问控制机制锁转换从低级别的锁模式转换到较高级别的锁模式由同一应用进行申请一个应用在同一时刻在一个对象上只能持有一种模式的锁比如：更新过程中由U锁转换为X锁数据库的并发访问控制机制锁升级当锁资源占用的内存总量=locklist*maxlocks/100时会引起锁升级较细锁定粒度的锁升级到更粗粒度的锁直至锁资源占用的内存量locklist*maxlocks/100锁升级极大地扩充了数据的锁定范围，极易产生锁等乃至死锁数据库并发访问控制机制决定锁定粒度与锁定范围的主要因素应用预编译或者绑定到数据库时使用的隔离级别Uncommitted Read(UR)不加锁，可以读取脏数据Cursor Stability(CS)只对当前扫描到的数据行加临时锁Read Stability(RS)对扫描过的数据行加临时锁，对满足查询条件的记录加持久锁Repeatable Read(RR)对扫描过的数据行加临时锁，对满足查询条件的记录以及紧邻查询条件的记录加持久锁优化器选择的执行计划IXSCAN/TBSCANNLJOIN/HSJOIN/MSJOIN表定义时指定的LOCKSIZE属性LOCKLIST*MAXLOCKS/100的值数据库并发访问控制机制影响数据库并发的罪魁祸首锁等一般锁等（等待时间locktimeout）死锁（并发执行的两个或者多个事务之间互相等待或者级联等待）提 纲数据库并发访问控制机制如何提高数据库的并发处理能力如何提高数据库的并发处理能力在保证数据正确性的前提下采用尽可能低的隔离级别WAS上的应用默认隔离级别为RS，如有特殊需求，可以调整默认隔离级别或者在SQL语句上添加WITH语句指定隔离级别存储过程使用绑定选项确定隔离级别缺省情况下使用注册变量DB2_SQLROUTINE_PREPOPTS指定的隔离级别存储过程内部的SQL可以通过在语句级添加WITH语句执行隔离级别如何提高数据库的并发处理能力减少锁定时间使用小事务使锁定级别高的语句尽可能靠近事务结束语句使用乐观锁（从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）如何提高数据库的并发处理能力乐观锁的例子Create table t(i_id int,i_cola int,ts_update timestamp not null generated always for each row on update as row change timestampInsert 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_updateUpdate 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方式，其他事务引起的同页面上数据的修改会导致更新失败如何提高数据库的并发处理能力通过设置如下注册变量启用延迟锁定DB2SET DB2_SKIPINSERTED=ONDB2SET DB2_SKIPDELETED=ONDB2SET DB2_EVALUNCOMMITTED=ON注：DB29.5之前，以上注册变量的缺省值为OFF,从9.5开始，默认开启延迟锁定。如何提高数据库的并发处理能力启用延迟锁定的影响如何提高数据库的并发处理能力启用CUR_COMMIT(9.7开始支持)读取当前已落实的数据（只对配置参数更新后产生的数据访问有效）与WITH UR的相同点都不会对目标数据对象加锁与WITH UR的区别With ur会读取到脏数据，但启用CUR_COMMIT后读取到的数据都是已经提交的数据启用CUR_COMMIT后会占用更多的日志空间如何提高数据库的并发处理能力为SQL量身定做索引，引导优化器选择索引路径以WHERE条件中区分度大的列为前导列建立索引当数据量变化幅度较小时通过执行RUNSTATS收集准确的统计信息（db2look）必要的情况下导入虚假的统计信息（上线初期，数据量变化幅度很大时需要）对数据量变化幅度很大的表启用volatile属性如何提高数据库的并发处理能力通过HINT方式为表关联操作选择NLJOIN、IXSCAN的执行路径HSJOIN/MSJOIN的处理机制决定了这两种关联方式必须经历TBSCAN的过程db2set DB2_OPTPROFILE=yesselect count(*)from(select t.i_cola,t3.i_cola from T inner join T3 on t.i_cola=t3.i_cola)/*/;如何提高数据库的并发处理能力如果没有特殊需要，使用缺省的locksize（row）如何提高数据库的并发处理能力如果内存充足，将LOCKLIST*MAXLOCKS/100设置的足够大Q/A
