Race condition in create_session causes UniqueViolation on app_states under concurrent use #4954

Describe the Bug:
DatabaseSessionService.create_session contains a SELECT-then-INSERT pattern when initialising the app_states row for a new app_name. Under concurrent parallel calls, all
callers see None from the SELECT and all attempt the INSERT, causing a UniqueViolation on app_states_pkey. The same race exists for user_states.

Steps to Reproduce:

1. Configure ADK with a PostgreSQL backend (DatabaseSessionService)
2. Start with a fresh database (no existing app_states rows for the target app_name)
3. Spawn multiple sub-agents in parallel that each call create_session for the same app_name concurrently (e.g. via asyncio.gather)
4. Observe UniqueViolation on all concurrent calls except the first

Expected Behavior:
Concurrent create_session calls for the same app_name succeed without error.

Observed Behavior:
psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "app_states_pkey"
DETAIL: Key (app_name)=(custom_agent) already exists.
[SQL: INSERT INTO app_states (app_name, state, update_time)
VALUES (%(app_name)s::VARCHAR, %(state)s::JSONB, now())
RETURNING app_states.update_time]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Environment Details:

• ADK Library Version: 1.26.0
• Desktop OS: macOS
• Python Version: 3.13.2

Model Information:

• Are you using LiteLLM: No
• Which model is being used: gemini-2.5-flash (Vertex AI)

🟡 Optional Information

Regression:
Unknown — this is triggered by a specific parallel usage pattern rather than a regression in functionality.

Logs:
psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "app_states_pkey"
DETAIL: Key (app_name)=(custom_agent) already exists.
[SQL: INSERT INTO app_states (app_name, state, update_time) VALUES (%(app_name)s::VARCHAR, %(state)s::JSONB, now()) RETURNING app_states.update_time]
sqlalchemy.exc.IntegrityError: (psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "app_states_pkey"

Additional Context:
This race only occurs on the first ever session for a given app_name on a fresh database. Once the app_states row exists, all subsequent create_session calls find it via
SELECT and skip the INSERT — the race can never happen again for that app_name. This makes it consistently reproducible in environments where the database is regularly reset
(local dev, CI) and agents are immediately started in parallel.

The root cause is in database_session_service.py:
storage_app_state = await sql_session.get(schema.StorageAppState, (app_name))

if not storage_app_state:
storage_app_state = schema.StorageAppState(app_name=app_name, state={})
sql_session.add(storage_app_state)
All concurrent callers see None from the GET and all attempt INSERT. The fix would be to use INSERT ... ON CONFLICT DO NOTHING (or equivalent upsert) rather than
SELECT-then-INSERT.

Minimal Reproduction Code:
import asyncio
from google.adk.sessions.database_session_service import DatabaseSessionService

async def main():
service = DatabaseSessionService(db_url="postgresql+psycopg://...")
# Simulate parallel sub-agent startup on a fresh database
await asyncio.gather(*[
service.create_session(app_name="my_agent", user_id=f"user_{i}")
for i in range(5)
])

asyncio.run(main())

How often has this issue occurred?:

• Always (100%) — on a fresh database with parallel session creation