How does Firebird's result set cache behave under READ COMMITTED isolation when concurrent updates occur?

Question

In a Firebird 3.0 database, a table is subject to frequent UPDATE and INSERT statements from several concurrent sessions. A reporting session repeatedly executes the same SELECT statement under the READ COMMITTED isolation level, expecting to see the latest committed changes after each transaction completes. Occasionally the query returns rows that reflect an earlier state, indicating that the result set cache might be serving stale data despite committed transactions. The goal is to understand when the result set cache is considered valid and how to detect whether a cached result set is being used in this setting.

How can I determine if the result set cache is being used for a particular SELECT statement?
What conditions cause the cache to retain outdated data under READ COMMITTED isolation?
Is there a way to disable or flush the result set cache for a session without affecting other connections?

Tasadduq Burney · Answer

In Firebird 3.0, the 'stale data' you are observing is likely not caused by a server-side result set cache, but rather the behavior of the READ COMMITTED isolation level regarding statement consistency. Under this isolation, Firebird provides a snapshot of the data at the start of **each statement**, not the start of the transaction.

If your reporting session keeps a transaction open and re-executes the same SELECT, it should see committed changes that occurred before that specific statement began. However, if the application is caching the result set object on the client-side without re-executing the query, it will continue to see the data from the first execution.

Determining if Cache is Being Used
Firebird does not have a global 'result set cache' that persists across different queries for the same table. To determine if you are seeing stale data, check the following:

**Application-Level Caching:** Is your application code (e.g., Java, Python, .NET) storing the results of the first query in a list or object instead of calling the database again?
  **Transaction Longevity:** Are you using the same transaction for multiple SELECTs? In READ COMMITTED, each new SELECT sees the latest committed data as of that moment.
  **Cursor Position:** If you are using a cursor and have not fully iterated through the result set, the engine may be holding onto the state from when the cursor was opened.

Conditions for Outdated Data
Under READ COMMITTED isolation, 'stale' data occurs in these specific scenarios:

Non-Repeatable Reads: Because READ COMMITTED allows other transactions to commit changes between your statements, two identical SELECTs in one transaction may return different results. If your logic captures the first result that missed a commit, it may appear stale.
  Client-Side Snapshots:** If the database driver or framework implements its own internal caching (common in some ORMs), it may not even hit Firebird for updates.
  Snapshot Isolation:** If the isolation is actually set to READ COMMITTED_SNAPSHOT, the snapshot is taken at the start of the transaction. You will never see changes until the transaction restarts.

How to Flush or Disable Cache
There is no 'FLUSH RESULT_SET' command in Firebird for specific sessions. To ensure you have the absolute latest data:

Commit/Rollback: The most reliable way to 'flush' state is to end the current transaction and start a new one.
  Re-execute Query: Ensure the application is actually sending the SQL command again rather than iterating over a previously fetched collection.
  Isolation Check: Ensure you are not using READ COMMITTED_SNAPSHOT if you require seeing mid-transaction commits.

Diagnostic Detail Request: Is your application using standard READ COMMITTED or READ COMMITTED_SNAPSHOT?

How does Firebird's result set cache behave under READ COMMITTED isolation when concurrent updates occur?

1 answer

Conditions for Outdated Data

How to Flush or Disable Cache

Your perspective belongs here.

1 question comment