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

Hi Antonio,

Please change for FOREACH xxx WITH HOLD FOR xxxx

Gustavo.