Waits due to rows being covered by the same BITMAP index fragment example

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:  Waits due to rows being covered by the same BITMAP index fragment example

Solution:

  The lock waits which can occur are demonstrated using the following

  tables. Connect as SCOTT/TIGER or some dummy user to set up the test

  environment using the following SQL:

    DROP TABLE tx_eg;

    CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) )

      INITRANS 1 MAXTRANS 1;

    INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );

    INSERT into tx_eg VALUES ( 2, 'Second','MALE' );

    INSERT into tx_eg VALUES ( 3, 'Third','MALE' );

    INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );

    INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );

    COMMIT;

  In the examples below three sessions are required:

        Ses#1   indicates the TX_EG table owners first session

        Ses#2   indicates the TX_EG table owners second session

        DBA     indicates a SYSDBA user with access to <View:V$LOCK>

Waits due to rows being covered by the same BITMAP index fragment

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  Bitmap indexes index key values and a range of ROWIDs. Each 'entry' 

  in a bitmap index can cover many rows in the actual table.

  If 2 sessions wish to update rows covered by the same bitmap index

  fragment then the second session waits for the first transaction to

  either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.

  Eg:   Ses#1:  CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );

        Ses#1:  update tx_eg set sex='FEMALE' where num=3;

        Ses#2:  update tx_eg set sex='FEMALE' where num=4;

        DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST

                 from v$lock where type='TX';

        SID        TY ID1        ID2        LMODE      REQUEST

        ---------- -- ---------- ---------- ---------- ----------

                 8 TX     262151         62          6          0

                10 TX     327680         60          6          0

                10 TX     262151         62          0          4

        This shows SID 10 is waiting for the TX lock held by SID 8 and it

        wants the lock in share mode (as REQUEST=4). 

        Ses#1:  commit;

        Ses#2:  commit;

References:

Get Oracle Certifications for all Exams
Free Online Exams.com