How would you implement Rollback when you need to make sure that 2 updates and 2 inserts either all happen or are all rolled back?

Question

I have a MySQL stored procedure. Cut down, it looks like this.

START TRANSACTION

SELECT some_columns

SET some_variables

UPDATE row_in_balance_table

UPDATE row_in_entry_table

INSERT row_in_ledger_table

INSERT row_in_ledger_table

COMMIT;

I need all 4 rows to be updated/inserted, or none of them to be.

What is just a standard normal way to make this happen? I had considered something like, after each query

IF (SELECT ROW_COUNT() = 1 ) THEN 
    SET row_affected_counter = row_affected_counter + 1;
END IF;

And then, because I need to affect 4 total rows, just before the COMMIT I could use..

IF (row_affected_counter  != 4 ) THEN 
    ROLLBACK;
END IF;

COMMIT;

So I think my procedure should rollback on an error, because it's in a transaction, and rollback if any of the updates/inserts don't happen, because the counter won't reach the expected total of rows affected.

This didn't work though because it seems like ROW_COUNT doesn't reset to 0 if a follow insert/update is called.

Is there a better way to do this?

Answer 1

Add FOR UPDATE to the end of that SELECT (assuming it feeds info to the subsequent statements).

Let's see the declaration of row_affected_counter.