D
David Abdala
Guest
I think the problem has nothing to do with transaction "atomicity", but with the default ON ERROR of FOR EACH. The fact that FOR EACH has an implicit ON ERROR UNDO, NEXT, means that a loop that fails, is simply ignored. This implies that the transaction is not undone in its entirely, but taken to the exact point it was at the beginning of the loop. The fact that some records got updated and some didn't means the ON ERROR UNDO, NEXT is working properly, not that the transaction is not atomic. This is the same as other databases that are capable of having multiple rollback points in the same transaction (most of them these days). While you are in a "global" transaction, you can set inner transaction point to wich you can undo the changes, and continue in the "global" transaction. I'm not sure a BLOCK-LEVEL solves it, as I don't recall if that affects default ON ERROR. The only "solution" to this program flow problem is to modify every FOR EACH, to have an ON ERROR UNDO, LEAVE, having a logical flag to know if the for each was successfull in a whole or not, and undoing the transaction based on this flag. You can pull this off too with ON ERROR UNDO, RETRY. I have been biten by this FOR EACH "feature" serveral times, still do (last week),,,
Continue reading...
Continue reading...