DBAPI: transaction_checkout() uses explicit BeginTransaction RPC instead of inline begin

Environment details

OS: macOS (also reproducible on Linux)
Python version: 3.13.7
google-cloud-spanner version: 3.63.0
sqlalchemy-spanner version: 1.17.2

Problem

Connection.transaction_checkout() calls Transaction.begin() explicitly before the first query, which sends a standalone BeginTransaction gRPC RPC. This adds an unnecessary round-trip to every read-write transaction initiated through the DBAPI/SQLAlchemy path.

The Transaction class already supports inline begin — piggybacking BeginTransaction onto the first ExecuteSql request via TransactionSelector(begin=...). This is what Session.run_in_transaction() uses (it creates a Transaction without calling begin()). But the DBAPI's transaction_checkout() bypasses this by eagerly calling begin(), which sets _transaction_id before the first query and prevents the inline begin path in _make_txn_selector().

Current behavior (4 RPCs)

BeginTransaction → ExecuteSql (read) → ExecuteSql (write) → Commit

Expected behavior (3 RPCs)

ExecuteSql (read, with inline begin) → ExecuteSql (write) → Commit

Performance impact

Measured ~16ms overhead per transaction on the Spanner emulator. This affects every transaction_scope write operation when using the DBAPI or SQLAlchemy.

Root cause

Connection.transaction_checkout() calls self._transaction.begin() on L413. This was likely written before inline begin support was added to the Python client library. Inline begin landed in PR googleapis/python-spanner#740 (Dec 2022), but transaction_checkout was not updated to take advantage of it.

The fix is to remove the self._transaction.begin() call, letting execute_sql() / execute_update() / batch_update() use their existing inline begin logic via _make_txn_selector().

This is also fully conformant with PEP 249 (DB-API 2.0), which does not define a begin() method — transactions are implicit, and the mechanism by which the driver starts the server-side transaction is an implementation detail.

Proposed fix

Draft PR: googleapis/python-spanner#1502

Interim monkey-patch for `transaction_checkout()`

For anyone who needs this fix before the upstream PR lands, here's a monkey-patch that removes the explicit begin() call and lets execute_sql / execute_update use their existing inline begin logic via _make_txn_selector().

The patch

from google.cloud.spanner_dbapi.connection import Connection

def _inline_begin_transaction_checkout(self):
    """Patched transaction_checkout that skips explicit begin().
    
    Lets execute_sql/execute_update use inline begin via _make_txn_selector()
    when _transaction_id is None — the same code path that
    Session.run_in_transaction() already relies on.
    
    Saves one BeginTransaction RPC (~5-20ms) per read-write transaction.
    """
    if not self.read_only and self._client_transaction_started:
        if not self._spanner_transaction_started:
            self._transaction = self._session_checkout().transaction()
            self._transaction.transaction_tag = self.transaction_tag
            if self._transaction_isolation_level:
                self._transaction.isolation_level = (
                    self._transaction_isolation_level
                )
            else:
                self._transaction.isolation_level = self.isolation_level
            self.transaction_tag = None
            self._snapshot = None
            self._spanner_transaction_started = True
            # Skip self._transaction.begin() — let execute_sql/execute_update
            # use inline begin via _make_txn_selector()
    return self._transaction

Connection.transaction_checkout = _inline_begin_transaction_checkout

Where to apply it

Call this once at import time, before any database operations. For SQLAlchemy users, a good place is wherever you call create_engine().

How to verify

With OpenTelemetry tracing enabled, check that CloudSpanner.Transaction.begin spans no longer appear as children of SpannerSqlAlchemy.Execute. The first Execute should go directly to CloudSpanner.Transaction.execute_sql with an inline TransactionSelector(begin=...).

Before (4 RPCs):

SpannerSqlAlchemy.Execute
  CloudSpanner.Transaction.begin          ← ~16ms, gone after patch
  CloudSpanner.Transaction.execute_sql
SpannerSqlAlchemy.Execute
  CloudSpanner.Transaction.execute_sql
SpannerSqlAlchemy.Commit
  CloudSpanner.Transaction.commit

After (3 RPCs):

SpannerSqlAlchemy.Execute
  CloudSpanner.Transaction.execute_sql    ← inline begin piggybacked here
SpannerSqlAlchemy.Execute
  CloudSpanner.Transaction.execute_sql
SpannerSqlAlchemy.Commit
  CloudSpanner.Transaction.commit

Why this is safe

execute_sql already handles _transaction_id is None with a lock for inline begin (transaction.py L612-L623)
execute_update has the same inline begin path (transaction.py L502-L508)
commit() handles _transaction_id is None by calling begin() as a fallback for mutation-only transactions with no prior reads (transaction.py L272-L276)
run_in_transaction already relies on this exact code path — it creates a Transaction without calling begin() (session.py L566)

Caveats

This reaches into DBAPI internals (_client_transaction_started, _spanner_transaction_started, etc.) — pin your google-cloud-spanner version and re-verify on upgrades
Tested against google-cloud-spanner 3.55.0 and 3.63.0
Remove the patch once feat(dbapi): use inline begin to eliminate BeginTransaction RPC python-spanner#1502 is merged and released

Timeline

Nov 2020 — DBAPI driver added with transaction_checkout() calling Transaction.begin() explicitly (#160)
Dec 2022 — Inline begin added to Transaction.execute_sql / execute_update / batch_update via _make_txn_selector() (#840). This PR modified transaction.py, snapshot.py, session.py, database.py, and pool.py — but did not touch spanner_dbapi/connection.py
Jan 2024 — DBAPI transaction retry logic refactored (#1056). transaction_checkout() retained the explicit begin() call
Jan 2025 — Mockserver tests added for DBAPI tags (#1262), asserting BeginTransactionRequest in read-write request sequences — reflecting the existing DBAPI behavior at the time
Dec 2024 — Mock server updated to support inline begin (#1271)

No prior issues or PRs discussed applying inline begin to the DBAPI layer. The run_in_transaction path has used inline begin since googleapis/python-spanner#840; the DBAPI path was not updated in that PR or any subsequent one.

This issue was moved from the following repo as part of the monorepo migration: googleapis/python-spanner

feat(dbapi): use inline begin to eliminate BeginTransaction RPC (goog…

feat(dbapi): use inline begin to eliminate BeginTransaction RPC (#1502)

Navigation Menu

Repository navigation

DBAPI: transaction_checkout() uses explicit BeginTransaction RPC instead of inline begin #15871

Environment details

Problem

Current behavior (4 RPCs)

Expected behavior (3 RPCs)

Performance impact

Root cause

Proposed fix

Interim monkey-patch for `transaction_checkout()`

The patch

Where to apply it

How to verify

Why this is safe

Caveats

Timeline

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

DBAPI: transaction_checkout() uses explicit BeginTransaction RPC instead of inline begin #15871

Description

Environment details

Problem

Current behavior (4 RPCs)

Expected behavior (3 RPCs)

Performance impact

Root cause

Proposed fix

Activity

waiho-gumloop commented on Feb 28, 2026

Interim monkey-patch for transaction_checkout()

The patch

Where to apply it

How to verify

Why this is safe

Caveats

waiho-gumloop commented on Feb 28, 2026

Timeline

chalmerlowe commented on Mar 2, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Interim monkey-patch for `transaction_checkout()`