-1

Simple example (PSEUDO CODE):

for (int i = 0; i < 100; i++) {
    START TRANSACTION;  
    SELECT id, name FROM employees WHERE id = i;

  
    IF (someFunction(id)) {
        ROLLBACK;
        CONTINUE; // GO TO NEXT EXECUTION OF FOR LOOP
    }

    UPDATE company SET good = good + 1;

    COMMIT;
}

Can I use in this example COMMIT (so I'm gonna have two COMMIT in my script) instead of ROLLBACK?

Does it make any difference to the database if I use COMMIT instead of ROLLBACK after select?

Is there any difference between MySQL and PostgreSQL here?

3
  • This example of SQL is not idiomatic. What you should do is select a list of IDs you want to test, perform the test, filter the IDs that have failed the test, and finally apply the update to increment the rows whose IDs passed the test. There would be no need, using such an approach, to either have a hand-rolled loop, or any use of rollback whatsoever.
    – Steve
    Oct 24 at 16:15
  • In what language does "BREAK;" go to next execution of for loop? In all the ones that I know, break; stops/exits a loop. You're looking for continue;, I think.
    – Erik Eidt
    Oct 24 at 16:19
  • @ErikEidt yes, thank you.
    – tihiyev
    Oct 24 at 17:27
0

It doesn't make any difference to use COMMIT or ROLLBACK in your loop within the if block, unless the someFunction(id) method/function has hidden effects that write to the database.

I don't think this is a matter of SQL flavor at all.

1
  • Agreed. The existence of side effects from the function call, would appear to be the only possible explanation for why there is any use of the rollback statement.
    – Steve
    Oct 24 at 16:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.