Thread: PSQLODBC and TRANSACTIONS
Hi, I'm trying transactions using M$-access and PSQLODBC without success, Seems that backend locks itself by opening two connections to work on the same table. I have a table with 3 rows: codice nome cap --------------------------- 1 pippo 0 2 pluto 0 3 topolino 0 --------------------------- I tryed to update table as: UPDATE operatori SET cap = 1; This works, but the next... UPDATE operatori SET cap = 2; At this point the backend was locked and I had to break connection... (See attached log file) Thanks for any help. -Jose'-
Attachment
Jose' Soares wrote: > Hi, > > I'm trying transactions using M$-access and PSQLODBC without success, > Seems that backend locks itself > by opening two connections to work on the same table. > > I have a table with 3 rows: > > codice nome cap > --------------------------- > 1 pippo 0 > 2 pluto 0 > 3 topolino 0 > --------------------------- > The logfile shows that Access is using Manual Commit. The last two lines show a separate connection being made and then a select statement on "operatori" table. But it is still locked from the other connection. (see below) I don't think there is anything the driver can do about this. You need Vladim's new locking code! I think that is going to be in the next version of Postgres. Byron <Highlights from the logfile> BEGIN UPDATE "operatori" SET "cap"='1' WHERE "codice" = '1 ' SELECT "codice","cap" FROM "operatori" WHERE "codice" = '3 ' UPDATE "operatori" SET "cap"='1' WHERE "codice" = '3 ' SELECT "codice","cap" FROM "operatori" WHERE "codice" = '2 ' UPDATE "operatori" SET "cap"='1' WHERE "codice" = '2 ' <Another connection is created here> SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=hygea;SERVER=verde;PORT=5432;UID=marco;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=set+ksqo+to+%27on%27%3b' <This one hangs it> SELECT "operatori"."codice" FROM "operatori"
Hello Byron, martedì, 12 gennaio 99, you wrote: BN> Jose' Soares wrote: >> Hi, >> >> I'm trying transactions using M$-access and PSQLODBC without success, >> Seems that backend locks itself >> by opening two connections to work on the same table. >> >> I have a table with 3 rows: >> >> codice nome cap >> --------------------------- >> 1 pippo 0 >> 2 pluto 0 >> 3 topolino 0 >> --------------------------- >> BN> The logfile shows that Access is using Manual Commit. The last two lines show a separate connection BN> being made and then a select statement on "operatori" table. But it is still locked from the other BN> connection. (see below) BN> I don't think there is anything the driver can do about this. You need Vladim's new locking code! I BN> think that is going to be in the next version of Postgres. BN> Byron BN> <Highlights from the logfile> BN> BEGIN BN> UPDATE "operatori" SET "cap"='1' WHERE "codice" = '1 ' BN> SELECT "codice","cap" FROM "operatori" WHERE "codice" = '3 ' BN> UPDATE "operatori" SET "cap"='1' WHERE "codice" = '3 ' BN> SELECT "codice","cap" FROM "operatori" WHERE "codice" = '2 ' BN> UPDATE "operatori" SET "cap"='1' WHERE "codice" = '2 ' BN> <Another connection is created here> BN> SQLDriverConnect(out)='DSN=PostgreSQL;DATABASE=hygea;SERVER=verde;PORT=5432;UID=marco;PWD=;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=set+ksqo+to+%27on%27%3b' BN> <This one hangs it> BN> SELECT "operatori"."codice" FROM "operatori" Thank you for reply Byron. I hope that v6.5 may solve my problem. -Jose'-