Implement AtomicKvStore in MysqlKvStore #1368

Motivation

Applications already using MysqlKvStore should be able to use atomic multi-key state without opening a separate PostgreSQL store. A notice and its budget, or fanout children and their parent cursor, need one conditional commit. This extends the adapter introduced by #585 with a generic KV capability rather than a MySQL-specific delivery-suspension store.

The existing MysqlKvStore.cas() in packages/mysql/src/kv.ts implements compare-and-swap (CAS). It acquires a pooled connection, begins a transaction, reads the row with FOR UPDATE, compares the logical value and commits or rolls back. That provides a starting point, not proof that the full AtomicKvStore contract is already met. Implement the new operations for the package's supported MySQL and MariaDB configurations.

Proposed API and use

Keep the constructor and existing methods. Implement AtomicKvStore and add:

getMany<T = unknown>(keys: readonly KvKey[]): Promise<(T | undefined)[]>;
setMany(entries: readonly KvStoreSetEntry[]): Promise<void>;
deleteMany(keys: readonly KvKey[]): Promise<void>;
casMany(entries: readonly KvStoreCasEntry[]): Promise<boolean>;

The shared contract defines entry types, equality, logical expiry, deletion, limits and failure semantics. An application can keep its existing pool and adapter:

import mysql from "mysql2/promise";
import type { AtomicKvStore } from "@fedify/fedify";
import { MysqlKvStore } from "@fedify/mysql";

const pool = mysql.createPool(databaseUrl);
const store: AtomicKvStore = new MysqlKvStore(pool);
const noticeKey = ["example", "notice"] as const;
const budgetKey = ["example", "budget"] as const;
const [notice, budget] = await store.getMany([noticeKey, budgetKey]);
const committed = await store.casMany([
  { key: noticeKey, expectedValue: notice, newValue: nextNotice },
  { key: budgetKey, expectedValue: budget, newValue: nextBudget },
]);

The application computes the next values from the snapshot and re-evaluates its rule when the result is false. These are proposed additions; pool configuration and shutdown remain the caller's responsibility.

Transaction and isolation contract

Run each mutation batch on one acquired connection with one transaction. Require transactional tables such as InnoDB and reject unsupported storage engines rather than weakening the contract. Do not treat a transaction opened on one connection as protecting statements sent through another pooled connection. Compare the full expected set, then commit all writes/deletes together or change nothing on a mismatch. Validate and encode inputs before mutation; reject duplicate mutation keys and oversized batches without silently splitting them.

getMany() needs one consistent read point, including expiry evaluation. Several ordinary reads within a transaction are not automatically a single snapshot under every isolation level. Use one suitable query or an explicitly established snapshot, following MySQL's consistent-read rules.

For conditional updates, MySQL's locking reads describe FOR UPDATE, while its transaction isolation rules explain why absent-key protection differs between REPEATABLE READ and READ COMMITTED. The current adapter explicitly notes that its missing-row approach relies on gap-lock behavior at REPEATABLE READ. Define and enforce the new batch's supported isolation behavior rather than assuming a missing row is protected in every configuration. Preserve handling of physically present but logically expired rows, and test absent and expired keys separately.

Acquire competing keys in a stable order and handle deadlocks with rollback and a bounded retry policy. Never continue a half-failed batch or report a backend error as a false comparison result. Recheck all expectations when retrying a transaction. Preserve the existing table/initialization/cleanup options and JSON comparison behavior. MySQL documents transaction control; MariaDB documents its own locking-read behavior, which must be validated on the supported versions rather than assumed identical.

Separate single-key cas() calls and an in-process mutex were rejected because neither protects a multi-key transition across clients. A new suspension-specific store was also rejected because the existing adapter can expose the generic contract. A single global state row would increase contention and size, so it is not the default replacement for multi-key operations.

Shipping criteria and docs

Run the shared Atomic KV conformance suite against MySQL and MariaDB in the package's claimed support range. Cover consistent reads, competing clients, missing/expired rows, mixed writes/deletes, a mismatch late in the batch, rollback after an early write, deadlocks, time to live (TTL) and codec behavior. Verify the package's Node, Deno and Bun declarations and existing KV tests. Document any isolation or engine restrictions before claiming support. This proposal is not a report that those tests have already passed.

Revise the support matrix and adapter section in docs/manual/kv.md, packages/mysql/README.md, public JSDoc, relevant KV-selection skill guidance and the MySQL package's changelog fragment. Ship this adapter independently of other adapters and of delivery suspension. Target Fedify 2.5 with the other Atomic KV issues.