Re: How is statement level read consistency implemented?

On Tue, Apr 22, 2008 at 12:41:08PM +0200, Thomas Kellerer wrote:
> I'm referring to the scenario that Tom Kyte describes here:
> www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html
> and how PG would detect that row 342,023 has been modified by a different
> transaction (and thus reads the correct value, unlike "the other database")

Check the docs, but the basic ideas are:
- Every transaction has a transaction ID and a snapshot which
identifies the transactions whose results it is allowed to see.
- Every row has a start and end transaction ID which identifes who
created it and who deleted it.
- Each row also has a ctid pointer which points to the newer version of
that row.

What will happen in this case is that the updating transaction will
duplicate the row, setting the end xid of the old version and the
start xid of the new version to its own xid. Any parallel scan will see
either the new version or the old version depending on its snapshot.

If you end up trying to update a row someone else updated in the
meantime, the ctid is followed to the new version, the tuple is
retested for matching and then updated.

See http://www.postgresql.org/docs/8.3/interactive/mvcc.html

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.