Home » SQL & PL/SQL » SQL & PL/SQL » Fetch and update a single record exclusively (Oracle 10g Release 10.2.0.1.0 - 64bit, RHEL )
icon5.gif  Fetch and update a single record exclusively [message #422580] Thu, 17 September 2009 08:40
johnbach
Messages: 32
Registered: June 2009
Member
I have a muti-instance of a process,which does

1. Fetch a single record with minimum NEXT_ATTEMPT(DATE) value exclusively(lock).
2. Process it
3. If processing==success
update this record - increment NEXT_ATTEMPT
commit;
4. else rollback; -- release lock
5.sleep for some time and continue from 1

* No two instance should pick the same record simultaneously

TABLE STRUCTURE:
CREATE TABLE SAF
(
  TXNID         VARCHAR2(20 BYTE) PRIMARY KEY,
  STATUS        CHAR(1 BYTE)     ,
  ATTEMPTS      NUMBER           ,
  NEXT_ATTEMPT  DATE             
);


SAMPLE PRO*C PSEUDO CODE
 SELECT TXNID INTO :TXNID  FROM SAF
      WHERE
      NEXT_ATTEMPT=(SELECT MIN(NEXT_ATTEMPT) 
FROM SAF 
WHERE STATUS!='A' AND NEXT_ATTEMPT<=SYSDATE AND ATTEMPTS<10) 
AND
      STATUS!='A' AND ATTEMPTS<10 AND
      ROWNUM=1 FOR UPDATE;
         
   //processing==success   
   UPDATE  SAF SET  ATTEMPTS=ATTEMPTS+1, NEXT_ATTEMPT=NEXT_ATTEMPT+(10/(24*60*60))
          WHERE TXNID=:TXNID;
        commit;
    else
        rollback;



Is there any other effective way to do this?

[Updated on: Thu, 17 September 2009 09:08] by Moderator

Report message to a moderator

Previous Topic: Insert or update using dbms_xmlsave
Next Topic: Getting Financial Year Begining
Goto Forum:
  


Current Time: Thu Dec 08 01:54:16 CST 2016

Total time taken to generate the page: 0.08188 seconds