您的位置:1010cc时时彩经典版 > 1010cc时时彩经典版 > A DB2 Performance Tuning Roadmap --DIVE INTO LOCK

A DB2 Performance Tuning Roadmap --DIVE INTO LOCK

发布时间:2019-08-18 16:56编辑:1010cc时时彩经典版浏览(164)

    A DB2 Performance Tuning Roadmap --DIVE INTO LOCK

    在重整了DB2 LOG相关内容的底蕴之上,这章整理lock的开始和结果,绝相比较与log,lock的从头到尾的经过更加多的与利用相关,涉及内容总体特别叶影参差。DBMS在本质上不相同于文件系统的地点在于DBMS系统所支撑的事务。 要是log的引进是为着保险长久化,那么LOCK的引进正是确认保障事务串行化,化解业务的产出带来了财富的RACE CONDITION。不一样的政工场景,定义了差异的出现需要,为此概念了4种专门的学问的隔开分离品级,分歧的DBMS引进了分化的锁机制来完毕。DB2的串行化学工业机械制至关心重视即便通过LOCK,LATCH,CLAIM/DRAIM 来兑现,具体的LOCK的有关属性以及LOCK属性对应用程序的熏陶比如OBJECT,SIZE,MODE,DURATION,PARTICIPANTS,PARAMETE奥迪Q5LOCATIONS都开始展览了介绍。当引进了DBMS CLUSTE福睿斯(DB第22中学为DATA SHA揽胜ING GROUP,而ORACLE中为ORACEL RAC) 今后,为了管理差别不一致MEMBE卡宴之间数据的一致性,DB2引进了PHYSICAL LOCK以及CF LOCK STRTURE 来兑现全局LOCK 争论检查实验。单个SUBSYSTEM中,主要的争辩有TIMEOUT,DEADLOCK,在DATASHA途胜ING GROUP,引进了新的CONTENTION,XES CONTENTION,FALSE CONTENTION,GLOBAL LOCK CONTENTION。引进LOCK的同不时候不可防止的推动了新的OVEENVISIONHEAD,DB2首要透过ICR-VLM,XES,CF等管理锁能源的央浼,除了地址空间除了锁供给之外,由于选用本人设计不创造也许某个特定的景况带来了繁多主题素材,如TEMEOUT,DEADLOCK,LOCK ESCALATION等,怎么样有效的制止那一个标题,供给系统运行人士以及开垦职员共同努力。
    本文的编慕与著述脉络基于个人对DBMS LOCK的咀嚼档案的次序,行文的逻辑性,合理性,整理内容的知识面包车型地铁广度和深度都有待进一步的思想。
    那篇博客从落笔到成功大意的框架,持持续续时间已经八九不离十2周,应该是友善耗费时间最长的一篇博客,纵使如此,每三回查看,发下仍有新的东西必要自己补充,等持续会三翻五次补充自身的知道。

    • LOCK OVERVIEW
      • WHY LOCKS ? DB2 Serialization Mechanisms
        • Data Consistency And Database Concurrency
          • Phenomena Seen When Transactions Run Concurrently
          • Lost Updates
          • DIRTY READS
          • Non-Repeatable Reads
          • Phantoms
      • LOCK PROPERTIES
        • LOCK OBJECT OWNER
        • LOCK PARTICIPANTS
        • LOCK SIZE
        • LOCK MODE
        • LOCK DURATION
        • LOCK REQUEST AND RELEASE
        • LOCK AND TRAN ISOLATION LELVEL
        • LOCKING PARAMETERS LOCATIONS
          • DDL
          • DML
          • Precompiler Locking Parameters
          • Bind Locking Parameters
          • Zparm Locking Parameters
      • CLAIM AND DRAIN
      • LATCH
      • ADVANCED TOPICS
        • L-LOCK(EXPLICIT HIERARCHICAL LOCKING )EHL
        • PHYSICAL LOCK
        • RETAINED LOCKS(UPDATE LOCKS)
          • IMPACT OF Retained LOCKS
        • LOCK SCOPE:
          • LOCAL LOCK
          • GLOBAL LOCK
          • GLOBAL LOCK COMMUNICATION
            • Page Set P-Lock Negotiation
        • CF LOCK STRUCTURE
          • MODIFIED RESOURCE LIST(MRL)
          • LOCK TABLE
          • XCF
          • XES
            • XES CONTENTION
            • FALSE CONTENTION
            • GLOBAL CONTENTION
            • DATA SHARING ACTIVITY REPORT
          • Lock Structure Shortage Actions
      • Types of locking problems
        • How do I find out I have a problem
        • Analyzing concurrency problems --TOOLBOXS
          • DB2 commands and EXPLAIN
          • DB2 TRACES
        • EXAMPLE:Analysis of a simple deadlock scenario and solution
          • DBD is locked

    A DB2 Performance Tuning Roadmap --LOG INTRODUCTION

    正文的内容是在整治表格时,对DB2 STAT LOG AVITIVITY部分的一个补给,首要介绍了DB2 LOG功能,overhead,涉及的预制构件以及部件之间的职业体制怎么样采纳DB2 log,现存的IBM 提供的 DB2 Log utilities。最后对DB2 log的逻辑给出多少个感性的认知。

    • 1 LOG OVERVIEW
      • DB2 LOG FUNDAMENTALS
        • LOG AIM:
        • WHY NEED LOGGING
        • IN PERFECT WORLD
        • OVERVIEW OF LOG WORK MECHANISM
    • 2 UNDERSTANDING OF LOG
      • LOG IMPLEMENTATION
        • 1 LOG RBA
        • 2 WHEN DATA SHARING INVOLED-LRSN
        • 3 LOG PAGE DATA SETS
          • FACTORS OF ACTIVE LOG DATA SETS:
          • BSDS – Boot Strap Data Set
          • SYSLGRNX-DLD
          • FACTORS OF ARCHIVE LOG DATASETS
      • LOG STRUCTURE
        • WHAT IS BEING LOGGED?
          • INFORMATION OF UR
          • PAGE SET INFORMATION
          • RECOVERY INFOMATION FOR
          • SYSTEM CHECKPOINTS
          • UNDO/REDO LOG EXMPLAE
          • COMPENSATION OF LOG EXMPLE
          • RECOVERY INFORMATION EXAMPLE
          • SYSTEM CHECKPOINTS DETAIL
        • WHEN IS LOGGING
      • LOG USAGE
        • Start DB2
        • Recovery of objects
        • REPLICATION
        • SOX Compliance
        • HEALTH CHECKS
      • LOG UTILITES,WORKING WITH THE LOG
        • DSN1LOGP
        • DSNJU004、DSNJU003
          • DSNJU004
          • DSNJU003 (a.k.a Change Log Inventory)
        • DSNJLOGF
        • RECOVER BSDS
          • #RECOVER BSDS
    • LOG RELATED DATASET
      • LOG PAGE FORMAT

    LOCK OVERVIEW

    1 LOG OVERVIEW

    WHY LOCKS ?DB2 Serialization Mechanisms

    DB2 LOG FUNDAMENTALS

    LOG AIM:

    The DB2 log has two main functions: to reapply or back out units of recovery, and to rebuild DB2 back to a consistent state in the event of a failure

    WHY NEED LOGGING

    1. overhead
    2. it costs in ters of performance,dasd,dministration,cleanup
    3. it's assurance-just in case of an accidentIN PERFECT WORLD
      1. no need to rollback
      2. no need to recover
      3. no program errors
      4. no hardware errors
      5. no power failtures
      6. no hurricances,terror attacks,fraud....

    Let’s get the MOST out of the LOG since it’s here

    OVERVIEW OF LOG WORK MECHANISM图片 1

    图片 2
    USAGE OF EACH COMPONENTS

    1. Active logs
      Where DB2 puts the current log information
    2. Log Buffers
      Where log information is held BEFORE externalisation to DASD
    3. Bootstrap Datasets
      These hold information about the current active and archive log datasets
    4. Archive logs
      These are copies of ‘old’ active log datasets
    5. SYSLGRNX
      DB2 keeps track of WHEN objects are (possibly) being updated

    Data Consistency And Database Concurrency

    DBMS差别与文件系统的最本质区别:DBMS帮衬工作
    DBMS :Allowing multiple users to access a database simultaneously without compromising data integrity.
    A transaction (or unit of work)is a recoverable sequence of one or more SQL operations that are grouped together as a single unit, usually within an application process.
    One of the mechanisms DB2 uses to keep data consistent is the transaction. A transaction or(otherwise known as a unit of work) is a recoverable sequence of one or more SQL operations that are grouped together as a single unit, usually within an application process. The initiation and termination of a single transaction defines points of data consistency within a database; either the effects of all SQL operations performed within a transaction are applied to the database and made permanent (committed), or the effects of all SQL operations performed are completely "undone" and thrown away (rolled back).

    2 UNDERSTANDING OF LOG

    Phenomena Seen When Transactions Run Concurrently

    事情并发带来的标题,为了减轻那么些难点,DB2定义了4种业务隔绝等级。

    1. LOST UPDATE
    2. DIRTY READS
    3. NON-REPEATABLE READS
    4. PHANTOMS

    LOG IMPLEMENTATION

    1 LOG RBA

    BYTE ADDRESSABLE(RBA=RELATIVE BYTE ADDRESS)
    RBA START=0X00000000000
    RBA END=0XFFFFFFFFFFF
    RBA UNIQUELY DEFINE A LOG RECORD

    2 WHEN DATA SHARING INVOLED-LRSN图片 3

    图片 4
    LRSN=LOG SEQUENCE RANGE NUMBER,WHICH IS A MODIFIED TIMESTAMP BASE ON SYSPLEX STORCE CLOCK

    3 LOG PAGE DATA SETS

    LOG OUTPUTBUFFER
    ACTIVE LOG
    ARCHIVE LOG
    图片 5
    图片 6
    WHEN DB2 GO TO THE LAST ACTIVE DATA SETS ,WRAP ROUND TO FIRST ACTIVE LOG DATA SETS

    FACTORS OF ACTIVE LOG DATA SETS:

    1. ACTIVE LOG DATA SETS NUMBER
    2. ACTIVE LOG DATA SETS SIZE
    3. SINGLE OR DUAL ACTIVE LOG
    4. PERFORMANCE CONSIDERATION:
      1. FAST DASD
      2. CONSIDER STRIPPING
      3. DB2 ZPARM OUTBUFFER LOGAPSTG

    BSDS – Boot Strap Data Set

    ? Name: catalog.BSDS01 and catalog.BSDS02
    ? Must be two identical datasets
    ? Contains highest RBA logged
    ? Contains active log description (begin – end RBA and status)
    ? Contains archive log description
    ? Dataset name
    ? Volume name
    ? RBA range
    ? Contains other necessary system information
    ? System checkpoint history, BP-description, CCSID info etc

    SYSLGRNX-DLD

    ? Table in Directory so no user access available
    ? Contains the RBA when any tablespace or
    partition is opened or closed for update
    ? Note this information is also in DB2 log
    ? SYSLGRNGX is used by DB2 to speed up
    recovery by limiting the amount of log data which needs to be scanned
    ? MODIFY utility removes this information along with SYSCOPY rows

    SYSLGRNGX is key to speeding up log processing during recovery Contains RBAs when tablespaces are open for update. Log ranges out ranges cannot contain updates for this tablespace and therefore we needed during the recovery process.SYSLGRNGX is also updated by running the Modify Recovery utility 
    

    FACTORS OF ARCHIVE LOG DATASETS

    1. ALWAYS PRODUCE TWO FILES FOR ONE ACTIVE LOGS
      IT IS AN ASYNCHRONOUS PROCESS
      VCAT.ARCHLOGN.BNNNNNN

      CONTAINDS THE BSDS INFORMATION BEFORE ARCHIVE IS WRITTEN FIRST LOG BEING ARCHIVED,IS STILL KNOWN AS ACTIVE LOG IN BSDS 
      

      VCAT.ARCHIVEN.ANNNNNN

      CONTAINS THE ACTUAL LOG FOR LOG RBA RANGE OF THAN LOG DATA SETSUPDATES BSDS WHEN COMPLETE 
      
    2. DB2 ZPARMS ARCHPFX1 ARCHPFX2 TSTAMP=YES|NO|EXT
      VCAT.ARCHIVEN.DXXXXX.TXXXXX.BNNNNNNNN

    3. WHEN ARCHIVE:
      ACTIVE LOG FULL
      TRUNCATED:ARCHIVE LOG COMAMND OR ERROR

    Lost Updates

    Occurs when two transactions read the same data, both attempt to update the data read, and one of the updates is lost

    Transaction 1 and Transaction 2 read the same row of data and both calculate new values for that row based upon the original values read. If Transaction 1 updates the row with its new value and Transaction 2 then updates the same row, the update operation performed by Transaction 1 is lost 
    

    LOG STRUCTURE

    WHAT IS BEING LOGGED?INFORMATION OF UR

    1. BEGIN/END UR INFORMATION
    2. UNDO/REDO STATEMENTS and compensation log rec
    3. commit rollback processing

    PAGE SET INFORMATION

    1. OPEN/CLOSE PAGE SET
    2. PENDING STATUS INFORMATION(COPY,CHECK,REORG)
    3. START/STOP INFORMATION(include which mode ut)
    4. DBD INFORECOVERY INFOMATION FORINVOLED TABLES:

      SYSIBM.SYSUTILX SYSIBM.DBD01 SYSIBM.SYSCOPY 
      

      SYSTEM CHECKPOINTSUNDO/REDO LOG EXMPLAE

    UPDATE T
    SET COL='BBB'
    WHERE ID=1
    LRH004A002F 06000001 0E800006 CEA48CC9 0006CEA4 8D590526 0006CEA4 8D59C7C0
    AF504B23 0000

    *LG** 80010C00 02000000 02000006 CEA4049E 2B02 0000 00120101 00030900 000600C2 C2C200C1 C1C1 
    

    C2C2C2='BBB'
    C1C1C1='AAA'
    URID(0006CEA48CC9)RBA OF UR STARTED IN LOG 010C=DBID
    0002==PAGE SET OBID 000000 02=PAGE ID**

    COMPENSATION OF LOG EXMPLE图片 7

    图片 8
    In case of the example the current value is BBB and after the update it should become AAA, but for some reason a ROLLBACK occurs, in that
    case DB2 needs to undo this update. This undo by itself is also being logged. These log records are called compensation log records.

    SUBTYPE(UPDATE IN-PLACE IN A DATA PAGE) CLR(YES)*LG** 80010C00 02000000 02000006 CEA550BF AB00 0000 00120101 00030900 000600C2 C2C200C1 C1C1 
    

    CLR(YES) which indicates it is a compensation log record

    RECOVERY INFORMATION EXAMPLE

    DB2 writes “syscopy” information for certain system tables to the log instead of writing it to syscopy. The reason is simple. In case of a
    disaster recovery, we need to recover the system in precise steps.Meaning that certain system tables have to be recovered before we can recover sysibm.syscopy. Therefore the backup information of those objects is written to the log.

    SYSTEM CHECKPOINTS DETAIL

    SYSTEM CHECKPOINT (snapshot of activity on system)

    1. WHAT IS IN SYSTEM CHECKPOINT
      1. Entry per active thread (amongst other status inflight/in commit etc)
      2. Entry per open page set (including exception status)
      3. Page externalization
    2. How often should one be taken ?

      1. Every 3-5 minutes (many sites are at 15-20 minutes)
      2. CHKFREQ : V9 : # LOG records or # seconds

         V10: Minimum of # LOG records and # seconds 
        
    3. Important for (re)start up performance
      ? Start up ALWAYS from LAST CHECKPOINT

    WHEN IS LOGGING

    As the work is being done
    ? In Log output buffer (OUTBUFF)
    ? Regularly flushed to Active Log Dataset
    Sequentially first active copy1 then active copy2 (DB2 V10 应该是双写)
    图片 9
    ? At Commit
    ? At Rollback
    ? Log Write threshold (WRTHRSH 20 pages without commit)
    ? Archive log command
    ? System Checkpoint
    ? Log Write Ahead Force

    DIRTY READS

    Occurs when a transaction reads data that has not yet been committed

    Transaction 1 changes a row of data and Transaction 2 reads the changed row before Transaction 1 commits the change. If Transaction 1 rolls back the change, Transaction 2 will have read data that theoretically, never existed. 
    

    LOG USAGE

    Start DB2

    4 phases :

    1. Log initialization
      Read/compare BSDS
      Find current active log dataset and end of log
    2. Current status rebuild
      Read last system checpoint
    3. Forward log recovery
      Do all work for INCOMMIT and INDOUBT threads
    4. Backward log recovery
      Do all work for INABORT and INFLIGHT threads
    5. ALWAYS start from LAST SYSTEM CHECKPOINT
      Amount of work varies (ab)normal shutdowN

    Recovery of objects

    DB2 新添了多少个参数BACKOUT(YES),那一个参数应该不是太常用,越来越多的情景相应是应用imgcopy log的方法去追,即point-in-time,这里不开始展览介绍。

    REPLICATION

    于今IDC内一定火的二个topic,它是贯彻双活或是多活的功底,平台版本的DBMS也可以有落实,比如mysql的mater-slave方式,即mysql自己就支持,没有须求新增添软件,不过是单线程实行处理,为此非常多vendors提供了巩固版的版本使slave上帮忙并发。zos 上使用QREP MQ完成,支持异构数据库,延迟能够调节在分钟以内。RPT=2MIN,RTO=2HOU中华V,

    SOX Compliance

    本条愈来愈多是的是内部审计,外审的用处。相对来讲使用审计LOG的艺术,费用依旧比较高的,有alternative software。

    HEALTH CHECKS

    BEST PRACTICE,依照系统今后的运市价况,查看时候ACTIVE LOG,AC生殖器疱疹E LOG是还是不是运行非凡。

    Non-Repeatable Reads

    Occurs when a transaction executes the same query multiple times and gets different results with each execution

    Transaction 1 reads a row of data, then Transaction 2 modifies or deletes that row and commits the change. When Transaction 1 attempts to reread the row, it will retrieve different data values 
    

    LOG UTILITES,WORKING WITH THE LOG

    有些一句,那么些理应是当前平台比较不足的,大概如今也存在,只是本身眼光浅短了。

    DSN1LOGP

    ? Formats DB2 log in a readable output
    ? Detailed or summarized (SUMMARY(YES/NO/ONLY)
    ? Include page set status (DATAONLY(YES/NO)
    ? Include SYSCOPY info (SYSCOPY(YES/NO)
    ? Limit range (from – to)
    ? Limit scope (e.g. URID,DBID,)
    ? Cannot read current active log
    ? Cannot read compressed logs by DFSMS

    DSNJU004、DSNJU003DSNJU004

    Will list certain parts of the BSDS information
    ? Log data set name(s), log ranges, volume(s) etc
    ? Active log status
    ? Conditional restart history
    ? System checkpoint history
    ? Backup system history
    ? Archive log history
    ? CCSID information
    ? Does not show Buffer pool configuration
    ? Be careful time values are in GMT except LTIME column

    DSNJU003 (a.k.a Change Log Inventory)

    ? Only runs when DB2 is down
    ? Allows to change BSDS content, USE WITH CARE!
    ? Add/Delete active and archive Logs
    ? Add/Delete system checkpoints
    ? Create conditional restart record (CRESTART)
    ? STARTRBA
    ? ENDRBA
    ? COLD START ? STARTRBA=ENDRBA
    ? Causes most of the time DATA LOSS

    DSNJLOGF

    DSNJLOGF
    ? Pre-formats new active log dataset
    ? Avoids delay at first use !!

    RECOVER BSDS#RECOVER BSDS

    ? MUST have TWO identical BSDS to start up
    ? What if one BSDS is broken ?
    ? DSNJ126I I/O ERROR FORCED SINGLE BSDS
    ? Do NOT stop DB2
    ? Issue command –RECOVER BSDS ? automate this!
    ? What if one BSDS is broken at start up?
    ? DB2 does not start
    ? IDCAMS rename/define, REPRO

    Phantoms

    Occurs when a row of data that matches some search criteria is not seen initially

    Transaction 1 retrieves a set of rows that satisfy some search criteria, then Transaction 2 inserts a new row that contains matching search criteria for Transaction 1’s query. If Transaction 1 re-executes the query that produced the original set of rows, a different set of rows will be retrieved – the new row added by Transaction 2 will now be included in the set of rows returned 
    

    LOG RELATED DATASET

    LOCK PROPERTIES

    LOG PAGE FORMAT

    对LOG PAGE 有三个感到认知,LOG 的逻辑结构复杂了,后续借使有亟待在补偿上吗。
    1 BYTE=8BITS
    1 NIBBLE=4BITS
    LOG PAGE SIZE=4KB
    LOG PAGE AVAIABLE SPACE=4075
    LOG PAGE CONTROL INTERVAL
    SIZE: LAST 21/X'15' BYTE OF PAGE
    USAGE:CI DESCRIBE THE LOG PAGE
    START RAB OF THE PAGE
    LOG RBA DO NOT HAVE RBA-----IT'S START RBA of the page plus offset into page of the record
    1 log rec maybe span may log pages
    本文全部的开始和结果均整理自互连网,仅供参谋学习,如有涉及版权难点,请自行删除本文,多谢。

    DB2 Performance Tuning Roadmap --LOG INTRODUCTION 本文的源委是在重新整建表格时,对DB2 STAT LOG AVITIVITY部分的三个填补,重要介绍了DB2 LOG成效,overhead,...

    LOCK OBJECT OWNER

    此处的object是指锁所施加的靶子,不相同的锁所能适用的对象是分化的。如PAGE LOCK的对象自然是PAGE.
    OWNE途乐 代表什么人全部锁,有的是TRAN,有的是DB2 MEMBETucson
    LOCK PARTICIPANTS
    图片 10

    图片 11
    Locking is a complex interaction of many parts.

    1. DBM1, where SQL executes, is the beginning of locking.
    2. The IRLM, a separate address space, is where locks are held and managed. The DBM1 AS requests locks from the IRLM. In addition to its functionality of granting and releasing locks, the IRLM is also in charge of detecting deadlock and timeout situations.
    3. In a data sharing environment, there are two other ingredients in the soup. The XCF address space is where its XCF component resides. The XES does some lock management and communicates directly with the lock structure in the coupling facility

      ### LOCK SIZE

      访谈数据的限定
      设计锁的颗粒度对系统和运用均有震慑:设计时要综合考虑

      1. 锁本人也是一种能源,持有的lock size越大,锁财富消耗越小
      2. lock size的高低与对利用访谈的产出(concurrent)成反比关系
      3. LOCK SIZE 带来的别的一个副作用正是当您报名的锁财富丰硕多,以至于超越系统设定的阀值时(NUMLKUS/NUMLKTS/LOCK MAX =0,SYSTEM,N),
        lock size: PAGE/ROW /TABLE /PARENT LOCKS:TABLESPACE /PARTITION

        Page/Row locks are not compatible with tablespace locks The solution is "Intent Locking" Regardless of the locksize, DB2 will always start with a tablespace lock 
        

        INTENT LOCKS:IS/IX USED whenever page or row locks are being used
        GROSS LOCKS:The gross locks (S, U, SIX and X) are tablespace locks which are used in three
        situations

      4. lock size tablespace

      5. lock table for a non-segmented tablespace
      6. lock escalation for a non-partitioned tablespace

        ### LOCK MODE

        数码的拜望格局:独占依然分享 S/U/X
        X:exclusive, not sharable with other
        S:shared, sharable with other S-locks
        LOCK MODE之间的包容性如下:

        S U X
        S Y Y
        U Y N
        X N N

        ### LOCK DURATION

    怀有数量的时光:commit,across commit(with hold)
    FROM:START OF FIRST USE
    TO :COMMIT OR MOMENTIALIY

    LOCK REQUEST AND RELEASE

    When modifying data (Insert, Update and Delete),DB2 determines all locking actions

    1. Lock size Tablespace
      => lock is taken at first use
      => lock is released at commit or deallocate
    2. SQL- Statement “Lock Table … in Exclusive Mode”
      => initially an IX-lock (on the tablespace)
      => lock is taken at first use
      => lock is released at commit or deallocate
    3. Page lock and row lock
      => initially an IX-lock (on the tablespace)
      => then X-locks are taken on each page/row as needed
      => these X-locks are released at commit

    LOCK AND TRAN ISOLATION LELVEL

    各个业务隔开分离等级锁所能解决的标题

    1. Repeatable Read
      => locks all data read by DB2
      => guarantees that exact the same result set will be returned when reusing the cursor
    2. Read Stability
      => locks all data “seen” by the application
      => guarantees that at least the same result set will be returned when reusing the cursor
    3. Uncommitted Read (UR)
      Pro:
      Our read will be “cheaper”
      We are not disturbing others
      Con:
      We may read data
      which may be “un-inserted”
      which may be “un-updated”
      When you are reading live (operational) data,you can never guarantee exact results
    4. Cursor Stability (CS)
      => a lock is taken when the row is fetched
      => the lock is released when next row is fetched,
      or at end of data or at Close Cursor
      For page locks:
      => a lock is taken when the first row on the page is fetched
      => the lock is released when next page is fetched,
      or at end of data or at Close Cursor
      This means:When you have fetched a row and have not reached end of data or closed the cursor,
      you are holding an S-lock on the row or page
    5. LOCK AVOIDANCE
      Updateable

      DECLARE upd_cur CURSOR FOR SELECT data1, data2 FROM table FOR UPDATE OF colx 
      

      Read-Only

      DECLARE ro_cur CURSOR FOR SELECT DEPTNO, AVG(SALARY) FROM EMP GROUP BY DEPTNO 
      

      Ambiguous

      DECLARE amb_cur CURSOR FOR SELECT data1, data2 FROM table 
      

      => Use FOR READ ONLY or FOR FETCH ONLY
      If we have the following:

      Read-Only (or Ambiguous) Cursor
      Cursor Stability
      Currentdata No
      it is possible that we can get "Lock Avoidance“
      Lock avoidance is advantageous!
      A possible problem with Currentdata No:
      Fetch a row
      ...
      Update (or Delete) Where Key = :hostkey
      can give SQLSTATE="02000" (Not Found)

    LOCKING PARAMETERS LOCATIONS

    下边分别介绍了系统参数,应用参数如何影响LOCK行为

    DDL

    OBJECT DEFINITION,CREATE TABLESPACE:

    1. LOCKMAX (INTEGER,SYSTEM)
    2. LOCKSIZE
      DEFAULT:ANY
      OPTION:ANY,TABLESPACE,TABLE,PAGE,ROW,LOB
    3. MAXROW: INTEGER
      DEFAULT:255
      USED TO increase concurrency
      MAXROWS 1Used to emulate row-level locking
      locking without the costs of page p-lock processing
    4. MEMBERCLUSTER:(P-LOCK)
      Clustering per member
      Reduce p-lock contentions forspace mappages
      Destroys clustering according to clustering index
    5. TRACKMOD (P-LOCK)
      DEFAUTL:NO
      OPTION:YES/NO

    DML

    1. LOCK TABLE
      SYNTAX:LOCK {TABLE, TABLESPACE [partno]} IN
      {EXCLUSIVE, SHARE} MODE
    2. Cursor Definitions
      FOR UPDATE OF
      FOR READ ONLY
      1. Tells DB2 the result set is read-only
      2. Positioned UPDATEs and DELETEs are not allowed
      3. No U or X locks will be obtained for the cursor
    3. Isolation Clause
      WITH [UR, CS, RS, RR]
      1. USE AND KEEP EXCLUSIVE LOCKS
      2. USE AND KEEP UPDATE LOCKS
      3. USE AND KEEP SHARE LOCKS

    Precompiler Locking Parameters

    NOFOR Option:NO FOR update clause mandatory for positioned updates
    STDSQL(YES): Implies NOFOR

    Bind Locking Parameters

    1. CURRENTDATA
      FUNCTION:CURRENTDATA helps to determine if DB2 will attempt to avoid locks
      mean:Must the data in program be equal to the data at the current cursor position
      lock aovidance
      demo:
      图片 12
      CURRENTDATA(NO)
      note
    2. ISOLATION
    3. RELEASE

    Zparm Locking Parameters

    ZPARM MEANING
    URCHKTH UR Checkpoint Frequency Threshold
    URLGWTH UR Log Write Threshold
    LRDRTHLD Long-Running Reader
    RELCURHL Release Held Lock
    EVALUNC Evaluate Uncommitted
    SKIPUNCI Skip Uncommitted Inserts
    RRULOCK U lock for RR/RS
    XLKUPDLT X lock for searched U/D
    NUMLKTS Locks per Table(space)
    NUMLKUS Locks per User

    本文由1010cc时时彩经典版发布于1010cc时时彩经典版,转载请注明出处:A DB2 Performance Tuning Roadmap --DIVE INTO LOCK

    关键词: