0

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?

3
  • 1
    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
    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
    Nov 6, 2023 at 21:55
  • 1
    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. Nov 6, 2023 at 23:12

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.