Hi Ann,

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.

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. 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.

Regards,
Sean

--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> On Fri, Mar 29, 2013 at 4:35 PM, sean_ohare@...
> <sean_ohare@...>wrote:
>
> >
> > Unfortunately, the problems we are experiencing are only visible when the
> > system is under load, so it is tricky to simulate/emulate the environment.
> > If we run a few users, we have no problems, so hence my questions about
> > multi user transactions and isolation levels.
> >
>
> Is the problem that transactions aren't seeing concurrent changes?
>
> You started by saying that Firebird loses transactions. That's very
> unlikely. Then you suggested that the application was rolling back
> transactions under load. That's possible, but would require active code to
> cause it to happen. Unlike some other databases, Firebird never rolls back
> a user transaction unless the connection is broken by a server crash,
> client crash, or network failure. What may be happening is that the
> application starts a read-only transaction with a stable view of the
> database as of that instant. Other transactions do things. The old stable
> transaction doesn't see the changes. New transactions do see the changes.
> How mystic, the database is in two places at once!
>
> So, can you explain the problem a little more. Does work go away
> completely or is it not visible to all running transactions?
>
> Good luck,
>
> Ann
>
>
> [Non-text portions of this message have been removed]
>