perf: RemoteSandboxService holds db_session across runtime-api network I/O + unbounded admin _secure_select (idle-in-transaction + full scans on v1_remote_sandbox)

[aivong-openhands]

Summary

RemoteSandboxService has two related performance problems against the v1_remote_sandbox table, both untouched by OpenHands/OpenHands#14637 (which fixed the same class of issue for poll_agent_servers in OpenHands/OpenHands#14636). Filing as a follow-up so the sandbox request path is tracked on its own.

1. DB session held across runtime-api network I/O → idle in transaction

RemoteSandboxServiceInjector.inject opens get_db_session(...) for the entire service/request lifetime (openhands/app_server/sandbox/remote_sandbox_service.py:869-887), and the service methods then perform runtime-api network calls with that transaction still open:

• search_sandboxes → _get_runtimes_batch() (network) — :314-329
• pause_old_sandboxes → /list + pause_sandbox() loop (network) — :574-606
• get_sandbox → _get_runtime() (network) — :341-347

Result: backends sit idle in transaction while waiting on network, holding open snapshots that block autovacuum on v1_remote_sandbox (dead tuples accumulate → scans get progressively more expensive). Observed in production: sessions idle-in-transaction for several minutes running the v1_remote_sandbox SELECT below.

Same root cause as OpenHands/OpenHands#14636. OpenHands/OpenHands#14637 established the fix pattern (fetch → release → network → re-acquire → write) but only for the polling loop.

2. Unbounded full-table scan in admin context

_secure_select() (:214-219) only adds WHERE created_by_user_id = ... when a user id is present. In admin context (no user filter) it emits an unbounded query:

SELECT id, created_by_user_id, sandbox_spec_id, session_api_key_hash, created_at
FROM v1_remote_sandbox;

The plan is a full Seq Scan over the whole table — no predicate, so an index cannot help. Any method that executes _secure_select() without a LIMIT in admin context reads the entire table on every call, which is the dominant CPU cost when sandbox activity is high.

Proposed fix

1. Scope the session tightly — apply PLTF-2895: scope DB sessions tightly in poll_agent_servers to prevent idle-in-transaction OpenHands#14637's pattern to RemoteSandboxService: release the DB session before runtime-api network calls and re-acquire a short-lived session only for writes. Don't span _send_runtime_api_request / _get_runtimes_batch with an open db_session.
2. Bound the admin-context _secure_select() — require a filter, paginate, or cap with LIMIT; and/or cache + reduce call frequency for any reconcile-style caller that legitimately needs the full set. (An index is not the fix for the no-WHERE query.)

Related

• perf: db_session held across network I/O in poll_agent_servers leaves connections "idle in transaction" (blocks autovacuum) OpenHands#14636 / PLTF-2895: scope DB sessions tightly in poll_agent_servers to prevent idle-in-transaction OpenHands#14637 — same class of bug + the fix pattern, polling loop
• perf: page poll_agent_servers conversation read in batches to bound peak memory #29 — poll_agent_servers memory paging

[github-actions]

This issue is stale because it has been open for 40 days with no activity. Remove the stale label or leave a comment, otherwise it will be closed in 10 days.

[aivong-openhands]

@OpenHands create a draft PR

[openhands-ai]

I'm on it! aivong-openhands can track my progress at all-hands.dev

[openhands-ai]

Implemented and opened draft PR #251: #251

Key changes:

• Replaced the request-lifetime RemoteSandboxService DB session with short transactional session contexts.
• Prevented unfiltered, unlimited admin _secure_select() queries and bounded lookup paths.
• Released the deferred finalizer’s count transaction before runtime-api deletion I/O.
• Added regressions for search/get/pause session scoping, admin query bounds, pause failure behavior, and finalizer ordering.

Verification:

• 256 relevant tests passed.
• Mandatory staged pre-commit hooks passed, including Ruff, formatting, and mypy.
• Commit: 0ccb567 perf: scope remote sandbox database sessions
• Branch: openhands/fix-remote-sandbox-db-sessions