Hi,
Server: Postgresql 7.4.3 on Cygwin while playing around
pg-odbc driver: 7.03.02.09
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 ...
BEGIN
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 ?