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 ?
