DatabaseSessionService: session row persists in storage without its user_state row ("User state missing" on later append_event)

[hungubqn0310]

Describe the bug

Under real production traffic, a small but persistent fraction of sessions end up with a row in the sessions table but no corresponding row in user_states for that (app_name, user_id). create_session() is supposed to create both atomically in one transaction, so this should not be possible — but it is happening continuously in production.

The symptom surfaces later, whenever anything calls append_event() on that session to apply a state update: DatabaseSessionService correctly raises, per _select_required_state():

ValueError: User state missing for app_name='mmvn_b2c_agent', user_id='<id>'. Session state tables should be initialized by create_session.

...but by that point the session has already been silently created without its user_state row, so every future state update for that user fails permanently until the row is manually backfilled.

Environment

• google-adk version: 2.3.0 (checked latest is 2.7.0; CHANGELOG 2.4.0–2.7.0 reviewed, no entry appears related)
• Database: PostgreSQL 16 (via asyncpg)
• Deployment: FastAPI via get_fast_api_app(..., auto_create_session=False), uvicorn, Docker
• Session creation goes exclusively through ADK's REST endpoints — no app code touches StorageUserState directly.

Evidence from production

SELECT count(DISTINCT s.user_id) FROM sessions s LEFT JOIN user_states us ON us.app_name=s.app_name AND us.user_id=s.user_id WHERE us.user_id IS NULL;
-- 2665 (of 19529 users = 13.6%)

Ongoing since 2025-10-30 through 2026-08-19, ~5-10/day. Reproduces on staging too (7/30 users).

What I've ruled out

• Collation/index corruption (forced seq scan still 0 rows)
• DB migration gap (legacy DB also missing these rows)
• App code deleting rows (full repo grep, nothing)
• Stale library bug (both envs on 2.3.0, SAVEPOINT logic looks correct on read)

Reproduction attempts (inconclusive)

1. 8x concurrent POST same session_id: 1 wins (200, consistent), 7 get raw HTTP 500 (see related bug), no orphan ever.
2. 8x concurrent POST same user, distinct session_ids: all succeed, 1 user_state row, no orphan.
3. Raw-socket abrupt disconnect (40x): 0 orphans — local request completes faster than ASGI can detect disconnect and cancel.

Leading hypothesis: task-cancellation-during-commit race under real network latency, not reproducible on localhost. Related prior issue: #3328.

Related bug found while reproducing

Same session_id race → loser hits raw IntegrityError → HTTP 500 with leaked SQL, instead of clean AlreadyExistsError/409.

Expected behavior

• create_session() should never leave an orphaned session without its state row, under any concurrency/disconnect scenario.
• Same-session_id race should give 409, not 500.

[surajksharma07]

@hungubqn0310 Wrapping the commit in try/except IntegrityError and raising AlreadyExistsError (same savepoint pattern as _get_or_create_state) fixes that part.
Could you verify it holds on your end and test thoroughly before raising a PR.

For the orphaned user_state race itself can't pin a root cause without more data.
Could you share your pinned asyncpg/SQLAlchemy versions and any connection-pool invalidation metrics around the known incident timestamps?

[hungubqn0310]

@surajksharma07 Thanks for the quick response!

Pinned versions (from uv.lock, matching the running venv):

• google-adk==2.3.0
• asyncpg==0.31.0
• sqlalchemy==2.0.44

Re: the IntegrityError → AlreadyExistsError patch — verified, works as described.

Reproduced the underlying race on create_session(): two coroutines calling with the same (app_name, user_id, session_id) — the up-front existence check isn't atomic with the insert that follows, so both pass the check and race on inserting the sessions row.

Tested against two backends:

• SQLite/aiosqlite — 5/5 trials without the patch: raw sqlite3.IntegrityError: UNIQUE constraint failed: sessions.app_name, sessions.user_id, sessions.id bubbles straight up to the caller.
• Postgres 17 + asyncpg (spun up a disposable container, not our live DB) — 4/5 trials without the patch: sqlalchemy.dialects.postgresql.asyncpg.IntegrityError wrapping asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "sessions_pkey" bubbles up the same way (the 5th trial's slower coroutine legitimately hit the existing early-exists check before reaching the insert — not related to the patch).

With the patch applied (wrapping the flush() around session insert in try/except IntegrityError, raising AlreadyExistsError, same pattern as _get_or_create_state) — 5/5 trials on both backends: clean AlreadyExistsError, exactly one session row survives, nothing left dangling.

Diff (around database_session_service.py:494-503):

       # Call to_session before commit to avoid post-commit lazy-load.
-      await sql_session.flush()
+      try:
+        await sql_session.flush()
+      except IntegrityError:
+        # A concurrent caller won the race on this (app_name, user_id,
+        # session_id) primary key (the up-front existence check above is
+        # not atomic with this insert). Same failure mode _get_or_create_state
+        # guards against for app_state/user_state; surface the same clean
+        # error here instead of letting the raw IntegrityError propagate.
+        raise AlreadyExistsError(
+            f"Session with id {session_id} already exists."
+        )
       session = storage_session.to_session(
           state=merged_state, is_sqlite=is_sqlite, is_postgresql=is_postgresql
       )

Happy to open a PR with this.

One caveat worth flagging: I don't think this patch is the fix for the orphaned user_state row itself. _rollback_on_exception_session rolls back the whole transaction on any exception (including the newly-raised AlreadyExistsError), so the losing coroutine in this particular race shouldn't leave any row behind — this patch just replaces a raw, confusing IntegrityError with a clean one for a session-PK collision. It's a real bug worth fixing on its own, but I don't believe it's the same code path that produces the orphaned user_state rows we're seeing in production.

Re: connection-pool invalidation metrics around incident timestamps — I don't have this yet. We don't currently capture pool-invalidation events tied to the orphan occurrences; we do have defensive logging in place ([ORPHAN_USER_STATE] on the app side when append_event raises the "state missing" error, plus a middleware catching duplicate in-flight create_session calls) but it hasn't fired since we added it, so no real incident to pull metrics from yet. Will follow up here once we have a captured occurrence with the surrounding pool metrics.

[hungubqn0310]

Opened a PR with the verified fix: #6843

Wraps the create_session() insert flush in try/except IntegrityError → AlreadyExistsError, same SAVEPOINT pattern _get_or_create_state already uses. Verified on both sqlite+aiosqlite and Postgres 17 + asyncpg, added a regression test, full unit test suite passes (12619 passed).

As noted in the PR, this only fixes the raw-IntegrityError-on-race part — I don't believe it's the root cause of the orphaned user_state row itself (_rollback_on_exception_session rolls back the whole transaction on any exception, so this particular race shouldn't leave a row behind). Still don't have connection-pool metrics for the orphan occurrences to help root-cause that part further.