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?

CodePudding user response：

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;

CodePudding user response：

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.

CodePudding user response：

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).