Snapshot isolation: SA12 vs PostgreSQL

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.

isolation-level snapshot sa-12

asked 11 Aug '22, 04:43

NCister
211●11●12●19
accept rate: 0%

edited 11 Aug '22, 10:13

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 11 Aug '22, 07:30

Volker Barth
39.5k●355●539●811
accept rate: 34%

edited 11 Aug '22, 07:54

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.

(11 Aug '22, 11:08) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438
isolation-level ×12
snapshot ×7

question asked: 11 Aug '22, 04:43

question was seen: 362 times

last updated: 11 Aug '22, 11:08

SAP SQL Anywhere

Snapshot isolation: SA12 vs PostgreSQL

Follow this question

Related questions