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.
SELECT unique I.col1, II.col2, I.col3
FROM tab1 I, tab2 II
WHERE InputValue = .....
SELECT MAX(coln) INTO var4 FROM Tab WHERE col2 = var1....
IF var4 <= .... THEN
EXECUTE PROCEDURE StoreProcedure1(var1,var2);
EXECUTE PROCEDURE StoreProcedure2(var3,var2);
IF NOT EXISTS(...)
INSERT INTO tab3()..........
Anybory have any idea how i can do a FOREACH an make a COMMIT for each row without losing the cursor?