|
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. |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- 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: 01 Aug '17, 17:21
question was seen: 1,367 times
last updated: 02 Aug '17, 06:51
I think I found something in the documentation that answers my question:
Is that the correct way to set a transaction to snapshot isolation ?
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.
Yeah, I realised late yesterday that
is in fact the command.
The other question I have is, does one need to set this command as a one-off:
?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.
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...