Sample Header Ad - 728x90

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?

0 votes
1 answer
146 views
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?
Asked by T M (11 rep)
Nov 6, 2023, 08:33 PM
Last activity: Jul 24, 2025, 08:03 PM