Hello

I have written a multithreaded socket server (Synapse), accessing a
FB2.0 database. I am using UIB components (v2.0), and create a new DB
and query connection within each thread. The connection is run within
the context of StartTransaction..Commit, and calls a stored procedure,
which updates a control table, inserts records, and selects and
returns a number of records in the same procedure.

If I run 1 client connecting to the socket server, everything works
OK, I can see the updates in the database, and the expected results
are returned. However when I load test with say 10 connected clients,
each running 50,000 queries sequentially I have strange results. It
appears that not all calls to the stored procedure are executed, and I
find that approximately 40% of the calls to the stored procedure are
logged, even though each call should be logged. The transaction is set
to Wait by Delphi.

Questions :

- Is Delphi 7 with FB2.0 OK?
- Is the mixing of Updates, inserts and multiple selects in a stored
procedure acceptable ? Or should I break these up into 3 separate
stored procedures ?
- Should I be implementing transactions within the stored procedure as
well e.g. a transaction for updates, a transaction for the inserts,
and a transaction for the selects ? Or does the Delphi based
transaction handle all of this for me ?


Any other thoughts on this would be gratefully accepted.

Thanks
Rob