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?

Why do you feel the need to add your own logic to the built-in transaction support in a DBMS? This Q&A is form SQL Server, but it applies in your case too, perhaps it'll help. — mustaccio
– mustaccio, Commented Nov 6, 2023 at 21:28
I thought there might be potential situations where something causes an insert or update to not happen, but it's not actually an error, so it doesn't get rolled back. So it seemed safer to me to count the inserts/updates as they happen just to make sure. Would you say this is the wrong way to think about it? — T M
– T M, Commented Nov 6, 2023 at 21:55
You don't need to check the inserts, either they happened or they errored. Only the updates need checking in case it couldn't find the row. — Charlieface
– Charlieface, Commented Nov 6, 2023 at 23:12

Rick James · Accepted Answer · 2023-11-10 18:23:00Z

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.

Stack Exchange Network

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?

1 Answer 1

Linked

Hot Network Questions

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?

1 Answer 1

Linked

Related

Hot Network Questions