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]