Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Foreach with commit

    The problem i will present is a Locks problem.

    I have many Store Procedures that do FOREACH to get several rows (information) from tables.
    The problem is that for each row i do severals operations like inserts, updates and reads thai will lock more rows maybe a page.

    Everythings goes fine until you use multithread. Severals threads will execute the same foreach that will search in same tables an lock this tables do several update and inserts and selects foreach row and could create a DEADLOCK.

    To prevent this i tried:

    1 - Inside the foreach (at the end) do COMMIT (this didn't work because the FOREACH cursor ends ).

    2- Make OLNY the Foreach and keep the data return in a collection. Then iterate the collection and do the operations,
    is better because the FOREACH is finish and tables will not been locked but still couldn't do the commit between the collection iterations

    The exemple under is only to give the ideia what is the problem.

    Exemple:
    FOREACH
    SELECT unique I.col1, II.col2, I.col3
    INTO var1,var2,var3
    FROM tab1 I, tab2 II
    WHERE InputValue = .....


    SELECT MAX(coln) INTO var4 FROM Tab WHERE col2 = var1....

    IF var4 <= .... THEN
    THEN
    EXECUTE PROCEDURE StoreProcedure1(var1,var2);

    EXECUTE PROCEDURE StoreProcedure2(var3,var2);

    IF NOT EXISTS(...)
    THEN
    INSERT INTO tab3()..........
    Update tab......
    ELSE
    ........................................
    END FOREACH;
    COMMIT;

    Anybory have any idea how i can do a FOREACH an make a COMMIT for each row without losing the cursor?

    regards
    António

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi Antonio,

    Please change for FOREACH xxx WITH HOLD FOR xxxx

    Gustavo.

Posting Permissions

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