none
ORA-02049 while polling oracle via stored procedure RRS feed

  • Question

  • I did see the other thread about increasing the polling time, this didn't seem to help.

    We added a trigger to our ERP system's oracle table that basically copies all changes to a "copy" table. I cannot touch the actual table, and with the copy we added an additional field that we can use to mark processing status.

    I'm polling oracle every 2 minutes for the first 1k records. I can watch the processor and the admin console and verify that these 1000 records are completed processing in about 20 seconds. I can verify that the records are updated with my post poll statement to a status of "null" meaning they are current.

    We are seeing dead locks on the trigger, and I'm recieving ORA-02049 on my side during times of heavy load.

    Has anyone dealt with this?

    ---- COPY and PASTE of my oracle procedures -----

    CREATE or REPLACE PACKAGE process_items_pk AS
     TYPE item_ref_type IS REF CURSOR RETURN TICCRM.TTIITM001100_COPY%ROWTYPE;
     PROCEDURE get_items(outrecs OUT item_ref_type);
     PROCEDURE set_items;
    END process_items_pk;

    CREATE or REPLACE PACKAGE BODY process_items_pk AS
     PROCEDURE get_items(outrecs OUT item_ref_type) IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     BEGIN
      UPDATE TICCRM.TTIITM001100_COPY SET REFLAG = '1' WHERE REFLAG = '0' AND ROWNUM < 1001;
      COMMIT;
      OPEN outrecs FOR SELECT * FROM TICCRM.TTIITM001100_COPY WHERE REFLAG = '1';
     END get_items;
     PROCEDURE set_items IS
     PRAGMA AUTONOMOUS_TRANSACTION;
     BEGIN
      UPDATE TICCRM.TTIITM001100_COPY SET REFLAG = NULL WHERE REFLAG = '1';
      COMMIT;
     END set_items;
    END process_items_pk;

    -------------------------------------------------

    I'm polling via the "get_items" procedure and calling "set_items" as my post poll statement.

    Thursday, May 20, 2010 9:21 PM

Answers

All replies

  • Hi,

    I haven't dealt with this problem, but the ORA-02049 can be dealt with according to tips provided here.

    HTH

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Friday, May 21, 2010 6:07 AM
    Moderator
  • Steff-Jan:

    This was, unfortunately, one of the top hits in google... so we've already tried it :)

     

    All:

    I just spoke with our Oracle DBA. It appears that the oracle adapter isn't always releasing it's lock. We can verify that the trigger is getting hung up because there is a lock on the copy table. The only two processes touching the copy table are the trigger and my biztalk poll. We actually locked up our ERP yesterday afternoon late and had to disble the trigger to allow our users to continue working.

    the trigger is an "on after insert or update" into the ERP table. If there is an update on the ERP side, the update is also copied to my table, if there is an insert, the data is also inserted to my side. Either of these actions adds a 'reflag' value of '0' telling me that the record has been modified and needs processed. I'm then using the stored procedure above to process through the table.

    Friday, May 21, 2010 12:50 PM
  • What are your transaction settings? What version of Oracle adapter are you using?
    Tuesday, May 25, 2010 5:51 AM
  • Transaction settings are pretty much all default. I'm polling every 2 minutes using ambient transaction = true.

    I'm current on the adapter and using oracle client 11g.

    Tuesday, May 25, 2010 12:46 PM
  • Thing to try #1: Does it help if you set use ambient transaction to false? I think you dont need to flow the transaction from bizatlk to oracle as you are using  PRAGMA AUTONOMOUS_TRANSACTION

    Thing to try #2: (See http://msdn.microsoft.com/en-US/library/dd787787(v=BTS.10).aspx)

    Poll statement : SELECT * FROM TABLE WHERE FLAG = 0 FOR UPDATE;

    Post poll statement: UPDATE TABLE SET FLAG = NULL WHERE FLAG = 0;

    And use TransactionIsolationLevel = Serializable - how to set is described here: http://msdn.microsoft.com/en-US/library/dd787944(v=BTS.10).aspx

     

    Tuesday, May 25, 2010 2:32 PM
  • I've tried ambient trans to false on a test server (no load so it doesn't produce the error to begin with), but I can't explain well enough WHAT that does to get approval to change it on production. Can you provide information about what ambient transaction actually does?

    I'll read through the rest of the suggestions and let you know what we find.

    Thanks!

    Tuesday, May 25, 2010 2:40 PM
  • I read over #2 but there is a cautionary note:

    "You can have scenarios where in the time window between the polling and post-poll statements, more records are added to the table that meet the condition of the post-poll statement. In such situations, the post-poll statement would update all the records that satisfy the condition and not just the records selected as part of the polling statement."

     

    Our issue is that this particular table on our ERP system get ALOT of use, sometimes up to 20k transactions an hour.

    This is a problem for me that would likely require the use of a cursor to "hold my place" on the rows I selected FOR UPDATE. I'm not familiar with using them, but I'm reading about it and it may be the way.

    Tuesday, May 25, 2010 3:22 PM
  • That is exactly why I suggested using Transaction isolation level serializable. You might want to bing and see what guarantees Serializabe transaction gives you.
    Wednesday, May 26, 2010 9:52 AM
  • Interesting:

    http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html find the serializable section.

     

    It seems strange to me that the MS article above has that cautionary note about additional rows, but doesn't explain that serilizable guarentees and identical postpoll to my poll.

    I'll have to try this and let you know. It seems like this might cause more locking issues than the current poll-update-update method.

    Wednesday, May 26, 2010 12:46 PM
  • Hello Matthew

    I am Struggling with the an issue simular to the Question you asked here: http://www.eggheadcafe.com/software/aspnet/35592183/polling-oracle-wstored.aspx

    I seems like you found a solution on how to poll using SP, can you share that with me as I cant seem to find any onlineressouces describing this? How is the PollingStatement for this defined?

    Best Regards

    Jesper Bergmann

    Friday, June 11, 2010 8:23 AM