Skip site navigation (1) Skip section navigation (2)

driver ODBC-PostreSQL, cursor and transaction isolation level

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 (view raw or flat)
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

 

 

 

Responses

pgsql-odbc by date

Next:From: Albe LaurenzDate: 2009-04-03 06:41:25
Subject: Re: driver ODBC-PostreSQL, cursor and transaction isolation level
Previous:From: Raul OrdunaDate: 2009-03-30 09:09:50
Subject: Re: Problem with Oracle, ODBC and Postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group