Subject Re: [firebird-support] Firebird SS hangs on ics_dsql_allocate_statement.
Author Helen Borrie
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.