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

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