What state does a relational database server keep that is scoped to a single client session, and why does that state matter when connections are shared or reused?

## What "session state" means When a client authenticates, the server creates an execution context that survives across statements. Anything remembered between two statements on the same connection, and not visible to other connections, is session state. It is the reason a database connection cannot be treated as an interchangeable pipe. ## The inventory **Transaction context.** Whether a transaction is open, its isolation level, its snapshot, its accumulated locks, and any savepoints. This is the most consequential piece: an open transaction pins resources and blocks other writers. **Session parameters.** Timezone, encoding, autocommit mode, default isolation level, statement and lock timeouts, the schema search path or default schema, the currently assumed role, and similar knobs. These change how identical SQL behaves. **Temporary objects.** Temporary tables (and in some engines temporary sequences or types) live in a session-private namespace and are dropped when the session ends. Two sessions can hold same-named temp tables with different contents. **Prepared statements and cursors.** A prepared statement is parsed - and often planned - once and referenced by name for the rest of the session. Open cursors hold a position in a result set, and in some engines an underlying snapshot. **Locks and subscriptions.** Session-level advisory locks are held until released or the session ends, regardless of transaction boundaries. Engines with asynchronous notification channels bind subscriptions to the session too. **Small conveniences.** The last generated identity value, session-scoped user variables, the current search of role privileges, and the authenticated identity. ## Why it matters The entire value of connection reuse rests on the assumption that a connection handed to new work behaves like a fresh one. Session state breaks that assumption in three ways. *Leakage.* Code that sets a timeout, changes the isolation level, switches role, or creates a temp table and then returns the connection leaves those changes behind. The next borrower inherits them, producing bugs that appear only under load, only sometimes, and never in a single-connection test. These are among the most miserable production bugs to reproduce, because the symptom depends on which request previously used the same connection. *Resource retention.* A connection returned with an open transaction keeps its locks and prevents cleanup of old row versions engine-wide. A connection with hundreds of accumulated prepared statements holds their memory. Neither is visible to the borrower that inherits the connection. *Security.* If an application uses a role switch to represent the end user, forgetting to reset it hands the next request the previous user's privileges. That is not a performance bug; it is an authorisation defect. ## Making reuse safe The discipline is simple to state: **the state a unit of work changes, it must undo before releasing the connection.** In practice: - always end the transaction explicitly - a rollback on release is the cheap safety net; - prefer statement-scoped settings over session-scoped ones where the engine offers them (for example, a setting applied only for the duration of the current transaction); - issue an explicit reset/discard command on return, which drops temp objects, deallocates prepared statements, releases session locks, and restores parameters to defaults; - release session-level advisory locks deliberately, since they do not follow transaction boundaries; - set defaults at the role or database level rather than per session, so the correct value is restored by a reset instead of being reapplied by application code. ## The trade-off to know Session state is also useful. Prepared statements amortise parse and plan cost across many executions, temporary tables give a place to stage intermediate results, and session settings let one workload run with different timeouts from another. The engineering tension is that every one of those benefits depends on statements landing on the *same* session - exactly what aggressive connection multiplexing removes. Recognising that tension, and naming the reset that resolves it, is what an interviewer is listening for.