0

Using MariaDB I am running an insert on one table and then trying to run another insert on second table. Due to a constraint the second insert should only succeed if the first one was successful.

If I try to carry out both inserts within one transaction, the second insert fails with an integrity error. If I commit the first insert, the second insert is successful.

Am I correct in assuming that both insert should be possible within a single transaction?

The actual case I am looking at is slightly more complicated: There are two inserts without constraints and a third with a constraint which will only succeed if the first two inserts did. The third insert uses the tuple of the primary keys of the first two tables for its primary key.

3
  • Am I correct in assuming that both insert should be possible within a single transaction? Disabled auto-commit with further explicit or implicit commit or explicit rollback is enough. The table(s) must must be transactional-safe (InnoDB, NDB, etc.).
    – Akina
    Nov 10, 2023 at 17:29
  • @Akina - Disabling auto_commit is probably the same as doing START TRANSACTION;
    – Rick James
    Nov 11, 2023 at 0:32
  • "Constraint" is a rather broad term. Can you show a specific example?
    – Rick James
    Nov 11, 2023 at 0:33

1 Answer 1

0

The solution was to FLUSH rather than COMMIT after the inserts. This way the information needed to fulfil the constraint is available to the session and the third insert succeeds. The whole transaction can then be committed. If, however, an exception occurs, any inserts which were successful can be rolled back.

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.