temporary transaction specific snapshot isolation

[Former Member]

I have 4 SQL statements: select update insert (optional depending on application logic) select

I would like these 4 DML statements to participate in a single snapshot isolated transaction. Once a commit is issued then snapshot isolation should no longer apply.

The question I have is, how can I specify snapshot isolation for the given transaction only, until a COMMIT is issued.

The documentation states "SET statement [T-SQL] Sets database options for the current connection in an Adaptive Server Enterprise-compatible manner."

which seems to suggest that it is set for the existing connection, not transaction. The BEGIN TRANSACTION does not have an option to set the isolation level, which is what PostgreSQL allows for. Oracle allows for a similar feature with SET TRANSACTION.

Then once a COMMIT isissued they both default back to what the server setting is, which is READ COMMITTED.

[Former Member]

I think I found something in the documentation that answers my question:

SET TEMPORARY OPTION isolation_level = 2;

Is that the correct way to set a transaction to snapshot isolation ?

[Breck_Carter]

Yes, that is the correct way to temporarily set the isolation level on the current connection.

No, that won't set it to snapshot isolation, it will set it to repeatable read.

Read all about isolation levels here.

Warnings: (1) Snapshot isolation is a big deal, and it may not be what you want. (2) Isolation levels affect SELECT and other read operations, so setting it on the connection doing the DML operations may not have the desired effect.

Please describe what you are trying to accomplish, in terms of the application requirements.

[Former Member]

Yeah, I realised late yesterday that

set temporary option isolation_level = 'snapshot'

is in fact the command.

The other question I have is, does one need to set this command as a one-off:

set option public.allow_snapshot_isolation = 'On'

?

There are several research papers related to SI that I am currently looking at: 1. Postgres-R(SI): Combining Replica Control with Concurrency Control based on Snapshot Isolation - Wu, Kemme 2. A Theoretical Study of "Snapshot Isolation" - Norman, Ostby 3. Performance Implications of Using Diverse Redundancy for Database Replication - Stankovic

Many many more, Have a look in Google Scholar and you'll see what I mean.

Most research is centred on either PostgreSQL or Oracle, a minority on Firebird, which is supposedly the first to offer SI. I intend to include SQL Anywhere as part of a redundancy setup, along with Oracle and PostgreSQL. PostgreSQL and Oracle have been used in tandem in redundancy configurations for several years and has proven adequate. I intend to prove that including SQL Anywhere as a viable option.

[VolkerBarth]

To answer your first question: That option "allow_snapshot_isolation" can be set permanently or can be toggled on/off, see here. It must be enabled before a connection can use any of the snapshot isolation levels.

Note that enabling it enforces the storage of row versions for modified rows to allow snapshots (whether they are used or not), so it has its price.

Here's a longer explanation by Glenn of the workings of row versioning and its effects...