Re: Reliable and fast money transaction design

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> I had the impression that, when working in READ COMMITTED mode, you
> could see (for instance) _new_ rows that were INSERTed by others who
> were also doing work. In SERIALIZABLE, you couldn't. So in cases
> where the additional rows met criteria in your WHERE clause late in
> your transaction, SERIALIZABLE had to throw them away. For instance,
> in READ COMMITTED, in a longish transaction, the "WHERE processed on
> IS NULL" might match more rows than were available at the beginning
> of the transaction when you SELECTed them, but in SERIALIZABLE, you
> can't see those additional rows. Is that wrong? (If so, I'm going
> to have to spend some time working out clarifications for the manual.)
> And doesn't the SERIALIZABLE transaction have to figure out that
> "this row doesn't count for me"?

Sure, but so does READ COMMITTED. Both of them work with MVCC
"snapshot" data structures that tell them which tuples to consider good
and which were changed too recently to consider visible. The only
difference is that SERIALIZABLE takes one snapshot at transaction start
and works with that for the whole transaction, whereas READ COMMITTED
takes a new snap for each statement.

It's certainly true that a later snapshot might allow more tuples to be
seen, but it might allow fewer as well. I don't see that that argument
proves anything in general --- it'd all depend on details of how your
application works.

regards, tom lane