Inconsistent result sets during paginated fetches without deterministic ordering

Question

DBeaver utilizes a configured fetch size to limit the number of rows loaded into the JVM from the database cursor. When navigating large datasets using the 'Fetch next page' mechanism, the application requests subsequent blocks of rows based on the initial query execution.
A conflict arises when the underlying table lacks a deterministic primary key or a unique ORDER BY clause. In these scenarios, if the database state is modified externally between page requests, the database engine may return duplicate rows or skip records entirely during the next fetch because the physical row order is not guaranteed.
Since the results grid caches fetched pages locally until a manual refresh is triggered, it is unclear how DBeaver maintains data integrity across the full result set for non-deterministic queries.

How does DBeaver guarantee row consistency across paginated fetches when the query lacks a unique sort?
  Is there a configuration to force a server-side snapshot for the entire result set to prevent drift?

Tasadduq Burney · Answer

Answer
DBeaver does not guarantee row consistency when a query lacks a deterministic ORDER BY; it relies on the underlying JDBC cursor’s stability, which is not a snapshot.
Confirmed facts

DBeaver fetches rows via a forward‑only ResultSet with the configured fetch size; it does not request a database snapshot.
Consistency depends on the DBMS cursor stability and the transaction isolation level of the connection.
Without a unique sort, concurrent inserts, updates or deletes between page fetches can cause duplicate or missing rows.

Likely explanation
The observed drift occurs because the database engine may return rows in a different physical order as the table changes, and DBeaver’s grid caches each page locally until a manual refresh.
Steps to obtain a stable page‑by‑page view

Add a deterministic ORDER BY clause (e.g., by primary key or a unique column) to the query.
Run the query in a repeatable-read (or serializable) transaction if your DBMS supports it.
Optionally enable DBeaver’s "Use scrollable/insensitive result set" setting (found under connection properties → Data source) so the driver requests a TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY cursor when available.

Server‑side snapshot
DBeaver has no configuration that forces a server‑side snapshot of the entire result set. Achieving a full snapshot requires DB‑specific features (e.g., PostgreSQL cursor WITH HOLD, Oracle SNAPSHOT, or copying to a temporary table) outside of DBeaver’s pagination controls.
Missing diagnostic detail
Does your JDBC driver and database report support for scrollable/insensitive result sets (TYPE_SCROLL_INSENSITIVE)? If they do not, the scrollable option will fall back to the default forward‑only behavior and you must rely on ORDER BY and transaction isolation.

Inconsistent result sets during paginated fetches without deterministic ordering

1 answer

Answer

Confirmed facts

Likely explanation

Steps to obtain a stable page‑by‑page view

Server‑side snapshot

Missing diagnostic detail

Your perspective belongs here.

0 question comments