--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> Greg At ACD wrote:
> >
> > Now, I have a situation where I have one connection service
> > an "insert into <table>..." request, and returns a PK for the

new

> > record ...
> >
> > 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.
>
> If that's happening, you're not doing your commits correctly. If

the

> first insert is committed, and the subsequent select is done from

a

> transaction that starts after the commit, the select will see the
> results. Absolutely. My guess is that you're using a read

committed

> transaction (which I wouldn't) and that each connection starts a
> transaction as soon as it commits the previous transaction. Or

perhaps

> that connections just hang around loose with open transactions.
>
> Regards,
>
>
> Ann

OK, that's what I had hoped. Something that I can fix :)

I am (or was) pretty sure that I am commiting correctly in all
situations, but apparently there must be an open transaction hanging
around (likely a missed commit). I do know that I issue a commit
after the Insert stored proc is called, so that's where I was
getting confused.

Probably what is happening is exactly what you say... the 2nd thread
(i.e. the one doing the SELECT) does a commit from a previous
request, and a new transaction starts immediately, but the
connection sits idle until after the insert is completed by the 1st
thread. Since the implicit transaction in the 2nd thread started
before the insert in the 1st thread, it doesn't "see" the inserted
data until it does it's own commit.

K thx for the help!

Greg