At 08:51 PM 5/02/2007, you wrote:

>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 ?

In a second posting you added:

>Forgot to mention I have also tested the stored procedure via calls to
>a php script using Apache Webserver, and the same issues arise. If I
>run a single client connecting to the webserver, results are as
>expected. When running multiple clients connecting to the
>webserver/php script calls to the database appear to be lost.

When you have multiple users operating concurrently on the same data,
you really can't get away with not understanding what a transaction
is and how concurrency is managed. It's a big topic, too big to
explain in one list message. May I recommend that you go to
www.ibobjects.com and click through to the Tech Info page
there. It's specific to IBO in many ways but I think that, as a
Delphi user, you will get some enlightenment from reading the various
papers on transactions.

The most basic thing to know is that, as soon as you have multiple
users hitting the same record[s] you have potential for update
conflicts. You have to handle those....but you are preventing that
by simply stacking up transactions that are waiting for conflicting
transactions to finish, instead of letting them throw the exception
immediately.

After all, there's no point in waiting on the off-chance that the
conflicting transaction will roll back (which is the only hope your
transaction has of getting past the conflict). If the other
transaction succeeds in committing its work, your transaction won't
be able to commit if its changes still conflict with the work that
has now been committed.

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.

In answer to your question about performing data-changing operations
in a selectable SP, it's a no-no, even if it seemed pretty cool when
you were the only user in the database. It's one of those things you
can do but shouldn't, since you will return a dataset that contains
uncommitted changes. Write an executable SP to do the DML and handle
the exceptions; write a selectable SP for viewing the data after
you've committed the DML; and don't use WAIT transactions unless you
seriously mean to. You can keep your SP prepared so you can refresh
its output after your executable proc has run; but don't write
selectable SP's that return more than a couple of hundred records.

To answer the other part of that question, any changes done inside a
SP remain uncommitted until the whole transaction is either committed
or rolled back. In case of a rollback, all of the changes in the SP
are rolled back too. You cannot commit and start transactions
inside other transactions - and that obviously means that starting
and committing transactions inside stored procedures is impossible
(you won't even be able to compile such a procedure!).

I know it's an incomplete answer...but research it, experiment with
it, and ask some more questions targetted at particular things you're
bemused by.

./heLen