Read-your-writes

Replication from your upstream database to Readyset is asynchronous. A session that writes a row and immediately reads it back can therefore see the pre-write value if the read is served from a Readyset cache before the cache has caught up to the write. Readyset gives you two separate controls for this, and they cover different code paths:

• Inside a transaction: the cache's per-cache transaction policy (set on CREATE CACHE / CREATE SHALLOW CACHE).
• Outside any transaction: an optional opportunistic read-your-writes window configured at the adapter level via --opportunistic-ryw-ms.

The two controls do not overlap. Opportunistic read-your-writes is dormant inside transactions; the per-cache transaction policy is what governs in-transaction routing. Pick the right control for the place the read happens.

Inside a transaction: the cache's transaction policy

When a session is inside an explicit BEGIN ... COMMIT block (or an implicit transaction under autocommit=0), routing is decided entirely by the cache's transaction policy, declared on the cache:

CREATE CACHE [ALWAYS | UNTIL WRITE] [<name>] FROM <query>;
CREATE SHALLOW CACHE POLICY ... [ALWAYS | UNTIL WRITE] FROM <query>;

There are three possible policies. They differ in how reads are routed once the session is inside a transaction:

NEVER (default, no keyword)

When no keyword is supplied on CREATE CACHE / CREATE SHALLOW CACHE, the cached query is always proxied upstream while the session is inside a transaction. The cache is consulted only once the transaction commits. This is the safest default: every read inside the transaction observes the same snapshot the upstream would, including the session's own pending writes.

Use this when an unconditional read-your-writes guarantee inside the transaction is required and cache hits inside transactions are not important.

ALWAYS

Documented at ALWAYS. The cached query is served from Readyset regardless of transaction state. Reads inside the transaction can therefore observe a pre-write value of the session's own writes (the cache has not yet absorbed them).

Use this when occasional stale reads inside transactions are acceptable in exchange for cache hits on every statement, even inside a BEGIN/COMMIT block. Common case: drivers that wrap every statement in a transaction yet only ever issue reads.

UNTIL WRITE

Documented at UNTIL WRITE. Reads are served from the cache as long as the transaction has not yet observed an INSERT, UPDATE, or DELETE. The first write flips the session into a "had write in txn" state, and every subsequent read in that transaction is proxied upstream, including reads from caches whose tables have nothing to do with the write. The flip is session-wide, not per-table. The cache becomes available again at the next transaction boundary.

This is the right default for client drivers that wrap every statement in an implicit transaction (Python with autocommit=False, JDBC defaults, ORMs that do not expose autocommit). Read-only-so-far statements get cache hits; once the transaction has written, the rest of the transaction reads its own writes through the upstream.

UNTIL WRITE is also the default policy for caches created automatically by --auto-cache and by hint-based auto-creation.

Picking a policy

Outside any transaction: the opportunistic window

Outside of a transaction (autocommit-on, no BEGIN), Readyset has no transaction boundary on which to flip routing. A session that does:

INSERT INTO t (id) VALUES (1);   -- write goes to upstream
SELECT * FROM t WHERE id = 1;    -- next statement is autocommitted

may be served the second statement from a Readyset cache before the cache has observed the insert. This is where --opportunistic-ryw-ms comes in. When set, every write on the session arms a deadline now + N ms. Until that deadline elapses, every read on the same session bypasses the cache and is proxied to the upstream, regardless of the cache's transaction policy, and regardless of whether the read touches the same table as the write. A write to one table arms the window for all caches that session subsequently reads from.

INSERT (autocommit on)  ──▶ window armed for N ms
SELECT during window     ──▶ proxied upstream (sees the write)
... time passes (> N ms) ...
SELECT after window      ──▶ served from cache (may be pre-write)

The window also applies on the canonical BEGIN; INSERT; COMMIT; SELECT pattern: BEGIN clears the window (the per-cache policy takes over while inside the transaction), and COMMIT re-arms it if the transaction wrote, so the post-COMMIT SELECT reads the session's own writes through the upstream. ROLLBACK clears it (rolled-back writes never landed).

This is opportunistic, not a guarantee

When to enable it

Enable --opportunistic-ryw-ms only when:

• Sessions issue writes outside transactions and immediately read back, and
• Occasional stale reads after the window are acceptable for your workload, and
• You have measured replication lag and picked a window that is long enough to cover the typical gap (too short and the freshly-written row may still be served stale; too long and reads that could safely hit the cache go upstream instead).

If you need stricter guarantees, do the writes and reads inside a transaction and pick the appropriate transaction policy above.

The window is disabled by default. The default is conservative because the right value depends on your replication lag and your tolerance for stale reads after the window.

See also

• --opportunistic-ryw-ms: flag reference.
• ALWAYS, UNTIL WRITE: per-cache transaction policy keywords.
• CREATE CACHE: full DDL syntax, including the [DEEP | SHALLOW] modifier.
• Auto-caching: default policy for auto-created caches.
• Hint-based caches: default policy for hint-created caches.