Snapshot isolation: SA12 vs PostgreSQL

[former_SQLA_member1694894]

Hi all
Sa12 (my version is 12.0.1) has 3 snapshot isolation types:
- snapshot
- statement-snapshot
- readonly-statement-snapshot

My questions are:
1. Which of these is comparable to PostgreSQL MVCC (by default it use read committed isolation level and read operations ignore uncommitted and locked rows ...) ?
2. Where exactly are temporary row informations stored (... to be able to control their occupation and behavior over time ...) ?
3. Can anyone confirm that all three types are "read committed" isolations (.. since it is set with the same instruction: SET OPTION isolation_level '...') ??
4. In PostgreSQL, when a row is updated, a new version of the row is created (-> "isolated") and inserted into the table.
In SA12 seems that rows are copied when data are read (too) ... Is it right ? .. Isn't this strategy more expensive??

Thanks.

[VolkerBarth]

As to the docs your third question is confirmed:

When you use snapshot isolation in a transaction, the database server returns a committed version of the data in response to any read requests. (*)

Can't comment on the PostgreSQL behaviour but for row versioning details, see my older question here.

---

(*): Unless it's "readonly-statement-snapshot" combined with "read uncommitted" and you are using updatable statements, see here.

[VolkerBarth]

See the linked question und Glenn's inevitable sound explanation, SQL Anywhere does also only create copies of the "before state" when an update occurs, not when a row is read.

Those rows are contained in the temporary file so I don't think you can "select" those explicitly, see the database property "VersionStorePages" for a number of those pages.

I guess to check the behaviour, you need to have according read transactions that access older vs. newer versions of some rows, see this simple sample from the docs.