7

If I execute some SQL inside a transaction successfully, can it happens that the commit will fail? And what are possible causes? Can it fail related to the executed queries, or just due to some DB side issues?

The question comes up because I need to judge if it makes sense to commit transactions inside tests or if it is "safe enough" to just rollback after each test case.

Improve this question
3
  • Yes, a transaction can fail at commit time, if e.g. deferrable constraints are used. Commented Nov 12, 2015 at 15:02
  • 1
    Or if you run out of resources during commit, or if the server goes down, or whatever. The correct way to handle this is by using 2 Phase Commits. You prepare your commit, and once it's prepared, you can even commit the transaction after a server restart - all the data is still there. Commented Nov 12, 2015 at 18:32
  • I care about application issues. So if the server goes down or runs out of resources, that's of course an issue, but nothing I want to test. I care more about application based issues that cause the commit to fail. Commented Nov 12, 2015 at 23:30

1 Answer 1

Reset to default
13

If I execute some SQL inside a transaction successfully, can it happens that the commit will fail?

Yes.

And what are possible causes?

  • DEFERRABLE constraints with SET CONSTRAINTS DEFERRED or in a one-statement autocommit transaction. (Can't happen unless you use DEFERRABLE constraints)
  • SERIALIZABLE transaction with serialization failure detected at commit time. (Can't happen unless you use SERIALIZABLE transactions)
  • Asynchronous commit where the DB crashes or is shut down. (Can't happen if synchronous_commit = on, the default)
  • Disk I/O error, filesystem error, etc
  • Out-of-memory error
  • Network error leading to session disconnect after you send the commit but before you get confirmation of success. In this case you don't know for sure if it committed or not.
  • ... probably more

Can it fail related to the executed queries, or just due to some DB side issues?

Either. A serialization failure, for example, is definitely related to the queries run.

If you're using READ COMMITTED isolation with no deferred constraints then commits are only likely to fail due to underlying system errors.

The question comes up because I need to judge if it makes sense to commit transactions inside tests or if it is "safe enough" to just rollback after each test case.

Any sensible test suite has to cover multiple concurrent transactions interacting, committing in different orders, etc.

If all you test is single standalone transactions you're not testing the real system.

So the question is IMO moot, because a decent suite of tests has to commit anyway.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.