Question 357066 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is it guaranteed that constraint violation is flagged at the end of transactional block?

I’m using Postgres and have a transactional method defined as follows:

@Entity
public class SomeEntity {
    // …
}

@Transactional(isolation = READ_COMMITED)
public void persistUniqueAndSendEmail() {
    SomeEntity e = // …
    // Persis the entity to Postgres with a unique constraint that may fail

    // Once the entity is persisted send the notification email
}

With READ_COMMITED isolation level is it possible that the unique constraint violation is thrown when the transaction is actually committed, not the actual sql statement is executed?

If so that would mean that the email can be sent, but the relevant changes are not persisted.

Tags:

2

Answers


  1. 0

    The transaction doesnt finish until the end of the method. The save doesnt flush until the transaction finishing prompts it to. So yes, you could get a constraint violation that rolls back the save after the email is sent.

    You could try calling saveAndFlush instead of save (see Difference between save and saveAndFlush in Spring data jpa). The flush forces the database call to occur, which will trigger any constraint violation.

    But you dont want the transaction to fail for some other reason and have the email sent erroneously. The safe thing to do is make sure the transaction completes successfully, then send the email either in another service calling the transactional service, or in an AOP interceptor.

    Login or Signup to reply.
  2. 0

    You have a few problems here.

    1. Yes, it can. Specifically, constraint checks can be done on the spot or when you commit and it depends on the DDL, not the setup of the connection. In PSQL, such constraint checks are triggers and you can configure them: Don’t run (bad idea, but can be useful when you’re bulk importing), Run on the spot, and defer until commit. We have no idea how you configured your DB (what SQL you wrote to set up these constraints). By default constraints are checked as the statement is executed.

    2. Use SERIALIZABLE instead. Your database’s integrity will thank you.

    3. Regardless of the situation of where constraints are checked, a commit is never guaranteed. What if you send out the email and then someone trips over the power cable? If you reverse the situation and commit, then send the email, what happens if someone trips over a power cable after the commit but before the email is sent?

    You’re stuck here in a 2-system commit cascade. You have 2 things that both aren’t atomic and you want either both to occur (an email is sent and the db row is updated), or neither to occur. But that is not possible – 2 disparate systems can’t do that.

    You have many ways out of the dilemma and they’re all bad. Some general aspects:

    • Remember that spaceballs movie scene with ‘preparing to rewind!’? Do that. Log somewhere (perhaps in the database): "About to send an email", "Email sent" – where each log action is fully flushed before proceeding. Then, on boot of the system, scan your dbs and check if the last event isn’t one that has returned the system to a stable state. If it has not, use what you have logged to restore state. This is exactly how journalling file systems work.

    If the last entry is ‘about to send an email’ then one of two things has happened:

    • The email was sent, but before you managed to log ’email sent’, someone tripped over a power cord. The correct act is to mark off the entry as ‘… and the email has been sent’.
    • The email was not sent as someone tripped over a power cord before it went out. The correct act is to send it.

    You can’t solve the dilemma without a separate API or other system that lets you check what happened. For example, if you’re sending via gmail y ou could use its API to check the sent box. Worst comes to worst, on boot you just hard crash and demand that a human operator update the entry to explain precisely what the fate is of that email. Or, pick some default (when in doubt, send twice – for example. i.e. assume the email did not go out, so to restore state, send it again).

    This is quite complicated but the only way to atomicize 2 disparate systems.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search