Hi all.
I'm using SA12.0.1 on Linux and I'm comparing it with PostgreSQL behaviour when locks occurs.
I need SELECT statements DON'T waits (eventually) locked tuples involved by updates.
In PostgreSQL, in a similar scenario, the select statement returns values NOT upgraded (.. old values) so it NOT depends from other transaction completion ... (very scalable ...)
In SA12 I can:
1. wait the commit of update transactions (default behaviour)
2. use WITH(NOLOCK) hint .... but not-committed values are returned (... not "old" values)
3. use WITH(READPAST) hint .... but locked tuples are skipped (!!!!) .... (not usable)
Any suggestions ?
Is there a global database option that allow to obtain the same Psql behaviour ?
Thanks. N.C.

asked 28 Jul, 08:08

NCister's gravatar image

NCister
19691118
accept rate: 0%

edited 28 Jul, 08:12


You are asking a question about "isolation level" which is a complex and confusing topic, especially when cursors are involved.

PostgreSQL 9.1.24 isolation levels are described here.

SQL Anywhere 12.0.1 Isolation levels are discussed here.

The default for SQL Anywhere databases is isolation level 0 (read uncommitted), unless the connection is using Open Client, jConnect or TDS in which case the default sp_tsql_environment() procedure changes the isolation level to 1 (read committed) which may be what you are experiencing.

The PostgreSQL documentation talks about "snapshot" when describing "read committed". In SQL Anywhere, there are three flavors of "snapshot isolation" which are described in detail here.

FWIW isolation level 0 (read uncommitted) offers good performance. In many applications, 99.999% of updates are eventually committed which means that a SELECT running at isolation level 0 is going to see the most up-to-date data 99.999% of the time, whereas SELECT statements running with snapshot isolation are frequently going to see out-of-date values.

However, the choice of isolation level is a very personal one. For example, some folks believe very strongly There Can Be Only Snapshot! :)

permanent link

answered 28 Jul, 09:38

Breck%20Carter's gravatar image

Breck Carter
27.0k447618889
accept rate: 21%

Just to add: You can use table hints to specify isolation levels for particular queries or even particular tables within a single query with joins, but usually you set the isolation level for the whole connection (as Breck has pointed out, and possibly with the help of the according connection parameter) and would only (and rarely, methinks!) use those table hints when you require different behaviour...

(28 Jul, 11:11) Volker Barth
Comment Text Removed

Thank you Breck !
You have (as always ...) perfectly focused the problem ;-)
I'm trying to clone PostgreSQL default behaviour using:

SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
SET OPTION isolation_level = 'readonly-statement-snapshot';

It seems works ...

permanent link

answered 28 Jul, 11:22

NCister's gravatar image

NCister
19691118
accept rate: 0%

edited 28 Jul, 11:24

1

Feel free to accept Breck's answer then:)

(28 Jul, 11:46) Volker Barth

Now I'm testing with JDBC (sajdbc4.jar) used by application and I noticed that driver IGNORES the default database options (isolation_level = 'readonly-statement-snapshot', in my case): it always forces isolation level = "0" (!!!)
So all work fine using Interactive SQL but, at application level, an explicit transaction level setting is required (!!).
This is a problem .... I cannot introduce changes and re-deploy application ...

(31 Jul, 06:22) NCister
Replies hidden
1

What about

  • using the IsolationLevel ODBC connection parameter, if that has an effect on JDBC connections, too (I don't know), or

  • using the InitString connection parameter (from the same doc page) to issue in initial "SET TEMPORARY OPTION isolation_level = 'readonly-statement-snapshot'" statement, or

  • using a LOGIN_PROCEDURE to set the according option when a particular connection (say, identified by API, your AppInfo or whatever) does start a connection?

(31 Jul, 08:33) Volker Barth

Check the value of the login_procedure database option.

Like alcohol, that procedure is the cause of and solution to many problems :)... check out what is being called, and what that procedure contains.

The default is the sp_login_procedure which is responsible for screwing up jConnect and other kinds of connections, but that isn't you're problem because sajdbc4.jar uses the CMDSEQ protocol rather than TDS...

CREATE PROCEDURE dbo.sp_login_environment( )
BEGIN
  IF connection_property( 'CommProtocol' ) = 'TDS' THEN
    CALL dbo.sp_tsql_environment( )
  END IF
END;

Anyway, it comes as a surprise to me that your connections are defaulting to something other than the isolation_level database option...

...but wait

...exactly how did you set it?

SET OPTION PUBLIC.isolation_level = 'whatever';

Without the PUBLIC, you are setting it for the current user id (as opposed to SET TEMPORARY OPTION which sets it for the current connection).

You might also want do check if some Evil Doer set the user-id-level value for every-single-user-id... that would be diabolical, and login_procedure may be your magic wand :)

(31 Jul, 08:39) Breck Carter

You may want to check SYSOPTION.

GRANT CONNECT TO x IDENTIFIED BY sql;
SET OPTION x.ISOLATION_LEVEL = '0';
GRANT CONNECT TO Y IDENTIFIED BY sql;
SET OPTION Y.ISOLATION_LEVEL = '1';
GRANT CONNECT TO Z IDENTIFIED BY sql;
SET OPTION Z.ISOLATION_LEVEL = '2';

SELECT user_name ( user_id ),
       "option",
       setting
  FROM SYSOPTION
 WHERE "option" = 'ISOLATION_LEVEL'
 ORDER BY user_name ( user_id );

user_name(ISYSOPTION.user_id),option,setting
'PUBLIC','isolation_level',0
'x','isolation_level',0
'Y','isolation_level',1
'Z','isolation_level',2
(31 Jul, 08:52) Breck Carter

The Help doesn't stress this point, but there are database-level and connection-level options, and the isolation_level is one of the latter.

For many connection-level options, there are three possibilities: global default (PUBLIC group), local default (individual user id) and temporary setting (connection).

For isolation level, there is a fourth possibility: table-level within a query.

Personally, I try to follow The Two-Step Program For Concurrency Affliction :)...

  1. Use only isolation level zero.

  2. Use only insensitive cursors.

(31 Jul, 09:06) Breck Carter

Thank you Volker.

To verify real SA12 behaviour I'm using the following query on the current connection:

SELECT Value FROM sa_conn_properties( ) where propnum=463 and propname='isolation_level' and number=CONNECTION_PROPERTY( 'number')


So, using a JDBC connection (from simple console program):

  • sp_login_environment (that I've modified to set isolation level) DON'T works (jdbc override it ...)
  • "InitString" (tipically included in DSN definition) WORKS (!) also in connection string
  • "IsolationLevel=1" attribute in connection string WORKS, too

I not used this two attributes becouse thought it was a DSN approach ...

(02 Aug, 06:01) NCister

Thank you Breck.
... this could be an interesting and alternative approach ;-)

(02 Aug, 06:16) NCister

I not used this two

To understand: Do you not use them now or have not thought about them before? If InitString and/or IsolationLevel do work for you in the app's connection string, I would not hesitate to use them. It should not matter IMHO that the are documented within the context of DSN entries (which are both used by ODBC and other APIs, so using them does not mean you are using ODBC!).

BTW: You can simply query "SELECT connection_property('isolation_level')".

(02 Aug, 06:36) Volker Barth
1

I have not thought about them before ;-)

(02 Aug, 07:21) NCister
showing 2 of 10 show all flat view
Your answer
toggle preview

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

Question tags:

×52
×19

question asked: 28 Jul, 08:08

question was seen: 238 times

last updated: 02 Aug, 07:21