database: explore multi-database transaction support

[gaborage]

Converted from issue #344 on 2026-07-25 (originally migrated from TODO.md on 2026-05-01). Per the 2026-07-24 backlog triage this is pure demand-gauging — it never reaches a decision — so it lives better as a Discussion than as an open issue.

Context

The current database.Tx interface supports single-database transactions. Distributed transactions across multiple databases would require significant complexity.

Open Questions

• Is there real-world demand for this?
• Would 2PC (two-phase commit) be sufficient, or do we need full SAGA pattern?
• Should this be a separate package to avoid bloating core?

Related

• database/types/

[farfromexact]

This is a useful demand-gauging question because multi-database transactions are not one small extension to a single-database Tx interface.

I would keep the core transaction API single-database and put coordination in a separate package. For most applications, an outbox plus an idempotent Saga is easier to operate: record the intent locally, deliver it with retries, and define compensating actions for partial failure. Two-phase commit is appropriate only when the participants, driver behavior, coordinator, timeout, and recovery semantics are all controlled; it adds blocking and operational failure modes.

Before implementing either pattern, define the contract for commit, timeout, retry, cancellation, coordinator restart, and partial success. A small adapter interface with a durable state machine, metrics, and one end-to-end example would establish whether there is enough real demand without bloating the core package.