Re: Testing remote procedure

gszalach_at_hotmail.com (szalas) wrote

> I have two databases: A and B. I call procedure PA on the database A.
> This procedure updates some rows and calls remote procedure PB on
> database B. After doing some DML instructions on the database B
> procedure PB sleeps for 60 seconds (for testing purpose). During it
> sleeps I disable network connection on computer B. I thought I'm going
> to receive some exception in procedure PA that remote database is not
> accesible. But i don't receive any exception.

Correct. Something needs to close the connection at IP socket level. Pulling the physical pipe does not raise such a notification. Remember that IP is routable, networks are segmented and all that.

> Procedure PA simply
> waits for procedure PB to finish its job and it keeps locks.
> How can I notice that remote host is not accessible and rollback
> transaction so that users could update table which is modified by
> procedure PA??

Look at DCD - Dead Connection Detection. Actually, that is something that your DBA, Unix sysadmin and Network admin should be looking at, and should have resolved.

It is _not_ the job of a programmer and a client app to check and trap problems like that. As soon as you do, where do you stop? Why not check local swap space? CPU performance? I/O performance? Bandwidth? Etc. Etc.

> Procedure PA is called from Delphi code and application hangs in the
> above situation (it is very wrong behaviour).

Yep. And all that the DBA needs to do is configure the listener to detect such dead connections and close them.

What is important on your part is data integrity and transactions. Are you using the right method to do this distributed thing of yours? Does it provide data integrity? Does it provide for rollback? Must the client app be part of this process? (i.e. it could simply initiate it). Etc.

--
Billy