Skip site navigation (1) Skip section navigation (2)

Re: incoherent view of serializable transactions

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: incoherent view of serializable transactions
Date: 2008-12-22 17:54:59
Message-ID: 20081222175459.GA23253@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Dec 22, 2008 at 11:00:53AM -0600, Kevin Grittner wrote:
> As I've understood limitations of the PostgreSQL implementation of
> SERIALIZABLE transactions, at least the only example given in the
> documentation, revolve around a rather unlikely situation:
>  
> Given concurrent transactions T1 and T2 and non-overlapping sets of
> data A and B, T1 reads data including A and uses the data to modify B
> while T2 reads data including B and uses that data to modify A, where
> the modifications performed by either would affect the modifications
> made by the other, if they were visible.

In so far as the "modifications" are just INSERTs (no UPDATEs or
DELETEs), yes. This case is covered in the documentation.

> Imagine, as an example, a system which involves recording receipts,
> each of which must go into a daily deposit.  There is a control table
> with one row containing the current deposit date for receipts. 
> Somewhere mid-afternoon that date is updated, all subsequent receipts
> fall into the new day, and a report is run listing the receipts for the
> day and giving the deposit total.

This is a variation of the above and has the same "proper" solution:
predicate locking. However, in this case the records in question are
already present so you can workaround it easily. First do a SELECT FOR
UPDATE on all the records you want to update. This will serialize all
parallel transactions to either before or after you. Then do your
update.

> This absolutely can't happen in a standard-compliant implementation.
> At a minimum, this window where visible data lacks coherency should be
> noted in the documentation.  I don't know if there's any way to fix
> this without killing performance.

Predicate locking is nasty and we don't try. I'm not sure if anybody
else does.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.

In response to

Responses

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2008-12-22 18:37:45
Subject: Re: incoherent view of serializable transactions
Previous:From: Kevin GrittnerDate: 2008-12-22 17:00:53
Subject: incoherent view of serializable transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group