Обсуждение: driver ODBC-PostreSQL, cursor and transaction isolation level
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=<password>;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=10000;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=5400000;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=1;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=0;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=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
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
olivier dorie wrote: > 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” > o /CDatabase * db1 = new CDatabase ;/// > o /CDatabase * db2 = new CDatabase/// > o /db1->OpenEx (chaine_connection_mydatabase, > CDatabase::noOdbcDialog);/// > o /db2->OpenEx (chaine_connection_mydatabase, > CDatabase::noOdbcDialog)/// > * I ask for the number of tuples of table “table1” à n tuples: > o /CRecordset crs (//db1)/ > o /crs.Open (CRecordset::snapshot, "SELECT count (*) FROM > table1;")/// > * I make the “connexion1” serializable : > o /db1->ExecuteSQL (“BEGIN TRANSACTION;”) / > o /db1->ExecuteSQL (“SET TRANSACTION ISOLATION LEVEL > SERIALIZABLE;”)/ ODBC doesn't recommend to call dbms specific commands to control transaction. Please use BeginTrans/CommitTrans/Rollback instead. Anyway I improved the handling of transaction under useDeclareFetch mode a little. Please try the drivers on testing for 8.4.0100 at http://www.geocities.jp/inocchichichi/psqlodbc/index.html . regards, Hiroshi Inoue
Hiroshi, Thank you for your answer. I try the 2 solutions (BeginTrans and the driver 8.4.0100) and the two of them work: in the small test, I describe in the last mail, the transaction is serializable. I have a last question: with BeginTrans or with the driver 8.4.0100, the transaction isolation level is every time serializable ? Is it possible to have a read committed level? Regards Olivier -----Message d'origine----- De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Hiroshi Inoue Envoyé : samedi 4 avril 2009 09:01 À : olivier dorie Cc : pgsql-odbc@postgresql.org Objet : Re: [ODBC] driver ODBC-PostreSQL, cursor and transaction isolation level olivier dorie wrote: > 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 > o /CDatabase * db1 = new CDatabase ;/// > o /CDatabase * db2 = new CDatabase/// > o /db1->OpenEx (chaine_connection_mydatabase, > CDatabase::noOdbcDialog);/// > o /db2->OpenEx (chaine_connection_mydatabase, > CDatabase::noOdbcDialog)/// > * I ask for the number of tuples of table table1 à n tuples: > o /CRecordset crs (//db1)/ > o /crs.Open (CRecordset::snapshot, "SELECT count (*) FROM > table1;")/// > * I make the connexion1 serializable : > o /db1->ExecuteSQL (BEGIN TRANSACTION;) / > o /db1->ExecuteSQL (SET TRANSACTION ISOLATION LEVEL > SERIALIZABLE;)/ ODBC doesn't recommend to call dbms specific commands to control transaction. Please use BeginTrans/CommitTrans/Rollback instead. Anyway I improved the handling of transaction under useDeclareFetch mode a little. Please try the drivers on testing for 8.4.0100 at http://www.geocities.jp/inocchichichi/psqlodbc/index.html . regards, Hiroshi Inoue -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc
Hi Olivier, olivier dorie wrote: > Hiroshi, > > Thank you for your answer. > > I try the 2 solutions (BeginTrans and the driver 8.4.0100) and the two of > them work: in the small test, I describe in the last mail, the transaction > is serializable. > > I have a last question: with BeginTrans or with the driver 8.4.0100, the > transaction isolation level is every time serializable ? Why do you think so? Your example doesn't seem to show us the difference. > Is it possible to > have a read committed level? It's determined by the server setting. The default is read committed. regards, Hiroshi Inoue
Hi Hiroshi,
Sorry for my last message, I made some mistakes in my tests.
Since, I have made others tests with begintrans with (the driver 8.4.0100 and the driver 8.3.4):
· No dirty read
· Read committed
· Serializable
I use the server settings or ExecuteSQL(“SET ISOLATION LEVEL ….”)
All tests give good results.
Regards
Olivier
Hi Olivier,
olivier dorie wrote:
> Hiroshi,
>
> Thank you for your answer.
>
> I try the 2 solutions (BeginTrans and the driver 8.4.0100) and the two
> of them work: in the small test, I describe in the last mail, the
> transaction is serializable.
>
> I have a last question: with BeginTrans or with the driver 8.4.0100,
> the transaction isolation level is every time serializable ?
Why do you think so?
Your example doesn't seem to show us the difference.
> Is it possible to
> have a read committed level?
It's determined by the server setting. The default is read committed.
regards,
Hiroshi Inoue
On Thu, Apr 9, 2009 at 3:05 AM, olivier dorie <olivier.dorie@ign.fr> wrote: > Since, I have made others tests with begintrans with (the driver 8.4.0100 > and the driver 8.3.4): > > · No dirty read > · Read committed > · Serializable > > I use the server settings or ExecuteSQL(“SET ISOLATION LEVEL ….”) > All tests give good results. Only Read Committed and Serializable are implemented in PostgreSQL all other isolation levels default to one of these two: http://www.postgresql.org/docs/8.3/interactive/sql-set-transaction.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug