Hi all,

DB: Firebird 1.5.2 SuperServer, local database
O/S: Windows XP Professional (SP2)

I have an application that basically composed of 2 pieces; a client
that makes requests of the database, and a back end that pools
multiple connections to the same database (local, in this case). The
client is multi-threaded, and can make requests to the back end,
which then uses an available connection in the pool to respond to
the request.

Now, I have a situation where I have one connection service
an "insert into <table>..." request, and returns a PK for the new
record (standard stuff... a stored proc that uses a generator to
grab a new PK, then insert based on this PK, and then return the new
PK value to the caller).

After a short amount of time, the client then uses this PK and makes
a request to the back end (select ... from <tablewhere id = <new pk
value>). It so happens that a different connection is used from the
connection pool, and the call comes back with an empty result (i.e.
the second connection doesn't see the data that was added in the
first connection). If I wait a second or so, then the 2nd connection
will return the correct data.

This causes a few sync problems for my application. I am wondering
if there is any way to assure that an insert/update made in one
connection (yes, commits are done correctly), will be available in
the second connection? Is this simply a lag issue that I'm going to
have
to live with?

One obvious solution is to have only one connection to channel
everything through, but our client app has several threads that each
make database requests, and so to mitigate situations where longer
queries would tie up the other threads, we've implemented a
connection pool to service the requests from the client to available
connections. Unfortunately, we get situations like above where an
update/insert is done in one request, which is then followed by a
query for the same data in another request that is handed by a
different connection. So, the single connection model really doesn't
work well for us.

Any help with this would be terrific!

thx!

Greg