I have multiple processes inserting into a Postgres (10.3) table using the SERIALIZED isolation level.

Another part of our system needs to read these records and be guaranteed that it receives all of them in sequence. For example, in the picture below, the consumer would need to

select * from table where sequanceNum > 2309 limit 5

and then receive sequence numbers 2310, 2311, 2312, 2313 and 2314.

The reading query is using READCOMMITTED isolation level.

What I'm seeing though is that the reading query is only receiving the rows I've highlighted in yellow. Looking at the xmin, I'm guessing that transaction 334250 had begun but not finished, then transactions 334251, 334252 et al started and finished prior to my reading query starting.

My question is, how did they get sequence numbers interleaved in those of 334250? Why weren't those transactions blocked by merrit of all of the writing transactions being serialized?

Any suggestions on how to achieve what I'm after? Which is, a guarantee that different transactions don't generate interleaving sequence numbers? (It's ok if there are gaps.... but they can't interleave).

Thanks very much for your help. I'm losing hair over this one!

PS - I just noticed that 334250 has a non zero xmax. Is that a clue that I'm missing perhaps?

enter image description here

  • I just read the docs on xmax - "The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back". I'm not aware of anything that is trying to delete those rows (unless it's a failed insert??)... I'm not sure of the significance of that 33. Sep 20, 2018 at 6:43
  • 1
    Question duplicated and answered here stackoverflow.com/q/52432459/494635 Sep 22, 2018 at 9:55


Your Answer

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

Browse other questions tagged or ask your own question.