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

concurrency with adodb & ms-access

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: concurrency with adodb & ms-access
Date: 2004-09-28 16:03:59
Message-ID: 41598B6F.8050804@gmx.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I neec help on record-locks and adodb-timeouts.


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


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 Recordset objects:
Naivly I began like this ...
BEGIN
SELECT * FROM sometable WHERE id = 123 FOR UPDATE
Fill the form's fields and after a user finishes with this form I write 
the data back and COMMIT (on close of the form).
Sounds easy enough.

I expected to see an error in any concurring form that wants to fetch
the same data telling something that the requested record is locked an 
can't be edited right now.

Now I tried 2 open Access-MDBs (like 2 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-novice by date

Next:From: Josh BerkusDate: 2004-09-28 16:40:59
Subject: Re: pg 8.0b2, psql problem
Previous:From: Taber, MarkDate: 2004-09-28 15:18:37
Subject: pg 8.0b2, psql problem

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