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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Inoue | 2009-04-04 07:01:08 | Re: driver ODBC-PostreSQL, cursor and transaction isolation level |
Previous Message | olivier dorie | 2009-04-02 15:53:51 | driver ODBC-PostreSQL, cursor and transaction isolation level |