JDBCQuotaStore hits CannotSerializeTransactionException in clustered deployments

[groldan]

When more than one JVM runs DiskQuotaMonitor against the same database (any clustered GeoServer deployment), concurrent updates to the same TILESET or TILEPAGE row deterministically abort with PostgreSQL's could not serialize access due to concurrent update:

org.springframework.dao.CannotSerializeTransactionException:
  PreparedStatementCallback;
  SQL [UPDATE TILEPAGE SET FILL_FACTOR = ? WHERE KEY = ?];
  ERROR: could not serialize access due to concurrent update

There is no retry, so the operation just fails. Easy to trigger: set a global quota lower than the cached size on two nodes - both LayerQuotaEnforcementTask instances try the same UPDATE TILEPAGE SET FILL_FACTOR = 0 ....

Why it matters

The wire-up for clustered GeoServer is essentially complete in the rest of the stack; the JDBC quota store is the missing piece. Today every write surface in JDBCQuotaStore is vulnerable when two JVMs touch the same row. The hottest case is the global ___GLOBAL_QUOTA___ row in TILESET, which every tile-writing node updates on every batched flush. With N replicas, that row is the cluster-wide hotspot.

Root cause

JDBCQuotaStore.java:

this.tt.setIsolationLevel(TransactionTemplate.ISOLATION_SERIALIZABLE);

SERIALIZABLE was a defensive choice for single-JVM correctness. There is no retry on serialization failure. Two hot paths additionally use a tolerant SELECT-then-INSERT-or-UPDATE loop in Java rather than an atomic upsert:

• upsertTilePageFillFactor()
• upsertTilePageHitAccessTime()

Both loop up to 100 times; under SERIALIZABLE the read + write within a single transaction triggers Postgres SSI's predicate locking and aborts when two nodes touch the same TILEPAGE row.

The TILESET quota update is already an in-place SQL increment (SQLDialect.java:326: UPDATE TILESET SET BYTES = BYTES + (:bytes) ...), so it's safe at READ COMMITTED today; only the isolation level needs to change for that path.

Proposed fix

Part 1 (the fix, all dialects): drop SERIALIZABLE

Change JDBCQuotaStore.java to use the default isolation (READ COMMITTED). One line. No SQL changes.

This alone resolves the reported error. Under READ COMMITTED:

• The TILESET quota update is already an in-place SQL increment (SQLDialect.java: UPDATE TILESET SET BYTES = BYTES + (:bytes) WHERE KEY = :tileSetId). Two concurrent writers on the same row just serialize via row-level locks; second writer waits and proceeds with the latest committed value. No conflict.
• The TILEPAGE tolerant loops (upsertTilePageFillFactor, upsertTilePageHitAccessTime) are idempotent by design - they already retry on no-rows-modified. Without SSI predicate locking they hit fewer conflicts and the existing retry covers what's left.
• setTruncated's SELECT-then-UPDATE is benign at READ COMMITTED: two concurrent calls both arrive at FILL_FACTOR = 0, last-writer-wins on the same value, no correctness issue.

Part 2 (Postgres only, optimization): native ON CONFLICT UPSERTs

Independent of Part 1. Replaces the 100-attempt loops with single atomic statements on Postgres.

PostgreSQLDialect.java is an empty subclass today. Add a capability flag dialect.supportsAtomicUpsert() (default false) and Postgres-native UPSERT overrides for the three insert-or-update paths:

• getOrCreateTileSet -> INSERT INTO TILESET ... ON CONFLICT (KEY) DO NOTHING. Replaces the loop at JDBCQuotaStore.java.
• upsertTilePageFillFactor -> INSERT INTO TILEPAGE ... ON CONFLICT (KEY) DO UPDATE SET FILL_FACTOR = LEAST(1.0, GREATEST(0.0, TILEPAGE.FILL_FACTOR + EXCLUDED.FILL_FACTOR)). The fill-factor delta moves from Java into the SQL VALUES clause.
• upsertTilePageHitAccessTime -> analogous, with GREATEST on LAST_ACCESS_TIME_MINUTES (commutative) and addition on NUM_HITS.

In JDBCQuotaStore, branch once on dialect.supportsAtomicUpsert() and call the new dialect methods when supported, fall back to the existing loop otherwise.

This is fewer round-trips per tile add (one statement vs the SELECT+UPDATE/INSERT pair) and removes the retry loop entirely on Postgres. It is not required to fix the reported error - Part 1 is.

Scope

In scope:

• Part 1 (isolation drop) for everyone.
• Part 2 (native UPSERT) for PostgreSQLDialect only.

Out of scope (separate, unfunded task):

• Native UPSERT for OracleDialect. The existing tolerant loop remains the documented behavior for Oracle.
• H2 / HSQL parity beyond making sure existing tests keep passing.

Affected versions

Present on main and on the 1.28.x series. Backport target: 1.28.x (consumed by GeoServer 2.28.x).

Related

The simpler JDBCQuotaStore.renameLayer bug (only updates LAYER_NAME, leaves TILESET.KEY and TILEPAGE.TILESET_ID stale) is tracked in

• JDBCQuotaStore.renameLayer only updates LAYER_NAME, leaves TILESET.KEY and TILEPAGE.TILESET_ID stale #1526

[aaime]

SERIALIZABLE has been added because of repeated cases of quota slowly going off sync with the file system; the disk quota is a ledger that updates on differences; it cannot have tolerances in that sense. I haven't had reports of this happening since SERIALIZABLE was used, so I guess that dropping it is not safe, and the current logic is not as bomb proof as it seems to be.

[groldan]

Hey @aaime, good on the push back, I think we can keep the SERIALIZABLE isolation level, you're right on the ledger semantics.
Starting off the day with a clear head, I can't help wondering if #1526 would've contributed to the drift between db and actual disk usage. After a rename, TILEPAGE.TILESET_ID keeps the old prefix, when the cleaner expires pages: StorageBroker.delete(oldName,..) the blobstore doesn't find any, but the quota is changed in the db.

About SERIALIZABLE , I'm pretty sure I've hit the abort while a node ran the cleaner while another was seeding. I suspect the missing piece is retry. My hypothesis is without it, when SSI aborts the transaction, QueuedQuotaUpdatesConsumer.run() catches the exception, logs, and proceeds, and that batch of deltas is never saved. That'd be a lost-update path, the type SERIALIZABLE is meant to defend against, but SSI is documented as expecting the application to replay aborted transactions, so by adding retry-on-CannotSerializeTransactionException we'd be completing the workflow.

Rough plan would then be:

• Reproduce with a testcontainers-Postgres test to have a regression asserting both "no abort propagates" and "ledger total matches the expected sum end-to-end"
• Add bounded retry around each tt.execute(...) in JDBCQuotaStore. SERIALIZABLE stays, test fixed
• Postgres ON CONFLICT upserts in PostgreSQLDialect as an optimization (orthogonal to retry, but reduces the conflict rate so retries rarely fire).

Keep you posted