# Sessions and transactions

> The session model of the DuckDB gateway: how transactions pin to a node, when a pin is invalidated, and how prepared statements behave.

A FlightSQL connection maps to a session on the edge that tracks which Quack node, if any, the connection's open transaction is bound to. This page explains the session model, how transactions pin to a node, when a pin is invalidated, and how prepared statements behave. It builds on [Routing and statement classification](/qod/concepts/routing).

## The session

A session is opened lazily on the connection's first statement and holds:

- `connectionId` and `user`
- `poolKey` - the `(tenant, tenantDb, pool)` the connection handshook onto
- `pinnedNodeId` - the node an open transaction is bound to, or none
- `txOpen` - whether a transaction is currently open

Sessions are keyed by connection and dropped when the connection closes. The number of live sessions and how many are mid-transaction are surfaced as metrics (`flightsql_sessions_active`, and the in-transaction count).

## Transaction pinning

Routing normally picks the least-loaded node per statement. That is wrong for a transaction: a `BEGIN` on node A followed by a load-routed `COMMIT` could land on node B, where the transaction does not exist. To prevent that, the session pins:

- A `Begin` statement sets `pinnedNodeId` to the node it executed on and marks `txOpen = true`.
- While `txOpen` is true, every subsequent statement short-circuits the load-based selection and routes to the pinned node.
- A `Commit` or `Rollback` clears the pin and sets `txOpen = false`, returning the connection to per-statement load routing.

The router only honors a pin while a transaction is open, so a stale `pinnedNodeId` can never misroute a non-transactional statement.

On the FlightSQL edge each pinned statement still opens a fresh session on the pinned node, so a transaction spans one node but not one node session. The [native Quack front door](/qod/connecting/duckdb) goes one step further: while a DuckDB client's transaction is open, the manager keeps the node connection that ran the `BEGIN` and sends the following statements down it, so `BEGIN; ...; COMMIT` from a DuckDB client is a real transaction on one node session. `COMMIT` and `ROLLBACK` release that connection and unpin, exactly as above.

## Pin invalidation

A pin is dropped (pin and `txOpen` both cleared) when the bound node can no longer serve the transaction:

- **Pinned node disappeared.** If the pinned node is gone from the pool snapshot, the statement fails with "pinned node disappeared; transaction lost" and the pin is invalidated. The transaction cannot be continued; the client must start over.
- **Transient failure mid-transaction.** As covered on the routing page, a transient node failure inside an open transaction does not retry on another node (a partially-applied transaction cannot move). The pin is invalidated and the statement fails.
- **Pool vanished.** If the target pool is no longer found while a transaction was open, the pin is invalidated.

In each case the failure is explicit: there is no silent re-execution of a transaction's statements on a different node.

## Retry and re-pin

The one case where a transactional statement moves nodes is a `BEGIN` that hit a transient failure *before* the transaction was established. Because no work has been committed yet, the router retries the `BEGIN` once on a different node and pins the session to that fallback node, so the rest of the transaction (and its `COMMIT`) follows it. This is the only interaction between retry-once and pinning.

## Prepared statements

The edge supports prepared statements with a behavior shaped by Arrow JDBC and DBeaver, which need the result schema known at prepare time to dispatch a handle as a query rather than an update:

- On `createPreparedStatement` the edge **pre-executes** the SQL through the normal route-and-authorize path, caches the resulting stream by a generated handle, and returns the result's Arrow schema in the prepare response.
- On the following `getStream` the edge **replays the cached stream once** and the caller closes it. The handle is effectively single-use: the result is produced at prepare time, not re-run per execution.
- `closePreparedStatement` is a safety net that frees a handle the client opened but never executed.
- Parameter bindings are not supported in this version; a prepared statement is a fixed SQL string.

Because a prepared statement executes at prepare time, it passes through the same classification, ACL gate, routing, and (if it is a `BEGIN`) pinning as a directly executed statement.