Review SQL and transactions

Goal and scope

Audit SQL access from pool/admission and query plans through transactions, migrations, replicas, failover, and tenant permissions.

Domain invariants

• Total possible connections across application replicas, pools, migrations, jobs, admin tools, and failover headroom stay within database and proxy budgets.
• Connect/acquire/statement/lock/idle-transaction/request timeouts form an ordered end-to-end budget and release connections on every cancel/error path.
• Queries use representative predicates/cardinality and have a plan/index/data-access shape that scales without hiding write amplification or stale statistics.
• Transaction boundaries and isolation level match the protected invariant; lock order, deadlock/serialization retry, and external calls do not create unsafe or long transactions.
• Schema changes use expand/migrate/contract where mixed application versions or large data require it; backfills are resumable, bounded, observable, and do not hold unbounded locks.
• Primary/replica routing makes read-after-write, lag, failover, transaction, and consistency expectations explicit; reconnect does not reuse stale session state.
• Tenant and privilege enforcement lives in authoritative roles/policies/queries; RLS/policy changes are tested under actual application and migration roles.
• Backup/restore, rollback/forward-fix, generated ORM/client artifacts, and query observability are synchronized with schema/connection changes.

Audit method

1. Map request/job → pool/acquire → query/transaction/locks → primary/replica → commit → downstream/cache/event and all timeout/cancel boundaries.
2. Calculate connection demand per process/replica and compare it with server/proxy limits, reserved admin capacity, and failover topology.
3. Inspect representative explain/plan evidence, row estimates, scans, sorts, temp spill, index selectivity, and write/update cost for the changed query.
4. Trace concurrent anomaly/deadlock scenarios and identify the exact database constraint, lock, isolation, or optimistic version that owns the invariant.
5. Review migration/backfill under mixed versions, large tables, restart, replica lag, rollback, and tenant/role permissions.

Handoff

Retain the query/transaction, engine/isolation/application role, representative records/plan and exact lock/commit/read sequence. Identify the first violated invariant, budget or supported compatibility state and pass it to Improve SQL Databases. Separate a reproducible concurrency anomaly from a speculative index or capacity recommendation.