Re: PG Manual: Clarifying the repeatable read isolation example

Heikki Linnakangas-6 wrote
> On 05/27/2014 10:12 PM, Evan Jones wrote:
>> I was reading the Postgres MVCC documentation today (which is
>> generally fantastic BTW), and am slightly confused by a single
>> sentence example, describing possible read-only snapshot isolation
>> anomalies. I would like to submit a patch to clarify this example,
>> since I suspect others may be also confused, but to do that I need
>> help understanding it. The example was added as part of the
>> Serializable Snapshot Isolation patch.
>>
>> Link to the commit:
>> http://git.postgresql.org/gitweb/?p=postgresql.git;h=dafaa3efb75ce1aae2e6dbefaf6f3a889dea0d21
>>
>>
>>
>> I'm referring to the following sentence of 13.2.2, which is still in
>> the source tree:
>>
>> http://www.postgresql.org/docs/devel/static/transaction-iso.html#XACT-REPEATABLE-READ
>>
>> "For example, even a read only transaction at this level may see a
>> control record updated to show that a batch has been completed but
>> not see one of the detail records which is logically part of the
>> batch because it read an earlier revision of the control record."
>
> Hmm, that seems to be a super-summarized description of what Kevin & Dan
> called the "receipts problem". There's an example of that in the
> isolation test suite, see src/test/isolation/specs/receipt-report.spec.
> Googling for it, I also found an academic paper written by Kevin & Dan
> that illustrates it: http://arxiv.org/pdf/1208.4179.pdf, "2.1.2 Example
> 2: Batch Processing". (Nice work, I didn't know of that paper until now!)
>
> I agree that's too terse. I think it would be good to actually spell out
> a complete example of the Receipt problem in the manual. That chapter in
> the manual contains examples of anomalities in Read Committed mode, so
> it would be good to give a concrete example of an anomaly in Repeatable
> Read mode too. Want to write up a docs patch?

While this is not a doc patch I decided to give it some thought. The "bank"
example was understandable enough for me so I simply tried to make it more
accessible. I also didn't go and try to get it to conform to other,
existing, examples. This is intended to replace the entire "For example..."
paragraph noted above.

While Repeatable Read provides for stable in-transaction reads logical query
anomalies can result because commit order is not restricted and
serialization errors only occur if two transactions attempt to modify the
same record.

Consider a rule that, upon updating r1 OR r2, if r1+r2 < 0 then subtract an
additional 1 from the corresponding row.
Initial State: r1 = 0; r2 = 0
Transaction 1 Begins: reads (0,0); adds -10 to r1, notes r1 + r2 will be -10
and subtracts an additional 1
Transaction 2 Begins: reads (0,0); adds 20 to r2, notes r1 + r2 will be +20;
no further action needed
Commit 2
Transaction 3: reads (0,20) and commits
Commit 1
Transaction 4: reads (-11,20) and commits

However, if Transaction 2 commits first then, logically, the calculation of
r1 + r2 in Transaction 1 should result in a false outcome and the additional
subtraction of 1 should not occur - leaving T4 reading (-10,20).

The ability for out-of-order commits is what allows T3 to read the pair
(0,20) which is logically impossible in the T2->before->T1 commit order with
T4 reading (-11,20).

Neither transaction fails since a serialization failure only occurs if a
concurrent update occurs to [ r1 (in T1) ] or to [ r2 (in T2) ]; The update
of [ r2 (in T1) ] is invisible - i.e., no failure occurs if a read value
undergoes a change.

Inspired by:
http://www.sigmod.org/publications/sigmod-record/0409/2.ROAnomONeil.pdf -
Example 1.3

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PG-Manual-Clarifying-the-repeatable-read-isolation-example-tp5805152p5805170.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.