Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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??


asked 11 Aug '22, 04:43

NCister's gravatar image

accept rate: 0%

edited 11 Aug '22, 10:13

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%20Barth's gravatar image

Volker Barth
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
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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

Question tags:


question asked: 11 Aug '22, 04:43

question was seen: 721 times

last updated: 11 Aug '22, 11:08