Subject Re: Firebird SS hangs on ics_dsql_allocate_statement.
Author Stan
Thanks for the answer.

Let me get this straight, you are saying that my
"statement preparation" is being blocked because
of transaction contention between clients?

This hang is happening before the clients actually
execute the statements. I was under the impression
that transaction conflicts only happen when the
statements are actually getting executed.

You mentioned an admin tool, no I dont have one open.
But my web-based UI could be doing selects while the
clients are inserting data.



--- In, Helen Borrie <helebor@...> wrote:
> At 10:10 AM 22/02/2007, you wrote:
> >Hi All,
> >
> >
> >I have 100+ clients connecting to Firebird using
> >the C++ fbclient.dll (v2,0, same version as server) api.
> >Each client prepares about 10 statements
> >and then uses these statements to batch insert 1000-3000
> >records into various tables.
> >
> >I noticed that the prepare-statement procedure would hang
> >for 30-90 seconds, at this time Firebird is idle and my
> >clients are also idle. Some clients are stuck
> >on calls to isc_dsql_allocate_statement, some on isc_dsql_prepare,
> >and some on isc_dsql_describe_bind
> >
> >After the initial 30-90 second "hang", the clients get unstuck
> >and the insert process proceeds very quickly.
> >
> >This issue starts happening as the number of clients that
> >connect at the same time goes above ~50.
> >
> >What could be causing the hang in firebird?
> >Is there some kind of shared resource involved in
> >preparing statements?
> Yes: the Transaction Inventory of the database. :-) Inserts to a
> particular table will conflict with any existing read-committed
> transactions having an open cursor on that table that is filtered by
> a WHERE clause. This is the one place in Firebird where readers can
> block writers. Are you by any chance watching the tables using some
> admin tool?
> >It feels like some kind of anti-DOS back-off algorithm?
> It feels like transaction concurrency control as
> well. :-) Specifically, transactions with the WAIT mode for lock
> resolution. This may be what you want, in order to serialise the
> inserts, in which case you will see those delays and also allow the
> possibility of deadlocks and livelocks. You might prefer to set NO
> WAIT and use a retry loop mechanism in the least that way
> you can keep the users informed about what is taking so long and/or
> give them the option of trying again later.
> ./heLen