Thanks Ann,

You have given me a lot of info to work with, but I guess I am going to have to get my head down and get stuck into the code to get to the bottom of this particular issue.

Thanks again.
Sean

--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> Hi Sean,
>
>
> The exact problem is one where not all the records are retrieved for a
> > particular cursor, a scroll cursor. Every now and again it retrieves 49
> > rows when it should retrieve 50. It happens about twice a day when under
> > load, which is not a lot as 10,000s, of records are read and updated daily,
> > but unfortunately it has a ripple effect in the rest of the application
> > when this happens.
> >
>
> Hmm... That's interesting and odd, but scrolling cursors are after my day,
> so I'll let someone else answer that.
>
> >
> > Cursors on the same table are being used by other users at the same time,
> > but not on the exact same rows in the table. The logical explanation I came
> > up with is that a first user would lock their cursor "for update" putting
> > into an exclusive lock, and during this time when the second user tried to
> > retrieve rows in its cursor, it is unable to read all the records as it was
> > locked by the first user.
>
>
> OK, what happens on a "SELECT ... FOR UPDATE" is approximately nothing.
> "SELECT ... FOR UPDATE WITH LOCK" causes Firebird to make a new version of
> the record tagged with the transaction identifier of the transaction that
> did the select. That version will block updates by other transactions.
> Readers would see the old version.
>
> However, this would only make sense if Firebird used page level locks
> > instead of row level locks as the as the data fetched is not the same. My
> > understanding is that it uses row level locks all the time. This was the
> > reason I asked about the capability of dirty reads, as then it would ignore
> > these locks.
> >
> > Firebird concurrency 101. Firebird's concurrency is managed by creating
> record versions that are tagged with the id of the transaction that created
> them. Firebird does not use traditional two-phase locking for concurrency
> control, either at the page or record level. When you insert a new row, it
> is tagged with your transaction id. Every running transaction keeps a bit
> map of the state of other transactions at the time it starts, so when it
> reads a record version, it knows whether the transaction that created that
> version was committed, active, or rolled back. If the transaction that
> created the record version was not committed, the reading transaction looks
> for an older version of the record. Readers don't block writers and
> writers don't block readers. They just use different versions of the
> record. Firebird manages write/write conflicts between concurrent
> transactions by disallowing updates and deletes if the most recent version
> of the record was not committed when the updating transaction started.
>
> Your problem is not in the locking strategy - there isn't one.
>
> One thing I wonder about is the isolation mode in the application.
> Firebird's default isolation mode produces consistent reads - execute the
> same query five times and you'll get the same answer, even though changes
> have been made to the records by other transactions. Your transaction is
> isolated from changes made by others. However, Firebird also supports
> other isolation modes, including one in which a transaction always reads
> the most recently committed version of a record, even if the record was
> changed by another concurrent transaction that committed. I wonder if
> there might be some unfortunate interaction between scrolling cursors and
> unstable reads.
>
> Good luck,
>
> Ann
>
>
> [Non-text portions of this message have been removed]
>