Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread Loss of connection to server during transaction
Fri, Oct 9 2009 11:28 PMPermanent Link

Tony Pomfrett
Hi,

I have a question about the behaviour of a DBISAM client application in a client/server environment.

Below is sample code taken from the manual to show how to do transactions. The code runs in the client application.
My question is what happens if the client loses its connection to the server after calling StartTransaction but before calling Commit as indicated below.
Does this then mean that the database will remain locked forever? The code in the Except and Finally sections cannot run because the client can no longer communicate with the server.

Is it possible to run this code in a server side procedure instead to minimise the chances of  this type of disruption?


============================

var
  TablesList: TStrings;
begin
  TablesList:=TStringList.Create;
  try
     with MyDatabase do
        begin
        TablesList.Add('Customer');
        TablesList.Add('Orders');
        StartTransaction(TablesList);
        try
           { Perform some updates to the table(s) in the transaction }

>>>>>> Client loses connection to server here >>>>>>

           Commit;
        except
           Rollback;
           raise;
        end;
  finally
     TablesList.Free;
  end;
end;

====================================
Sat, Oct 10 2009 2:37 PMPermanent Link

"Raul"
What you need to ensure is that server will detect and clean up the dead
sessions quickly and thus will roll the transaction back.

StartTransaction simply places locks on tables but nothing is actually
written to tables til commit - so killing the dead session will result in
locks being released and data still being intact. No other client write
while locks are active (but they can read) so you just need to build some
retry logic into your code to "wait and try-again" if locking error occurrs.

Look at server settings ConnectTimeout and especially DeadSessionExpires.
Also enable RemotePing on your session and set it to a  reasonable value for
you - now you can set the DeadSessionExpires to something low (low multiple
of RemotePing value for example).

You can definitely write a serevr side procedure as well - however
server-side procedures are executed in the context of session thread of the
session and do pass results and progress back. I don't know what happens if
session drops while server-side procedure is executing. However. server-side
procedure should run much faster (local data access) and will likely
complete before the dead session is cleaned up by server.However maybe
somebody else can comment on server-side procedure behaviour with client
disconnect or you can just test it.

Raul



"Tony Pomfrett" <tonyp@aline.com.au> wrote in message
news:B2BB8243-B859-44CB-8931-CD4F77C0265E@news.elevatesoft.com...
> Hi,
>
> I have a question about the behaviour of a DBISAM client application in a
> client/server environment.
>
> Below is sample code taken from the manual to show how to do transactions.
> The code runs in the client application.
> My question is what happens if the client loses its connection to the
> server after calling StartTransaction but before calling Commit as
> indicated below.
> Does this then mean that the database will remain locked forever? The code
> in the Except and Finally sections cannot run because the client can no
> longer communicate with the server.
>
> Is it possible to run this code in a server side procedure instead to
> minimise the chances of  this type of disruption?
>
>
> ============================
>
> var
>   TablesList: TStrings;
> begin
>   TablesList:=TStringList.Create;
>   try
>      with MyDatabase do
>         begin
>         TablesList.Add('Customer');
>         TablesList.Add('Orders');
>         StartTransaction(TablesList);
>         try
>            { Perform some updates to the table(s) in the transaction }
>
>>>>>>> Client loses connection to server here >>>>>>
>
>            Commit;
>         except
>            Rollback;
>            raise;
>         end;
>   finally
>      TablesList.Free;
>   end;
> end;
>
> ====================================
>

Sat, Oct 10 2009 4:16 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<< My question is what happens if the client loses its connection to the
server after calling StartTransaction but before calling Commit as indicated
below.

Does this then mean that the database will remain locked forever? The code
in the Except and Finally sections cannot run because the client can no
longer communicate with the server. >>

No, the database will remain locked as long as the session is present on the
database server, which is, by default, defined by the Dead Session
Expiration setting on the database server.  The default setting is fairly
large, but you can bump this down to just 30 seconds after enabling pinging
in the client application's remote TDBISAMSession via the RemotePing
property:

http://www.elevatesoft.com/manual?action=mancompprop&id=dbisam4&product=d&version=7&comp=TDBISAMSession&prop=RemotePing

After you have set up pinging for the client application, you can then bump
down the Dead Session Expiration time so that sessions are removed within 30
seconds of being disconnected by the database server.

<< Is it possible to run this code in a server side procedure instead to
minimise the chances of  this type of disruption? >>

Sure, any client-side Delphi code can be run as a server-side procedure.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Oct 10 2009 4:21 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Raul,

<< You can definitely write a serevr side procedure as well - however
server-side procedures are executed in the context of session thread of the
session and do pass results and progress back. >>

Passing progress back to the client is optional, and if the results don't
make it back to the client, it doesn't really matter - the procedure will
have already executed (and committed) at that point.

<< I don't know what happens if session drops while server-side procedure is
executing. >>

If the server-side procedure tries to send progress back to the client, then
it will possibly get back an abort status due to the connection being
dropped.  However, it is strictly up to the server-side procedure whether to
actually abort or not in such a case.  It could choose to always ignore the
abort status of the progress update.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Oct 10 2009 5:24 PMPermanent Link

Tony Pomfrett
Thanks Tim and Raul....

Would not the ConnectTimeOut setting be critical? I already had RemotePing enabled and set at 60 seconds.
ConnectTimeOut is set at 300 seconds. The Dead Session Expiration however, was set at 12 hours.

Should I set ConnectTimeOut lower than 300 seconds? I have clients connecting over the Internet so it's entirely possible that a few pings will not succeed.
At 300 seconds I am allowing for 5 consecutive unsuccessful pings which seems reasonable.
Sat, Oct 10 2009 8:16 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<< Would not the ConnectTimeOut setting be critical? I already had
RemotePing enabled and set at 60 seconds.
ConnectTimeOut is set at 300 seconds. The Dead Session Expiration however,
was set at 12 hours.

Should I set ConnectTimeOut lower than 300 seconds? I have clients
connecting over the Internet so it's entirely possible that a few pings will
not succeed. At 300 seconds I am allowing for 5 consecutive unsuccessful
pings which seems reasonable. >>

You can certainly lower the connection timeout in the server configuration.
As long as it is higher than the ping interval, you should be fine, but your
mileage may vary once actually implemented, so you might want to keep it at
120 secs or so just to make sure.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Oct 10 2009 9:24 PMPermanent Link

Tony Pomfrett
Tim,

Almost all my users, both local network and Internet, are using notebooks. Notebooks by default are configured to go into sleep mode after a period of
user inactivity. This is a real problem because those sessions will be disconnected after 330 seconds and the user will experience an error when they try to
continue. When the Dead Session Expiration was set at 12 hours it presumably meant that notebook users could allow the notebooks to go into sleep mode.
Unfortunately, it probably also meant that stuck locks would hang around for 12 hours also.

I guess the solution is to have a short Dead Session Expiration but include code to gracefully reconnect without restarting the software. Something like a
message box asking the user to standby while their session is re-established or maybe telling them they are disconnected and giving the option of
restarting, reconnection or shutting down.

Although, in my case, reconnection would require reloading tables and re-initialising some variables so it's probably just as easy to restart the software. So
maybe a message box with 2 choices "restart" or "shutdown" would be appropriate?

Is reconnection on the fly, without restarting, desirable or even possible?
Sun, Oct 11 2009 9:57 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Tony,

<< I guess the solution is to have a short Dead Session Expiration but
include code to gracefully reconnect without starting the software.
Something like a message box asking the user to standby while their session
is re-established or maybe telling them they are disconnected and giving the
option of restarting, reconnection or shutting down.

Although, in my case, reconnection would require reloading tables and
re-initialising some variables so it's probably just as easy to restart the
software. So maybe a message box with 2 choices "restart" or "shutdown"
would be appropriate?

Is reconnection on the fly, without restarting, desirable or even possible?
>>

Yes, but you'll have to manage it since the session will have been removed
already on the database server.  The main thing is to make sure to
disconnect the remote session, which will close everything (databases,
tables, etc.), and then set everything back up again.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image