Обсуждение: postgresql's MVCC implementation
Hi, I read a description of MVCC in http://www.cs.ust.hk/~dimitris/CS530/L24.ppt and found that this isn't exactly what is implemented in PostgreSQL. For example, for a sequence of operations like: 1: T1 sets isolation to serializable & begins a transaction 2: T2 sets isolation to serializable & begins a transaction 3: T1 reads X into v1 4: T2 reads Y into v2 5: T1 writes v1 into Y 6: T2 writes v2 into X 7: T1 commits 8: T2 commits Obviously, this sequence is also not a serializable execution. However, it is allowed by PostgreSQL. Moreover, according to the MVCC reference above, step 5 should really fail because the read timestamp of Y is that of T2, which is greater than that of T1. I understand that PostgreSQL doesn't implement predictive locking, but this example doesn't involve any phantom at all. It is plain multiversion timestamp concurency control. Any idea? Thanks in advance! ----- -- Kent Tong Wicket tutorials freely available at http://www.agileskills2.org/EWDW Axis2 tutorials freely available at http://www.agileskills2.org/DWSAA -- View this message in context: http://www.nabble.com/postgresql%27s-MVCC-implementation-tp18302020p18302020.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Kent Tong <kent@cpttm.org.mo> writes:
> 1: T1 sets isolation to serializable & begins a transaction
> 2: T2 sets isolation to serializable & begins a transaction
> 3: T1 reads X into v1
> 4: T2 reads Y into v2
> 5: T1 writes v1 into Y
> 6: T2 writes v2 into X
> 7: T1 commits
> 8: T2 commits
> Obviously, this sequence is also not a serializable execution. However, it
> is allowed by
> PostgreSQL. Moreover, according to the MVCC reference above, step 5 should
> really
> fail because the read timestamp of Y is that of T2, which is greater than
> that of T1.
If you want that to fail, use a SELECT FOR UPDATE at steps 3/4.
My interpretation of MVCC is that the above example isn't even
meaningful, because it assumes that "writing into Y" is an overwrite,
which it is not in Postgres --- that is, if T2 reads Y again, it'll
get the same value as before.
regards, tom lane
Tom Lane-2 wrote: > > If you want that to fail, use a SELECT FOR UPDATE at steps 3/4. > > My interpretation of MVCC is that the above example isn't even > meaningful, because it assumes that "writing into Y" is an overwrite, > which it is not in Postgres --- that is, if T2 reads Y again, it'll > get the same value as before. > Hi Tom, Thanks for your reply. I think what I'd like to know is the exact meaning of MVCC as implemented in PostgreSQL. It seems that a transaction (with isolation set to serializable) will always read the values as if they were when the transaction started. If it is the case, why? Is MVCC not well defined? Could say Oracle or MS SQL implement it differently? ----- -- Kent Tong Wicket tutorials freely available at http://www.agileskills2.org/EWDW Axis2 tutorials freely available at http://www.agileskills2.org/DWSAA -- View this message in context: http://www.nabble.com/postgresql%27s-MVCC-implementation-tp18302020p18309342.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Kent Tong <kent@cpttm.org.mo> writes:
> Is MVCC not well defined?
It's not defined by the SQL standard, nor any other standard that I know
of. So yes, different implementations might mean subtly different
things by it.
regards, tom lane
Tom Lane-2 wrote: > > It's not defined by the SQL standard, nor any other standard that I know > of. So yes, different implementations might mean subtly different > things by it. > OK, I see. Where can I find out the precise meaning of MVCC as in PostgreSQL. I've read http://www.postgresql.org/docs/8.3/interactive/transaction-iso.html and wondering if there is any more detailed description. Thanks! ----- -- Kent Tong Wicket tutorials freely available at http://www.agileskills2.org/EWDW Axis2 tutorials freely available at http://www.agileskills2.org/DWSAA -- View this message in context: http://www.nabble.com/postgresql%27s-MVCC-implementation-tp18302020p18309553.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.