skip to content

When each tenant has its own datasource or schema, what must the data-access layer do before a unit of work starts and after it ends?

level: seniorimportance: must knowfreq 55%

answer

  1. resolve the tenant before the boundary
  2. bind before the first statement runs
  3. reset the connection on release
  4. identifiers repeat, so key caches by tenant

basics

~20 s

Resolve the tenant and bind its datasource or schema before the first statement, since the connection is fixed for the unit's life. On release, reset any state the switch set, or the pool lends the connection out still pointed there.

solid answer

~50 s

Tenant routing is the same mechanism as replica routing with a much worse failure mode. The tenant identity has to be resolved at the edge of the request and be available at the boundary, because the unit binds one connection and that connection already points at one datasource, or carries one schema selection, from its first statement onwards. The trap is on the way back. If the switch was made by setting connection-level state rather than by borrowing from a per-tenant pool, that state must be cleared before the connection returns to the pool; a pooled connection is reused, and one that goes back still selecting the previous tenant's schema will serve the next request under the wrong identity, silently and with full permissions. The same reasoning extends to everything keyed off row identifiers - identity maps, shared caches, prepared-statement caches - all of which need the tenant in the key.

go deeper

for a junior

Recall the ordering: the layer must know which tenant it is working for before it runs the first statement, because the connection it borrows already points somewhere. Getting it wrong means touching another customer's data.

for a middle

Explain the two switching styles - a pool per tenant versus setting selection state on a shared connection - and why the second one has to be undone before the connection goes back to the pool.

for a senior

Emphasise the failure mode and its detection: an intermittent cross-tenant leak that only appears when connections are recycled under load, plus the caches and background jobs that need the tenant in their key or payload.

for a principal

Weigh isolation against connection budget across the tenant distribution, decide where resolution happens and that it fails closed, and define what must carry the tenant identity across every asynchronous hop in the system.

## The shape of the mechanism When tenants are separated at the storage level - a datasource each, or a schema each inside one server - the data-access layer has to answer "which tenant" before it can answer anything else. Concretely: 1. **Resolve the tenant** at the request edge, from the authenticated principal or the routed host, never from anything the caller can freely assert. 2. **Make it available at the boundary**, as ambient request state the routing layer can read when a unit starts. 3. **Bind the target before the first statement** - either by taking the connection from that tenant's own pool, or by taking a shared connection and setting the connection-level state that selects the tenant's schema. 4. **Run the unit**, during which the target cannot change, exactly as with replica routing. 5. **Restore the connection on release**, undoing any state set in step 3. Step 5 is the one that gets skipped, and it is the one that leaks data. ## Two ways to switch, with different risks | | pool per tenant | one pool, switch on the connection | |---|---|---| | how the tenant is selected | choose which pool to borrow from | set connection-level state after borrowing | | state left on release | none | whatever the switch set | | leak mode | none of this kind | connection returns still pointed at the last tenant | | cost | connections multiply by tenant count | one pool, but every borrow needs the switch | | scales to many tenants | poorly, idle connections per tenant | well | Neither is universally right. A handful of large tenants can afford a pool each and get isolation almost for free, including separate limits so one tenant cannot exhaust another's connections. Thousands of small tenants cannot - the idle connection count alone is prohibitive - so the shared pool with a per-borrow switch is the usual answer, and then the reset discipline is not optional. ## The mis-pointed pooled connection This is the trap the leaf exists to teach. A pooled connection is a long-lived object handed to one borrower after another. If tenant selection is written into connection-level state and never cleared, the next borrower inherits it. The next request then: - reads another tenant's rows while believing it is on its own; - writes into another tenant's tables, with no error, because the statements are perfectly valid there; - passes every application-level check, since the application believes it is correctly scoped. It is a cross-tenant data breach produced by a missing reset, and it is intermittent by nature - it only fires when a connection is recycled between tenants, so it is far more likely under load than in a test. The defences that work: - perform the reset in the release path that always runs, not at the end of the happy path; - assume it can be missed and **also** set the state on every borrow, so a connection is never used with inherited selection; - fail closed - if the tenant cannot be resolved at the boundary, refuse the unit rather than falling back to a default; - verify in a test that borrows, switches, releases and re-borrows, asserting the second borrow sees no inherited selection. ## Everything keyed by row identity needs the tenant too Tenants reuse identifiers. Every tenant has an order numbered 1. So any structure keyed by type plus identifier is ambiguous the moment it spans tenants: - the **identity map** inside a unit is safe only because the unit belongs to one tenant; - a **shared cache** that outlives the unit must carry the tenant in the key, or it serves one tenant's row to another - a cache hit takes no connection, so no amount of connection-level correctness saves you; - **prepared-statement or plan caches** keyed by statement text are fine only if the text fully qualifies the target; with schema selection carried on the connection, the same text means different tables at different times; - **background jobs** have no request and therefore no ambient tenant, so the tenant must be part of the job payload and re-bound explicitly when it runs. ## What this leaf does not cover Two neighbouring concerns are easy to blur into this one and should be named as separate: - **Choosing the layout** - shared tables, schema per tenant, database per tenant - is a data-modelling and operations decision, weighed on isolation, cost per tenant and the effort of rolling out a change to all of them. - **The tenant predicate inside a shared table** - the filter every statement must carry when tenants share tables - is a different mechanism from routing, and it applies where there is nothing to route to. What belongs here is narrower: binding the right datasource or schema to the unit's connection before the first statement, and giving it back clean.

  • Pool per tenant, or one pool with a switch per borrow?
    A pool per tenant gives real isolation, including separate connection limits, and leaves nothing on a connection to reset - but idle connections multiply with tenant count, so it suits tens of tenants, not thousands. A shared pool with a per-borrow switch scales, at the price of making the reset discipline load-bearing. Many systems run both: dedicated pools for the largest tenants, a shared pool for the long tail.
  • Where should the reset happen so it cannot be skipped?
    In the release path the pool always runs, alongside whatever returns the connection, not at the end of the successful branch - an exception must not be able to bypass it. Belt and braces is to also apply the tenant selection on every borrow, so a connection carrying inherited state is corrected before its first statement instead of being trusted.
  • How does a background job get the right tenant?
    Explicitly. There is no request scope to inherit from, so the tenant has to be part of the job's payload and re-bound at the start of the job's own unit of work. Jobs that fan out across tenants should treat each tenant as its own unit with its own binding, never reuse one bound connection across several.

A shared connection is a hot-desk workstation: whoever sits down next inherits whatever the previous occupant left logged in, so signing out has to be part of standing up, not something you do if you remember.

saying these in an interview costs you the question

  • Switches the tenant after the unit has already run a statement
  • Returns a connection to the pool without clearing the tenant selection
  • Takes the tenant from a request header the caller can set freely
  • Caches by row identifier without the tenant in the key
  • Falls back to a default tenant when resolution fails