driver ODBC-PostreSQL, cursor and transaction isolation level
2009-04-02 15:53:51 GMT
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
RSS Feed