MonoGame Update Loop ↔ SQLite Persistence: Implementing Idempotent Retry Logic

Answer

The most efficient way to guarantee no duplicate records when transient SQLite errors occur from MonoGame’s Update loop is to:

1. Make each write operation idempotent by using SQLite’s INSERT … ON CONFLICT DO UPDATE (UPSERT) with a unique constraint on the logical record identifier.
2. Decouple the database work from the game thread: enqueue a lightweight descriptor of the write (e.g., a struct containing the identifier and the values to store) onto a thread‑safe concurrent queue inside Update.
3. Run a dedicated background thread that owns its own SQLiteConnection, continuously drains the queue, wraps each batch of descriptors in a transaction, attempts the UPSERT, and retries on transient errors (SQLITE_BUSY or SQLITE_LOCKED) with exponential back‑off (e.g., 10 ms, 20 ms, 40 ms up to a maximum of 5 attempts).
4. Signal shutdown with a CancellationTokenSource; the worker drains the remaining queue before exiting to avoid lost writes.

Why UPSERT over a pre‑write duplicate check?

• A pre‑check requires two separate statements (SELECT then INSERT) and introduces a race condition where another thread could insert the same key between the check and the insert.
• UPSERT performs the existence test and the insert/update atomically inside a single statement, eliminating that race and reducing round‑trips to the storage engine.
• Performance‑wise, a single UPSERT is typically faster than a SELECT + INSERT pair, especially when the table is indexed on the unique column.

Thread‑safety and connection handling

SQLite connections are not thread‑safe unless you enable shared‑cache mode and serialize access yourself. The simplest, safest approach is to give the background worker its own connection (opened once at startup) and never share it with the main thread. If you prefer shared‑cache, ensure all accesses are guarded by a lock or Mutex, but this adds unnecessary complexity for this scenario.

Verification steps (to be run in a test harness)

1. Inject simulated SQLITE_BUSY errors into the worker’s write attempt and assert that after the retry loop the database contains exactly one row per unique identifier.
2. Profile the Update method with a Stopwatch while the queue is saturated; confirm the average frame time stays below the target budget (e.g., < 16 ms for 60 FPS).
3. After forcing a process crash, restart the game and verify that no duplicate rows appear and that all enqueued writes have been persisted.