Hi all,
Suppose I have two tables, pluses and minuses, and for any given id I
maintain the invariant sum(pluses.plus) - sum(minuses.minus) = 0
At a given point in time the tables may be like this:
pluses
id plus desc1 100 'a'1 150 'b'1 25 'c'2 80 'a'2 70 'c' minuses
id minus desc1 120 'd'1 80 'e'1 75 'f'2 60 'd'2 50 'e'2 40 'f'
I want to ultimately produce HTML output that looks like this:
1 275 a=100,b=150,c=25 d=120,e=80,f=752 150 a=80,c=70 d=60,e=50,f=40
The problem is that the obvious (to me!) way to do it involves multiple
separate queries, so with the read committed isolation level it is
possible that the data presented (for a given id) will not be consistent.
I believe serializable isolation level would get around this, but at the
expense of me having to retry "manually" any data-altering queries.
Alternatively I could lock the tables with (I think) ShareLock, but this
may not be the best solution from a performance point of view.
Does anyone have any other suggestions?
Thanks
Ian