Commit instead of rollback?

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 answers

  • answered 2021-10-25 00:51 ysth

    So I understand your question to be asking if ROLLBACK or COMMIT is better when, after only a select, you determine that no changes are going to be made in this transaction.

    As far as mysql is concerned, there's no reason to do either rollback or commit; since no changes have been made, neither does anything, and doing neither causes no issues. But it is not clear what you hope to accomplish by having the select inside the transaction in the first place, or by having a separate transaction for each iteration of the loop. If you were to provide more information, you would get better advice.

  • answered 2021-10-25 02:35 Belayer

    Select by itself does not require either rollback nor commit. Those are needed only after DML (insert, update, delete). Further it is typically considered better to have only 1 commit in a transaction. The idea being the entire transaction completely succeeds or completely as a unit. so your pseudo code becomes:

    START TRANSACTION;   
    for (int i = 0; i < 100; i++) {
        SELECT id, name FROM employees WHERE id = i;
    
        IF NOT (someFunction(id)) {
           UPDATE company SET good = good + 1;  
        }
    }
    COMMIT;
    

  • answered 2021-10-25 03:17 kmoser

    Here is a variation of @Belayer's answer, in which I made the following changes:

    1. Perform a single SELECT, with the intention of reducing the number of queries
    2. Keep a running total of the number of times company.good should be incremented, before finally incrementing it with a single UPDATE.
    new_good = 0;
    SELECT id, name FROM employees WHERE id >= 0 AND i < 100
    for each fetched row {
        IF NOT (someFunction(id)) {
           new_good++;
        }
    }
    START TRANSACTION /* Probably not needed */
    UPDATE company SET good = good + new_good;  
    COMMIT /* Probably not needed */;
    

    This probably eliminates the need for a transaction entirely, since any failed call to SELECT or fetch will result in no change to new_good, so when it eventually gets to the UPDATE, new_good will still contain a valid value (even if 0).

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum