--- In firebird-support@yahoogroups.com, "Nick Upson" <nick.upson@...>
wrote:

>
> On 19/06/07, Svein Erling Tysvær
> <svein.erling.tysvaer@...> wrote:
> > --- In firebird-support@yahoogroups.com, "Nick Upson" wrote:
> > > > Nick Upson wrote:
>
> > >
> > > so I can't let A wait until B commits and then A will proceed?
> > >
> > > There isn't actually a conflict as they are updating different
> > > fields in a single-row table
> >
> > If you want to allow these fields to update independantly of each
> > other and still not allow simultaneous transactions to update the same
> > field concurrently, I would consider storing them in separate records
> > or even separate tables.
> >
> > Set
>
> I was now thinking along those lines as well. Originally there was
> only 1 process updating this table, then there was a new feature
> added, don't you love em
>

We have several applications where different processes, and even
different applications on different PC's may clash trying to update
information if the same record. Indeed this is to be expected in lot
of applications.

To resolve this we always use a construct like

function TLoaderData.ExecSQL3( sqlStr : STRING) : BOOLEAN;
var
retries : Word;
begin
if not ibWorkload.Connected then
ibWorkload.Connected := True;
Result := FALSE;
with IBQuery3 do
begin
retries := 0;
while retries < MAX_RETRIES do
begin
try
inc(retries);
if Transaction.Active then
Transaction.Commit;
if Active then
CLose;
SQL.Text := SQLStr;
Transaction.StartTransaction;
ExecSQL;
Transaction.Commit;
Result := TRUE;
retries := retries + MAX_RETRIES;
except
Transaction.Rollback;
end;
end;
end;

if Retries <= Max_Retries then
DebugMessage('ExecSQL3 :' + sqlstr); // ie ist failed multiple
times
end;

This will automatically handle singe transaction/record clashes. On
an application with duplicated processing where transaction clashes
were expected/common we changed the simple inc(retries) to a function
that added a slight delay, depending on a function of the PC ID and
had MAX_RETRIES=6 - that worked 24 hours a day for years -