[BUG] SQLite database lock contention causes HTTP 500/503 under concurrent traffic

[JhaSourav07]

Description

When running OrcaRouter-Lite with the default SQLite database (sqlite+aiosqlite:///./orca.db), concurrent write transactions trigger database lock contention resulting in sqlite3.OperationalError: database is locked.

Because AuthMiddleware updates last_used_at via session.commit() on every authorized request, and API routes write RequestLog rows concurrently, multiple active write transactions contend for SQLite's exclusive database lock. Under default SQLite engine settings (DELETE journal mode without WAL enabled and without connection busy timeout configuration), concurrent requests fail with HTTP 500 or 503 Service temporarily unavailable.

---

Environment & Impact

• Affected Version: OrcaRouter-Lite v0.1.0
• Affected Components: packages/db/engine.py, packages/auth/key_validator.py
• Severity: ⚠️ High
• Impact: High failure rate (HTTP 500/503) under concurrent traffic when running default SQLite configuration.

---

Technical Root Cause Analysis

1. Default Journal Mode (DELETE): In packages/db/engine.py, create_async_engine initializes SQLite connections without setting PRAGMA journal_mode=WAL;. Default SQLite DELETE journal mode acquires an exclusive lock during writes, blocking both readers and other writers.
2. Missing Busy Timeout: Connections are created without setting PRAGMA busy_timeout=30000; or configuring timeout in connect_args.
3. Per-Request Auth Writes: Every API request passing through AuthMiddleware calls validate_api_key(), which executes an UPDATE api_keys SET last_used_at=... transaction. Concurrent requests attempt simultaneous write transactions on orca.db, quickly exceeding SQLite's default lock timeout.

---

Steps to Reproduce

Option A: Using the Automated Pytest Integration Test

Run the targeted concurrency integration test:

pytest tests/integration/test_sqlite_concurrency.py

Observed Output:

FAILED tests/integration/test_sqlite_concurrency.py::test_sqlite_lock_contention_under_concurrent_writes
AssertionError: Expected 0 database lock errors under concurrent load, but encountered 19 failures!
First error: (sqlite3.OperationalError) database is locked
[SQL: UPDATE api_keys SET last_used_at=?, updated_at=CURRENT_TIMESTAMP WHERE api_keys.id = ?]

---

Option B: Parallel cURL Load Test

1. Start Uvicorn server:

   uvicorn app.main:app --port 8000

2. Execute 10 parallel requests using seq and xargs:

   seq 10 | xargs -P 10 -I {} curl -s -X POST http://localhost:8000/v1/chat/completions \
     -H "Authorization: Bearer sk-orca-test" \
     -H "Content-Type: application/json" \
     -d '{"model": "auto", "messages": [{"role": "user", "content": "ping"}]}'

Observed Output: Multiple requests fail with HTTP 503 Service temporarily unavailable and Uvicorn logs tracebacks containing sqlite3.OperationalError: database is locked.

---

Expected vs Actual Behavior

• Expected Behavior: SQLite database engine should handle concurrent reads and writes gracefully under load using Write-Ahead Logging (WAL) mode and adequate busy timeouts.
• Actual Behavior: Concurrent write transactions lock the entire database file, causing request failures (sqlite3.OperationalError: database is locked).

---

Proposed Solution

1. Enable WAL Mode & Busy Timeout in Engine Listener: Configure packages/db/engine.py to issue PRAGMA journal_mode=WAL; and PRAGMA busy_timeout=30000; on SQLite connection creation:

from sqlalchemy import event

@event.listens_for(engine.sync_engine, "connect")
def _set_sqlite_pragmas(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL;")
    cursor.execute("PRAGMA busy_timeout=30000;")
    cursor.close()

2. Set Connection Timeout in connect_args:

connect_args={"check_same_thread": False, "timeout": 30.0}