Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    20

    Unanswered: Question on Locking in Oracle

    Hi ,
    We have an application which carries out Updates ,inserts and deletes on three tables say table A for updates and delete and Table B for insert and delete and a table C which contains the deleted records .This application is to be made available to the end users 24*7 hours.
    At the end of the day we have to carry out an extract for the newly updated ,inserted and deleted records using UTL file package .After this extract is complete we have to delete the records present in all the table i.e. A, B,C as the records present in these tables have been already processed .
    My query is while carring out the final delete process there might be a possiblity that the end users are carrying out updates/inserts/deletes on the tables A/B/C so if we fire the delete process at that time then deadlock situation might arise.How can we avoid such a situation ??

    Thanks in advance
    Satya_x
    Regards
    Satya

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

    Re: Question on Locking in Oracle

    It wouldn't be a deadlock, it would just be a lock - i.e the process waits for the user or vice versa.

    To avoid excessive waiting for locks, your process should probably work like this:

    For each row to be processed:

    - SELECT row FOR UPDATE NOWAIT;

    - Handle exception ORA-00054 (resource busy and acquire with NOWAIT specified) if it is raised - maybe try again after a short wait, or skip record and move on to next. In 9i you can specify WAIT <n> instead of NOWAIT, where <n> is number of seconds.

    - Write to file

    - Delete row

    Here is a demo using the EMP table. First in one session I will update 2 rows, hence locking them:
    Code:
    Session1> update emp set sal=sal where ename in ('WARD','KING');
    
    2 rows updated.
    Now in another session I run my batch process:
    Code:
    Session2> declare
      2    cursor c is
      3      select * from emp order by ename;
      4    v_sal emp.sal%type;
      5    row_locked exception;
      6    pragma exception_init( row_locked, -54 );
      7  begin
      8    for r in c loop
      9      begin
     10        select sal into v_sal from emp where emp.empno = r.empno for update of sal nowait;
     11        dbms_output.put_line(r.ename||' processed');
     12        -- delete emp where emp.empno = r.empno;
     13      exception
     14        when row_locked then
     15          dbms_output.put_line(r.ename||' locked by other user - not processed');
     16      end;
     17    end loop;
     18  end;
     19  /
    ADAMS processed
    ALLEN processed
    BLAKE processed
    CLARK processed
    FORD processed
    JAMES processed
    JONES processed
    KING locked by other user - not processed
    MARTIN processed
    MILLER processed
    SCOTT processed
    SMITH processed
    TURNER processed
    WARD locked by other user - not processed
    
    PL/SQL procedure successfully completed.
    If I hadn't commented out the DELETE statement on line 12 I would have successfully deleted all employess except KING and WARD.

    You might want to keep a count of failures (e.g. 2 above), and repeat the whole process until it gets to zero or you don't want to wait any longer.

  3. #3
    Join Date
    Jan 2004
    Posts
    20
    Thanks a lot Tony.
    Regards
    Satya

  4. #4
    Join Date
    Jan 2004
    Posts
    20
    Tony i Was trying to implement the delete process mention by your previous post.

    1st session
    -------------
    update emp set sal=sal where ename in ('WARD','KING');

    2 rows updated.

    2 session
    -------------
    I execute the following

    DECLARE
    CURSOR c IS
    SELECT * FROM EMP ORDER BY ename;
    v_sal EMP.sal%TYPE;
    row_locked EXCEPTION;
    PRAGMA EXCEPTION_INIT( row_locked, -54 );
    BEGIN
    FOR r IN c LOOP
    BEGIN
    --SELECT sal INTO v_sal FROM EMP WHERE EMP.empno = r.empno FOR UPDATE OF sal NOWAIT;
    --DBMS_OUTPUT.PUT_LINE(r.ename||' processed');
    DELETE EMP WHERE EMP.empno =r.empno;
    DBMS_OUTPUT.PUT_LINE(r.ename||' processed');
    EXCEPTION
    WHEN row_locked THEN
    DBMS_OUTPUT.PUT_LINE(r.ename||' locked by other user - not processed');
    END;
    END LOOP;
    END;

    Ideally it should not delete WARD and KING while the other rows should be deleted.
    But 2nd sessions hangs till i give a commit or rollback in my 1st session.
    Why is it not giving the rowlocked exception in the case of delete.
    Regards
    Satya

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Unfortunately, the NOWAIT option is only available on SELECT FOR UPDATE, not on UPDATE or DELETE statements. The DELETE statement will wait "forever" until it can lock the record. The solution is to do SELECT FOR UPDATE NOWAIT first, and then if successful delete the record.

  6. #6
    Join Date
    Jan 2004
    Posts
    20
    Tony,

    Instead of using select for update nowait I used select for update skip locked and it works fine.Concider the following example---

    DECLARE
    CURSOR c IS
    SELECT * FROM emp ORDER BY ename FOR UPDATE SKIP LOCKED;
    v_sal emp.sal%TYPE;
    row_locked EXCEPTION;
    PRAGMA EXCEPTION_INIT( row_locked, -54 );
    BEGIN
    FOR r IN c LOOP
    BEGIN
    DELETE emp WHERE emp.empno = r.empno;
    DBMS_OUTPUT.PUT_LINE(r.ename||' processed');
    EXCEPTION
    WHEN row_locked THEN
    DBMS_OUTPUT.PUT_LINE(r.ename||' locked by other user - not processed');
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE(r.ename||' locked by other user - not processed1');
    NULL;
    END;
    END LOOP;
    END;
    Regards
    Satya

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Beware! I was fascinated by your use of "FOR UPDATE SKIP LOCKED", as I had never heard of it before. I rushed to the online docs, and could find no reference to it anywhere, not even in 10G. Then I searched Ask Tom and found this reference:
    Reviewer: Connor from Wet Scotland

    There is a "select for update skip locked" mechanism which allows to lock a
    record after skipping over the ones already locked. This can be helpful for
    multiple consumers.


    Followup:
    sure there is -- AQ uses it.

    It is undocumented.
    It is not supported for external use.
    It works only in special conditions (it is not a general purpose command).

    I would still recommend using AQ since it is supported, has tons of other
    features they would need to build themselves, and provides all of the
    functionality they need "out of the box".
    And further on, Tom says:

    It is not only "not" ANSI, it is

    o not documented
    o not supported
    o not a good idea to use it.

    If you don't believe me, please contact support and ask them if it is OK to use
    this -- will you still be supported in your production application.

    Ultimately, the decision is yours. You have my opinion. Technically, there is
    no reason it would not work -- however, given the above points, I cannot suggest you look at it.
    Here's the page:

    http://asktom.oracle.com/pls/ask/f?p...#1576578561766

  8. #8
    Join Date
    Jan 2004
    Posts
    20
    Thanks Tony.
    Phew i was almost going to commit a mistake.
    Regards
    Satya

Posting Permissions

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