From: | "olivier dorie" <olivier(dot)dorie(at)ign(dot)fr> |
---|---|
To: | <pgsql-odbc(at)postgresql(dot)org> |
Subject: | driver ODBC-PostreSQL, cursor and transaction isolation level |
Date: | 2009-04-02 15:53:51 |
Message-ID: | 056F3845699645D39CF3C392BD754BEA@ign.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
Hello,
I have some problems to have a transaction isolation level serializable with
postgresql-ODBC driver for windows by using the ODBC cursor
(UseDeclareFetch=1).
My database is on PostgreSQL 8.3.7/ Linux.
I use the postgresql-ODBC version 8.03.04 for windows.
I make this test with the default_transaction_isolation = 'read committed'
and after with the default_transaction_isolation = 'serializable' on the
server. The results are the same.
I do the followings operations in a c++ programm:
* I open 2 connections on my database with the ODBC drivers:
"connexion1" and "connexion2"
* CDatabase * db1 = new CDatabase ;
* CDatabase * db2 = new CDatabase
* db1->OpenEx (chaine_connection_mydatabase, CDatabase::noOdbcDialog);
* db2->OpenEx (chaine_connection_mydatabase, CDatabase::noOdbcDialog)
* I ask for the number of tuples of table "table1" --> n tuples:
* CRecordset crs (db1)
* crs.Open (CRecordset::snapshot, "SELECT count (*) FROM table1;")
* I make the "connexion1" serializable :
* db1->ExecuteSQL ("BEGIN TRANSACTION;")
* db1->ExecuteSQL ("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
* In the connexion1, I add a tuple in the table "table1" :
* db1->ExecuteSQL ("INSERT INTO table1 ..;")
* In the connexion1, I ask for the number of tuples of table "table1"
--> n+1 tuples
* CRecordset crs (db1)
* crs.Open (CRecordset::snapshot, "SELECT count (*) FROM table1;")
* In the connexion2, I ask for the number of tuples of table "table1"
--> n+1 tuples;
* CRecordset crs (db1)
* crs.Open (CRecordset::snapshot, "SELECT count (*) FROM table2;")
I don't understand why the number is not n tuples in the "connexion2"
because the connexion1 is serializable ?
Do I use the good parameters for ODBC? Are there some restrictions for the
use of the cursors with ODBC-PostgreSQL and the transaction isolation level
?
My parameters are the followings:
"DRIVER={PostgreSQL
ANSI};DATABASE=<mabase>;SERVER=<monserver>;PORT=5438;UID=<user>;PWD=<passwor
d>;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;
RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=10000;Socket=4096;Unk
nownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=5400000;Debug=0;CommLog=0;
Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarch
ar=1;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFC
onversion=0;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;Bytea
AsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier=0;XaOpt=1"
If I use the option "UseDeclareFetch=0", the transaction isolation level
serializable is respected.
Thank's all
Olivier
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2009-04-03 06:41:25 | Re: driver ODBC-PostreSQL, cursor and transaction isolation level |
Previous Message | Raul Orduna | 2009-03-30 09:09:50 | Re: Problem with Oracle, ODBC and Postgresql |