# CorpusFetch Method Slice (Zero-Prose Python Stdlib Reference)
# Canonical Residual: https://corpusfetch.com/c/within-what-transaction-scope-and-lock-timeout-bounds-must-an-instance-confine-database-connections-to-prevent-idle-in-transaction-deadlocks-and-pool-exhaustion.md
# Remnant Warrant:    https://corpusfetch.com/c/within-what-transaction-scope-and-lock-timeout-bounds-must-an-instance-confine-database-connections-to-prevent-idle-in-transaction-deadlocks-and-pool-exhaustion.warrant.json
# Markdown SHA-256:   c0110b8fbe2516ee063dbb29aee68f1fe125b4ca17e85cfc90fe6feac7059870
# Code AST SHA-256:   38fd50375b730656afa08110258466bc79c5eec306f920ab94e63d1335e9ef73
# Verified Classes:   DatabaseLockTimeoutError
# Defensive Guards:   6 fail-closed invariants (zero external deps)

import sqlite3
import time
from contextlib import contextmanager
from typing import Callable, Iterator, Sequence, TypeVar

T = TypeVar("T")


class DatabaseLockTimeoutError(RuntimeError):
    '''Raised when a transaction cannot acquire a database lock within lock_timeout_ms.'''
    pass


def create_bounded_sqlite_connection(
    db_path: str,
    *,
    busy_timeout_ms: int = 2000,
) -> sqlite3.Connection:
    '''
    Creates a SQLite connection configured with WAL concurrency mode and a strict
    finite busy/lock timeout so blocked writers fail fast instead of hanging forever.
    (For PostgreSQL, use: SET lock_timeout = '2000ms'; SET statement_timeout = '5000ms';
     SET idle_in_transaction_session_timeout = '3000ms';)
    '''
    conn = sqlite3.connect(db_path, timeout=busy_timeout_ms / 1000.0, isolation_level=None)
    conn.execute(f"PRAGMA busy_timeout = {int(busy_timeout_ms)}")
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


@contextmanager
def immediate_bounded_transaction(
    conn: sqlite3.Connection,
    *,
    max_duration_sec: float = 2.0,
) -> Iterator[sqlite3.Connection]:
    '''
    Opens an explicit short-lived write transaction (`BEGIN IMMEDIATE`), enforces
    a strict monotonic execution ceiling, and guarantees deterministic commit/rollback.
    '''
    t0 = time.monotonic()
    try:
        conn.execute("BEGIN IMMEDIATE")
    except sqlite3.OperationalError as exc:
        raise DatabaseLockTimeoutError(f"Failed to acquire write lock within timeout: {exc}") from exc

    try:
        yield conn
        elapsed = time.monotonic() - t0
        if elapsed > max_duration_sec:
            conn.execute("ROLLBACK")
            raise DatabaseLockTimeoutError(
                f"Transaction aborted: duration {elapsed:.2f}s exceeded max_duration_sec={max_duration_sec}s. "
                "Never run slow I/O inside a transaction."
            )
        conn.execute("COMMIT")
    except Exception:
        try:
            conn.execute("ROLLBACK")
        except sqlite3.Error:
            pass
        raise


def update_balances_deadlock_free(
    conn: sqlite3.Connection,
    adjustments: dict[str, int],
) -> dict[str, int]:
    '''
    Applies multiple account balance adjustments inside a single atomic transaction
    by sorting account IDs into canonical ascending order BEFORE touching any row.
    Eliminates AB-BA lock-order deadlocks across concurrent workers.
    '''
    if not adjustments:
        return {}

    # Gate 1: Sort keys deterministically so all concurrent transactions lock in identical order
    ordered_items = sorted(adjustments.items(), key=lambda pair: pair[0])

    updated_balances: dict[str, int] = {}
    with immediate_bounded_transaction(conn, max_duration_sec=1.5):
        for account_id, delta_cents in ordered_items:
            if isinstance(delta_cents, bool) or not isinstance(delta_cents, int):
                raise TypeError(f"delta_cents for {account_id!r} must be an integer")

            row = conn.execute(
                "SELECT balance_cents FROM accounts WHERE id = ?",
                (account_id,),
            ).fetchone()
            if row is None:
                raise KeyError(f"Account {account_id!r} does not exist")

            new_balance = int(row[0]) + delta_cents
            if new_balance < 0:
                raise ValueError(f"Insufficient funds for account {account_id!r}")

            conn.execute(
                "UPDATE accounts SET balance_cents = ? WHERE id = ?",
                (new_balance, account_id),
            )
            updated_balances[account_id] = new_balance

    return updated_balances