Skip site navigation (1) Skip section navigation (2)

concurrency with adodb & ms-access

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-odbc(at)postgresql(dot)org
Subject: concurrency with adodb & ms-access
Date: 2004-09-25 23:41:43
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc

Server:  Postgresql 7.4.3 on Cygwin while playing around
pg-odbc driver:
windows 2000
access 2000

I ask here since the problem seems not to exist on Microsofts SQL Server 
and don't want to get flamed for not using it.

I trying to get a deeper look into transactions with postgres. Up until 
now I dumped our data into plain access-mdb without concurrency control 
and hoped for the best.
Now I'm moving the backend to postgres and I want to do it "right".
I'll use unconnected forms that fetch their data content on open via 
ADODB Connection and Recordeset objects:
Naivly I began like this ...

SELECT * FROM sometable WHERE id = 123 FOR UPDATE
on close of the form I'll write the data back and COMMIT.
Sounds easy enough.
I expected to see an error in any concurring form that wants to fetch 
the same data.

Now I tried 2 open Access-MDBs (users) that do this. I sat a breakpoint 
after the "SELECT...FOR UPDATE" of MDB1 to look what happens in MDB2.
MDB2 stalls in it's own "SELECT...FOR UPDATE" line until MDB1 commits.

It might be OK, that user2 at the same time can't see the same record as 
user1 but user2 should be informed that there is a lock and that he 
can't change anything for now.
Access just stops processing and waits for the return of the SELECT.
I can't do anything until the warning would be obsolete anyway.

I tried setting:
    Set obj_conn_ado = New ADODB.Connection
    obj_conn_ado.CommandTimeout = 5
    obj_conn_ado.ConnectionTimeout = 5

So there should be a timeout raised after any 5 seconds of thumb 
twiddling as far as I understood the documentation I found with google.
Then I tried using a Command object

    Dim objCmd As ADODB.Command
    Set objCmd = New ADODB.Command
    objCmd.ActiveConnection = obj_conn_ado
    objCmd.CommandTimeout = 1
This should set the TimeOut to 1 second.

Still the second vba-procedure runs to the SELECT and drops dead.

What can I do to get an error-message instead of a deadlock ?

pgsql-odbc by date

Next:From: J JDate: 2004-09-27 04:47:30
Subject: problems with the ODBC driver
Previous:From: Melanie BergeronDate: 2004-09-24 14:57:04
Subject: odbc with PostgreSQL 8.0 & Delphi

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group