1

In PostgreSQL (and other MVCC databases), transactions can rollback due to a deadlock or serialization error. Assume two transactions are currently running, is it ever possible that both, instead of just one, transaction will fail due to this kind of errors?

The reason why I am asking is that I am writing a retry implementation. If both transactions can fail, we might end up in a never-ending loop of retries if both retry immediately. If only one transaction can fail, I don't see any harm in retrying as soon as possible.

Lesly O
  • 123
  • 6

1 Answers1

1

Yes. A deadlock can involve more than two transactions. In this case more than one may be terminated. But this is an extremely rare condition. Normally.

If just two transactions deadlock, one survives. The manual:

PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete.

Serialization failures only happen in REPEATABLE READ or SERIALIZABLE transaction isolation. I wouldn't know of any particular limit to how many serialization failures can happen concurrently. But I also never heard of any necessity to delay retrying.

I would retry as soon as possible either way.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you. Could it be that *all* involved transactions are aborted (in case of deadlock or serialization errors)? Or is there always at least one surviving transaction? In the first case, retrying as soon as possible might not be a good idea, I suppose. – Lesly O Jan 13 '18 at 12:33
  • @LeslyO: Serialization errors are a different matter with `SERIALIZABLE` transaction isolation, not with the default `READ COMMITTED`. As for deadlocks: I have never seen more than one being terminated, and the manual also informs that only one transaction at a time is terminated. But transactions might be rolled back due to other, unrelated exceptions, which does not seem any more unlikely. – Erwin Brandstetter Jan 13 '18 at 13:36
  • Thanks again. Could you please explain what you mean with `Serialization errors are a different matter with SERIALIZABLE transaction isolation, not with the default READ COMMITTED`. I know the difference between the isolation levels, but shouldn't all isolation errors, regardless of the level, be treated the same way as deadlock errors: retry the transaction? If I should open a new question for this, let me know. – Lesly O Jan 13 '18 at 14:45
  • I focused on deadlocks and lost serialization errors out of sight, which you also included in the question. Also, to be precise, those occur in `REPEATABLE READ` or `SERIALIZABLE` transaction isolation. I wouldn't know of any particular limit to how many serialization failures can happen concurrently. But I also never heard of any necessity to delay retrying. – Erwin Brandstetter Jan 13 '18 at 15:30
  • Thank you for sharing your knowledge. – Lesly O Jan 13 '18 at 15:36