on 2017 Jul 28 9:08 AM
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.
Request clarification before answering.
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! 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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 ...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Feel free to accept Breck's answer then:)
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 ...
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?
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 🙂
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
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 :)...
Use only isolation level zero.
Use only insensitive cursors.
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')
I not used this two attributes becouse thought it was a DSN approach ...
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')".
I have not thought about them before 😉
User | Count |
---|---|
75 | |
30 | |
9 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.