--- In firebird-support@yahoogroups.com, Andi Voss <andi@...> wrote:

>
> thanks for the replay.
>
> > Have you checked the performance monitor (Administration Tools ->
> > Performance). I would be looking at read and write queues for the
> > disk, as slow queries tend to be waiting for CPU time or disk access.
>
> This was one thing I checked at first.
> I monitored the read and write processes by filemon and there was no
> abnormality during the problems in the ERP.

Filemon; presumably the (former) Sysinternals tool is useful, but it
is not the same thing as the performance monitor. It tells you what
processes are trying to read or write what files (and any errors the
process encounters) and it really is quite shocking what an "idle" box
is actually doing behind the scenes.

> Nothing hangs or took too much time or something similar.

This is like watching the checkout person in a supermarket and saying
that they seem to be working efficiently and no one customer currently
being served appears to be waiting too long for the checkout operator
to scan an individual item. Unfortunately you do not see the 10 other
people behind this person waiting in a long queue to have their items
scanned.

>
> >
> > If that does turn out OK, then is there a possibility that the queries
> > are running under a WAIT transaction, and so they are waiting to see
> > if the conflicting transaction commits or rolls-back before returning.
>
> OK this sounds logical, but how can I detect these WAIT transactions?

It is a property of your transaction. In any multi-user system you are
going to need to contend with the possibility that someone else has
made a change that is incompatible with the change you want to make
(lost update problem etc).

Given that you have an isolated view of the database, there are
(broadly) two ways of handling this. You can either tell Firebird that
under such situations, you want an exception immediately. Your
application would need to handle such an exception (lock conflict on
no wait). Alternatively, you can tell Firebird that under such
situations, you want it to hang around and see whether the problem
will resolve itself when the transaction running the incompatible
change completes.

The advantage of NOWAIT is that you don't get held up by actions in
other transactions. The disadvantage is that it is a pessimistic
approach, and perhaps if it waited for long enough, it could succeed.

The advantage of WAIT is that in many cases, optimism pays off and
your action can proceed. The disadvantages are that it may not pay off
and then you have waited around for no benefit, and that you become
vulnerable to design mistakes in other transactions.

> And when this is the problem, why it is this problem?

It is not a "problem". It is a requested behaviour.

>
> >
> > Your database stats look OK from a garbage perspective (and you would
> > be seeing a maxed out CPU core if it wasn't).
>
> The health monitor from SBS never showed something critical.

I don't know that it would be "critical".

Adam