On Wed, Mar 01, 2006 at 05:42:57PM +0100, Denis Sbragion wrote: > Hello Andrew, > > On Wed, March 1, 2006 17:31, Andrew Piskorski wrote: > > Is that in fact true? I am not familiar with how PostgreSQL > > implements the SERIALIZABLE isolation level, but I assume that > > PostgreSQL's MVCC would still give some advantage even under > > SERIALIZABLE: It should allow the readers and (at least one of) the > > writers to run concurrently. Am I mistaken? > > PostgreSQL always played the "readers are never blocked" mantra. Nevertheless > I really wonder how the strict serializable constraints could be satisfied > without blocking the readers while a write is in place.
Simple: readers have to handle the possibility that they'll need to re-run their transaction. From http://lnk.nu/postgresql.org/8gf.html: UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row may have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the serializable transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the serializable transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the serializable transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update because a serializable transaction cannot modify or lock rows changed by other transactions after the serializable transaction began. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
