Results 1 to 3 of 3

Thread: Read Locks

  1. #1
    Join Date
    Dec 2009

    Unanswered: Read Locks

    I wanted to know how to implement the below in Oracle

    Table : Event
    Records In Event Table

    EventId EventName EventStatus
    1 Agent1 New
    2 Agent2 New
    3 Agent3 New
    4 Agent1 New
    5 Agent5 New

    Now if a select is issued on Event Table like

    select * from event where EventStatus = 'New' and rownum <=2

    It returns 2 records
    EventId EventName EventStatus
    1 Agent1 New
    2 Agent2 New

    But at the same time if the same select statement is issued from a different session then it should not return the same 2 records but different 2 records

    The objective is to process different set of records where the EventStatus = 'New' at the same time

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    You may be able to use FOR UPDATE SKIP LOCKED. This is documented for 11G but existed undocumented in earlier releases:
       cursor c is
          select * from event where EventStatus = 'New'
          for update skip locked;
       l_row c%rowtype;
       open c;
       for i in 1..2 loop
          fetch c into l_row;
          exit when c%notfound;
          -- process this row
       end loop;
       close c;

  3. #3
    Join Date
    Oct 2002
    Cape Town, South Africa
    Nice one Tony, that is truely outstanding. Would never have thought of that. I immediately started thinking of Advanced Queueing, but your solution is so much simpler code wise.

Posting Permissions

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