Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Location
    India
    Posts
    5

    Question Unanswered: locking during [bulk inserts/updates] in 8i

    Hi All

    can some one explain how locking is handled when we do bulk inserts/updates using a pl/sql procedure.

    Will oracle lock the table as a whole or lock only the rows that are updated or inserted. Are the source rows ie from selected rows from a source table are also locked this process.


    Assuming if rows from two tables are source for insertion into a target table will oracle lock rows of both source tables and also the records that are inserted if specified in a begin transaction .... commit block.

    An example would be of great use.


    Regards,
    Vineeth

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: locking during [bulk inserts/updates] in 8i

    Originally posted by shetty_vin
    Hi All

    can some one explain how locking is handled when we do bulk inserts/updates using a pl/sql procedure.

    Will oracle lock the table as a whole or lock only the rows that are updated or inserted. Are the source rows ie from selected rows from a source table are also locked this process.


    Assuming if rows from two tables are source for insertion into a target table will oracle lock rows of both source tables and also the records that are inserted if specified in a begin transaction .... commit block.

    An example would be of great use.


    Regards,
    Vineeth
    When you insert a new record it is not locked, but until you commit it no one else can see it - it does not exist yet for them. So no one else can update or delete it until you commit it.

    When you update or delete a record, you obtain a lock on that record. No one else can update or delete that record until you commit or rollback your transaction. They can still select it (with its old values) but they can't lock it.

    When you select a record normally, you do not obtain a lock on it. Another user could update or delete the record you selected. If you use SELECT ... FOR UPDATE then the record(s) you select are locked and no one else can update or delete them (or select them with FOR UPDATE) - but they can still SELECT the record.

    Use of PL/SQL and bulk operations makes no difference to this behaviour.

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    5

    Re: locking during [bulk inserts/updates] in 8i

    Originally posted by andrewst
    When you insert a new record it is not locked, but until you commit it no one else can see it - it does not exist yet for them. So no one else can update or delete it until you commit it.

    When you update or delete a record, you obtain a lock on that record. No one else can update or delete that record until you commit or rollback your transaction. They can still select it (with its old values) but they can't lock it.

    When you select a record normally, you do not obtain a lock on it. Another user could update or delete the record you selected. If you use SELECT ... FOR UPDATE then the record(s) you select are locked and no one else can update or delete them (or select them with FOR UPDATE) - but they can still SELECT the record.

    Use of PL/SQL and bulk operations makes no difference to this behaviour.

    ----------------------------------------------------------------------------------
    Hi Andrew


    Can u advice on the locking mode in the pl/sql proc below

    I Have a PL/SQL Proc. which does the needs to do the
    follwing

    I have two unrelated tables, namely Table A and Table
    B. I want to select say 25 records of Table A and
    insert into column A of Table X and also select 25
    records of Table B and insert into column B of Table
    X.

    I will only inserting column 1 of Table A and column 1
    of Table B into Target two colums of Table X.

    After inserting these 25 records into Table X i want
    to update, a status field of the both table A and
    Table B for all those records inserted into Table X to
    'R'.

    At present i am doing this by performing a bulk
    insert/update without running a loop. It works great
    and zips.

    But when i give try to issue a LOCK by giving FOR
    UPDATE NOWAIT within the bulk update or insert i get
    the an error.


    ----Following is the code what i have written

    CREATE OR REPLACE PROCEDURE pairing (

    P_BATCH_NUM_N NUMBER, -- batch number
    P_AA_CATEGORY_N NUMBER, -- sim category
    P_ST_AA_NUM_V VARCHAR2, -- sim start no
    P_BB_BB_CLASS_NUM_N NUMBER, -- mobile class
    P_ST_BB_NUM_V VARCHAR2, -- start mobile no
    P_TOTAL_QTY_N NUMBER, -- total qty
    P_USER_CODE_N NUMBER, -- user code
    P_STATUS_OPTN_V VARCHAR2, -- status flag
    P_SUCCESS OUT NUMBER, -- success flag
    P_ERR_MESG OUT VARCHAR2 -- process error
    message
    )

    IS

    v_process_ok VARCHAR2 (1);
    quit EXCEPTION;
    v_last_seq_no NUMBER(10);

    BEGIN

    SAVEPOINT TEST;



    INSERT INTO X
    (BATCH_NUM_N, SEQ_NUM_N, AA_NUM_V,
    BB_NUM_V, STATUS_OPTN_V)(

    SELECT P_BATCH_NUM_N, SEQ.NEXTVAL,
    aa.AA_NUM_V, bb.BB_NUM_V,'F'


    FROM

    (SELECT AA_NUM_V, AA_CATEGORY_CODE_N,
    STATUS_OPTN_V, rownum rn FROM A
    WHERE AA_NUM_V >= P_ST_AA_NUM_V AND
    AA_CATEGORY_CODE_N = P_AA_CATEGORY_N and
    STATUS_OPTN_V = 'F' ) aa,

    (SELECT BB_NUM_V, BB_CLASS_NUM_N,
    STATUS_OPTN_V,rownum rn FROM B
    WHERE BB_NUM_V >= P_ST_BB_NUM_V AND
    BB_CLASS_NUM_N = P_BB_BB_CLASS_NUM_N and
    STATUS_OPTN_V = 'F')bb

    WHERE

    aa.rn = bb.rn and aa.rn <=
    P_TOTAL_QTY_N );


    p_err_mesg := 'Before Updation of Status to
    Reserved';



    --Update A Status to 'R'

    UPDATE A SET STATUS_OPTN_V = 'R'

    WHERE AA_NUM_V

    IN (SELECT AA_NUM_V FROM A
    WHERE AA_NUM_V >= P_ST_AA_NUM_V AND
    AA_CATEGORY_CODE_N = P_AA_CATEGORY_N and
    STATUS_OPTN_V = 'F' AND ROWNUM<=
    P_TOTAL_QTY_N);


    --Update B Status to 'R'


    UPDATE B SET STATUS_OPTN_V = 'R'

    WHERE BB_NUM_V

    IN (SELECT BB_NUM_V FROM B
    WHERE BB_NUM_V >= P_ST_BB_NUM_V AND
    BB_CLASS_NUM_N = P_BB_BB_CLASS_NUM_N and
    STATUS_OPTN_V = 'F' AND ROWNUM <=
    P_TOTAL_QTY_N FOR UPDATE NOWAIT);

    COMMIT;

    P_SUCCESS := 1;
    P_ERR_MESG := 'Successfully finished pairing for the
    defined range.';


    END pairing;
    /

    *****************************************

    what i want is that when i do the insert into table x
    i want to lock say 25 records of TABLE A and TABLE B
    so that no other user can update or delete these
    records. Only after i change the status in these 2
    tables should the process release the locks of these
    records of table A and B.


    Also can u tell me if what i am doing is the correct
    way of doing this or should i use cursors for this.

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

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The for update nowait doesnt make sense in a subquery. It is only used in a select statement not in the actual update statement. So you might do a select for update statement at the beginning, and then later on you might do an update on the row selected (see the processing transactions section of the PLSQL manual).

    Oracle only locks those rows which are being changed, it never escalates locks. See Tom Kytes v.good article at http://www.databasejournal.com/featu...le.php/1403591
    http://www.databasejournal.com/featu...le.php/1403731
    for a better understanding of locking (and why Oracle is so much better than other so-called enterprise databases : ) ).

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •