Locking

Lock Attributes :

      -Size              -> how much data to be locked

      -Mode           ->read/update locks on Objects

      -Duration        -> how long the lock held
  

Lock Size :

    Tablespace

    Table

    Partition

    Page

    Row

  n  LOB, XML 

Lock Modes:

   Intent Share (IS)

  Share(S)

  Intent Exclusive(IX)

  Exclusive(X)

  Share with Intent Exclusive(SIX)

  Update(U)

Lock Durations:

  

    Table space,table and partition lock duratios ::

  

      ACQUIRE --> when locks are taken

            ALLOCATE  > at first SQL stmt issued, max required lock is taken on 

                                      all objects of plan/package

            USE               >  when SQL stmt issued, required lock is taken on 

                                     object involved in SQL stmt..

     RELEASE -->WHEN LOCKS GET RELEASED.

            DEALLOCATE > released at the end of the program 

             COMMIT         > released at commit.

Isolation levels :

  UR(uncommited Read)

  CS(cursor stability)

  RS(read stability)

 RR(repeatable read)  >> default

Lock Monitoring :

  -EXPLAIN     > PLAN_TABLE > TSLOCKMODE

  -DISPLAY     > DISPLAY DATABASE(dbname) SPACENAM(tsname) LOCKS

                             LCOKINFO column >>  H(X,P,A)

                                 Lock qualifier >  H-holder  ,  W-waiter

                                 Lock Identifier > X-exclusive S-share ..............

                                 Lock Object    > P-partition , T-table, S- ts ....

                                 Lock Duration > A-deallocae , C-commit, P-plan completion   

  -TRACE        > STATISTIC TRACE CLASS 3  > info about deadlocks and timeouts

                        >ACCOUNTING TRACE CLASS 3 >shows elapsed time divided into various waits

 

and some more topics ::

 Lock avoidance

Claims and Drains

Timeouts and Deadlocks

Lock promotions and escalations

Database and application design for concurrency ..