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

Re: driver ODBC-PostreSQL, cursor and transaction isolation level

From: "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "olivier dorie *EXTERN*" <olivier(dot)dorie(at)ign(dot)fr>,<pgsql-odbc(at)postgresql(dot)org>
Subject: Re: driver ODBC-PostreSQL, cursor and transaction isolation level
Date: 2009-04-03 06:41:25
Message-ID: D960CB61B694CF459DCFB4B0128514C202FF6537@exadv11.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-odbc
olivier dorie wrote:
> 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;")

Wait a minute:
I would say that in connection 1, you ask for the number of tuples in table2
and not what you claim. Is this a typo?

> I don't understand why the number is not n tuples in the 
> "connexion2" because the connexion1 is serializable ? 

The isolation level of a transaction does not influence other transactions.
It only changes the world-view of the transaction itself.

It is the fact that there is a transaction in progress (irrespective of
its isolation level) that will keep others from seeing the inserted record.

But yes, you are right, a second connection should only see committed records.

Yours,
Laurenz Albe

In response to

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2009-04-04 07:01:08
Subject: Re: driver ODBC-PostreSQL, cursor and transaction isolation level
Previous:From: olivier dorieDate: 2009-04-02 15:53:51
Subject: driver ODBC-PostreSQL, cursor and transaction isolation level

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