At 01:21 AM 6/02/2007, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
> > The more transactions you have sitting around uncommitted,
> > waiting....waiting..., the less likely it becomes that much progress
> > will be made. You don't say how you are logging but, if it is to a
> > table in the database, then that's a source of bottlenecks and
> > waiting, also; and log records won't get committed either.
> >
>Hello Helen
>
>Thanks for the detailed reply, throws a lot of light on the issue and
>I will refer to the IBO technical page as suggested. Couple of further
>questions for clarification if I may :
>
>- by throwing an exception with an update conflict I presume the SP
>will return to the calling program.

It will if you let it. If you want to have the SP change its mind
about an update or delete if it encounters a lock conflict, you can
do that too. The exception handling capabilities in Fb stored
procedures are great: you can literally design your SP as a series
of reversible nested operations...again, it takes a heck of a lot
more than a list answer to cover it. There's a whole section in my
book about server-side programming...covers this in detail.

> If so, I assume that I would need
>to check for the exception and call the SP again until successful ? Or
>perhaps loop in the SP until successful.

Yes to both, depending on what you want to do about particular types
of exceptions.

>- If I call an executable SP to do updates from a Delphi program,
>using transaactions and specifying read committed, do calls from other
>clients which may also want to update the same data not wait until my
>transaction commits, then the next client grabs the data, updates,
>commits, followed by the next...and so on ?

It *can* be done that way by configuring the transaction a certain
way - research the [NO]RECORD_VERSION transaction parameter. But
consider the implications seriously: do you actually want
transactions to be overwriting one another's work willy-nilly?

>If I understand you correctly once there is a lock held by a
>transaction others will fail even if the lock (and data changes) are
>committed by the original
>transaction.

That depends on the isolation level of the transactions
involved. Read committed is not really a safe isolation level for
read-write transactions, even though a lot of Delphi components make
it the default. Read committed read-only is ideal for the typical
Delphi-style graphical "look" (grids!) ...anyway, keep reading up
about the topic. I'm off to bed. :-)

./hb