Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: Last processed/seen row

    I have a table, each new row gets a new id from a sequence. I want to observe new rows in this table and I cannot add new column "processed" to this table. Naive way is to store last processed/seen row id:

    select *
    from my_table
    where id > last_processed_id

    I said naive because this way I may skip rows. If there are concurrent transactions inserting rows into my_table, it can happen that T1 grabs new sequence ID1 before T2 grabs ID2 (ID1 < ID2) but T2 commits before T1. Now if I run the select just after T2 committed but before T1 committed, I will not see T1 row (still not commited), so last seen row will be ID2. Next select will skip ID1. Are there any other solutions besides creating another table (id, processed) next to the my_table?

    Thank you, Jan

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, you can't see something that doesn't exist. If ID1 record is not committed, it is not visible in MY_TABLE.

    Perhaps you could use a BEFORE INSERT database trigger which would populate ID value from a sequence. That way, a transaction wouldn't "reserve" an ID so all used sequence numbers will really be in MY_TABLE.

  3. #3
    Join Date
    Oct 2011
    Posts
    4
    Quote Originally Posted by Littlefoot View Post
    Perhaps you could use a BEFORE INSERT database trigger which would populate ID value from a sequence. That way, a transaction wouldn't "reserve" an ID so all used sequence numbers will really be in MY_TABLE.
    The question is, is this quaranteed? I doubt it, it will just minimize the window.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Coding triggers might be an interesting read for you (if you didn't read it yet).

    Here's how it goes, shortly: create a table, a sequence, and a trigger:
    Code:
    SQL> create table test
      2    (id       number   constraint pk_test primary key,
      3     ename    varchar2(20)
      4    );
    
    Table created.
    
    SQL> create sequence seq_dbf;
    
    Sequence created.
    
    SQL> create or replace trigger trg_test
      2    before insert on test
      3    for each row
      4  begin
      5    select seq_dbf.nextval
      6      into :new.id
      7      from dual;
      8  end;
      9  /
    
    Trigger created.
    Insert a record and check table contents
    Code:
    SQL> insert into test (ename) values ('Littlefoot');
    
    1 row created.
    
    SQL> select * from test;
    
            ID ENAME
    ---------- --------------------
             1 Littlefoot
    
    SQL>
    A trigger fired at the moment of inserting a record. In your case, that would be when one of those transactions (after long and tedious computing) finally decided to insert a record into a table. At that very moment, a sequence's next value is fetched, assigned to new record's ID (":new.id") and - along with employee's name - stored into a table.

    If another transaction decides to do the same, at the same moment - no problem. Oracle sequences guarantee that the same value won't be fetched twice, so another transaction would assign another SEQ_DBF.NEXTVAL into :NEW.ID and store it into a table.

    On the other hand, if you have a better idea, go ahead! Happy programming!

  5. #5
    Join Date
    Oct 2011
    Posts
    4
    Quote Originally Posted by Littlefoot View Post
    If another transaction decides to do the same, at the same moment - no problem. Oracle sequences guarantee that the same value won't be fetched twice, so another transaction would assign another SEQ_DBF.NEXTVAL into :NEW.ID and store it into a table.
    Thank you, but my problem is not uniqueness of the sequence value. That is guaranteed to be unique by Oracle no matter *when* you ask for NEXTVAL. The problem is that T2 may commit before T1 (see my initial question) and thus ID2 will be visible to outside world (on read committed) before ID1. Naive implementation observing new rows in that table will skip T1 row.

  6. #6
    Join Date
    Oct 2011
    Posts
    4
    Replying to myself: http://asktom.oracle.com/pls/asktom/...16998677475837, the ORA_ROWSCN and rowdependencies on is what I was after.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the ORA_ROWSCN and rowdependencies on is what I was after.
    if you are happy, then we are happy.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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