Commit instead of rollback?

Question

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?

so for every id you start a new transaction, so the cmit makes nothing at all as doesn the rollback in your cqase as it is only 1 comand — nbk
– nbk, Commented Oct 24, 2021 at 19:42
Your pseudo-code makes little sense. Based on what you've posted here I'd be looking to perform whatever someFunction(id) does as part of the UPDATE query and do away with the whole START TRANSACTION | COMMIT | ROLLBACK thing altogether. — Tangentially Perpendicular
– Tangentially Perpendicular, Commented Oct 24, 2021 at 20:24
Just omit COMMIT and ROLLBACK and commit the transaction after your code is done. — Laurenz Albe
– Laurenz Albe, Commented Oct 24, 2021 at 21:00

Belayer · Accepted Answer · 2021-10-25 02:35:22Z

2

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 Oct 25, 2021 at 2:35

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kmoser Over a year ago

Probably better to do SELECT id, name FROM employees WHERE ( id >= 0 AND id < 100 ), then iterate through each fetched row. That would avoid multiple SELECT calls, which are more expensive than simply fetching results from a query which has already been performed.

user330315 Over a year ago

This is not entirely true. In Postgres SELECT starts a transaction if autocommit is disabled and to end that transaction you would need a commit or rollback, otherwise the connection remains the state "idle in transaction". In MySQL when autocommit is disabled, it might also be necessary to end a transaction started by a SELECT to see changes done by other transactions if the default REPEATABLE READ isolation is in effect.

tihiyev Over a year ago

@a_horse_with_no_name in my example do I have to use commit or rollback if the example will work on two databases and it does not matter if I use commit or rollback? Please add a new answer.

kmoser · Accepted Answer · 2021-10-25 03:17:46Z

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

Perform a single SELECT, with the intention of reducing the number of queries
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).

ysth · Accepted Answer · 2021-10-25 16:17:20Z

1

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.

edited Oct 25, 2021 at 16:17

answered Oct 25, 2021 at 0:51

ysth

99.1k6 gold badges126 silver badges220 bronze badges

Collectives™ on Stack Overflow

Commit instead of rollback?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related