|
Hi all. |
|
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! :)
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 '17, 11:11)
Volker Barth
Comment Text Removed
|
|
Thank you Breck ! SET OPTION PUBLIC.allow_snapshot_isolation = 'On'; SET OPTION isolation_level = 'readonly-statement-snapshot'; It seems works ... 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" (!!!)
(31 Jul '17, 06:22)
NCister
Replies hidden
1
What about
(31 Jul '17, 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 '17, 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 '17, 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 :)...
(31 Jul '17, 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')
I not used this two attributes becouse thought it was a DSN approach ...
(02 Aug '17, 06:01)
NCister
Thank you Breck.
(02 Aug '17, 06:16)
NCister
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 '17, 06:36)
Volker Barth
|