Question Re: Snapshot isolation: SA12 vs PostgreSQL in Technology Q&A

Snapshot isolation: SA12 vs PostgreSQL

former_SQLA_member1694894 — Thu, 11 Aug 2022 04:43:49 GMT

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.

Re: Snapshot isolation: SA12 vs PostgreSQL

VolkerBarth — Thu, 11 Aug 2022 07:30:43 GMT

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.

Re: Snapshot isolation: SA12 vs PostgreSQL

VolkerBarth — Thu, 11 Aug 2022 11:08:48 GMT

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.