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 <klep...@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
signature.asc
Description: Digital signature