DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Locking Record Question

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Locking Record Question

    I create a table with a list of account numbers. Then I want to create a program that selects for update(locks the record) an account number from that table then runs some processes with that account number. Once it is done I will update a success flag to Y in that table for that account number and commit. Then repeat the process all over again.

    My question is what if I want to kick of five seperate processes to do this? If I have the first process grab the first account number and lock it. How do I tell the rest of the processes to go to the unlocked accounts. I am thinking the next four processes will keep trying to select that locked record and blow up.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you use "... for update nowait" then if the row is already locked Oracle will return an "ORA-00054: resource busy and acquire with NOWAIT specified" error. You can trap this and use it as a signal to try and lock the next record.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    I Still Do not get it

    Once I capture the error how to I then tell this other query to select the next record.

    Example

    Session 1

    1 select acct_no from myacct
    2 where flag != 'Y' and rownum < 2
    3* for update nowait
    user1@testdb>/

    ACCT_NO
    --------------------
    0000000001


    Session 2

    user2@testdb>select acct_no from myacct
    2 where flag != 'Y' and rownum < 2
    3 for update nowait;
    select acct_no from myacct
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: I Still Do not get it

    Originally posted by lesstjm
    Once I capture the error how to I then tell this other query to select the next record.

    Example

    Session 1

    1 select acct_no from myacct
    2 where flag != 'Y' and rownum < 2
    3* for update nowait
    user1@testdb>/

    ACCT_NO
    --------------------
    0000000001


    Session 2

    user2@testdb>select acct_no from myacct
    2 where flag != 'Y' and rownum < 2
    3 for update nowait;
    select acct_no from myacct
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified
    Use a FOR LOOP cursor to process the rows. Do the processing in a procedure and when you catch the ORA-00054 exit out of the procedure without throwing an error. It will move on to the next row and try to process that row.

  5. #5
    Join Date
    Jan 2001
    Posts
    515

    Parrallel Process

    I want to kick of five seperate processes to run this acct numbers so I can't do it in one script.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: Parrallel Process

    Originally posted by lesstjm
    I want to kick of five seperate processes to run this acct numbers so I can't do it in one script.
    If the account number is completely numeric than you can do something like this. You may need to store the rows that need processing in a collection to avoid snapshot too old errors.

    Code:
    CREATE OR REPLACE PACKAGE pk_acct_num_job
    AS
       PROCEDURE master_proc (p_number_of_processes IN BINARY_INTEGER DEFAULT 4 );
    
       PROCEDURE update_accounts ( total_procs IN BINARY_INTEGER, 
                                   mod_number  IN BINARY_INTEGER );
    END pk_acct_num_job;   
    /
       
    CREATE OR REPLACE PACKAGE BODY pk_acct_num_job
    AS
       PROCEDURE master_proc (p_number_of_processes IN BINARY_INTEGER DEFAULT 4 )
       AS
       BEGIN
          FOR i IN 1..p_number_of_processes
          LOOP
             DBMS_JOB(Submit update_accounts (p_number_of_processes, i);
          END LOOP
       END;
       
       PROCEDURE update_accounts ( total_procs IN BINARY_INTEGER, 
                                   mod_number  IN BINARY_INTEGER )
       AS
          CURSOR IS 
             SELECT c_acct_no 
               FROM myacct
              WHERE flag != 'Y' 
                AND MOD(acct_no,total_procs) = mod_number
                FOR UPDATE WAIT;
       BEGIN
          FOR r_acct_no IN c_acct_no
          LOOP
             Process the account number;
          END LOOP;
          
          COMMIT;
       END;
    END pk_acct_num_job;   
    /

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Parrallel Process

    Originally posted by lesstjm
    I want to kick of five seperate processes to run this acct numbers so I can't do it in one script.
    Each script would do the same thing ... try to lock a row, move on to the next one if it can't. You give them all the exact same list of account numbers to be processed, and the first process to try and lock a particular number will do the processing for it.

    so each of the processes is running the same program
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width