I've got a multithreaded application that does the following
(simplistically speaking):

thread 1: insert a record ("RECORD1"), commit, signal wake for thread 2
thread 2: select * from table

the DB connection is ReadCommitted and Lockwait is true. I'm using
IBPP for firebird access.

the problem is, thread 2 cannot access to RECORD1 yet as the select
statement reveals it is not available. I have to refetch a few times
to get it stably read in RECORD1. How do we make sure Firebird returns
the committed result set instead of returning the one prior to the
commit?

I know there's an execution queue in firebird where operations do not
get executed in-order. But if that's the case, then why would a single
thread one ALWAYS return the committed result set?

I'm guessing it's the API optimization (or over-optimization in this
case) that causes this failure. Perhaps it's a scenario that has
escaped the developers' mind?

Thanks,
Zach