Multi-Tenant SaaS in Production: Spring Boot Dynamic Data Source & Row-Level Isolation

1. Isolation Mode Selection: Why Shared Table Is the Baseline

The industry typically splits multi-tenant isolation into three approaches. Choice must factor in customer willingness to pay and operational bandwidth.

Independent Database : Each tenant gets a physical instance. Strongest isolation, faults don't cascade, but cost is extreme — provisioning, backup, scaling, monitoring all duplicated. Suits high-ARPU, compliance-heavy gov/finance clients; small teams cannot afford it.

Independent Schema : Shared instance, separate schemas. Good logical isolation, DDL/backup per schema, but application routing logic grows complex; connection pools reused yet management overhead remains. Fits mid-large SaaS where data sovereignty matters but budget falls short of dedicated instances.

Shared Table + tenant_id : All tenants share tables, distinguished by a column. Lowest ops overhead, easy scaling, unified SQL optimization. Downside: isolation relies entirely on code and constraints; a missed condition causes full cross-tenant leakage. For most standardized SaaS serving SMBs, this is the highest ROI baseline. The core challenge isn't storage but making the DAL layer intercept and route transparently so business developers only write CRUD without touching tenant_id.

2. Context Propagation & Dynamic Routing Internals

Spring's dynamic data source switching builds on AbstractRoutingDataSource — a proxy that, on getConnection(), calls determineCurrentLookupKey() to fetch a key from thread context and looks up the real DataSource from a routing map.

Pure shared-table architectures don't need dynamic data sources ; a SQL interceptor suffices. Dynamic routing shines in hybrid deployments: large customers get dedicated databases, others stay on shared tables. The architecture blends both — shared DB uses default routing, VIP tenants switch dynamically.

Tenant context must bind strictly to request lifecycle. Production strongly recommends plain ThreadLocal; avoid InheritableThreadLocal. Many assume it solves cross-thread propagation, but in Tomcat or custom thread pools workers are reused — parent context never auto-propagates, instead causing hard-to-debug dirty data.

public class TenantContextHolder {
    // Production: stick with ThreadLocal; propagation handled by thread-pool decorator or TTL
    private static final ThreadLocal<String> TENANT_ID = new ThreadLocal<>();

    public static void set(String tenantId) { TENANT_ID.set(tenantId); }
    public static String get() { return TENANT_ID.get(); }
    public static void clear() { TENANT_ID.remove(); }
}

Context injection typically lives in a web-layer interceptor — domain resolution, X-Tenant-Id header, or token parsing all work. One iron rule: cleanup must be in a finally block ; a single leak can pollute subsequent requests.

@Component
public class TenantOncePerRequestFilter extends OncePerRequestFilter {
    @Override
    protected void doFilterInternal(HttpServletRequest request,
                                    HttpServletResponse response, FilterChain chain) {
        String tenantId = request.getHeader("X-Tenant-Id");
        try {
            TenantContextHolder.set(tenantId);
            chain.doFilter(request, response);
        } finally {
            TenantContextHolder.clear();
        }
    }
}

After injection, MyBatis obtains a connection and the routing layer auto-binds the data source. Remember: data source switch must happen before Connection acquisition; switching inside a transaction has no effect .

3. SQL Interception & Automatic tenant_id Completion

Scattering WHERE tenant_id = ? in business code is a maintenance nightmare. MyBatis-Plus's TenantLineInnerInterceptor (AST-based via JSqlParser) is the de facto production standard.

Interceptor registration order matters — wrong order makes pagination and tenant filtering clash.

@Configuration
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        // 1. Tenant interceptor MUST come first
        TenantLineInnerInterceptor tenantInterceptor = new TenantLineInnerInterceptor();
        tenantInterceptor.setTenantLineHandler(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                String tid = TenantContextHolder.get();
                return new StringValue(StringUtils.isNotBlank(tid) ? tid : "global");
            }
            @Override
            public String getTenantIdColumn() { return "tenant_id"; }
            @Override
            public boolean ignoreTable(String tableName) {
                // Dictionary tables, global sequences, system config — no isolation needed
                return Set.of("sys_dict", "sys_config", "seq_record").contains(tableName);
            }
        });
        interceptor.addInnerInterceptor(tenantInterceptor);

        // 2. Pagination interceptor goes after
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

Rewrite logic: SELECT/UPDATE/DELETE traverse WHERE tree, inject AND tenant_id = ? if missing; INSERT checks column list, auto-adds and sets value; JOIN queries also propagate filter to joined tables.

But JSqlParser isn't omnipotent. Complex dynamic SQL inside <script>, handwritten native XML, or stored procedure calls often break AST parsing. Don't rely on the interceptor there — annotate the Mapper method with @TenantIgnore, manually pass the parameter in business layer, or route through a separate query channel.

4. Feign Cross-Service Calls & Async Thread Propagation

After microservice split, JVM memory isn't shared; context must travel explicitly over the network.

Feign propagation is straightforward: implement RequestInterceptor to forward the header. Downstream service reads the header and writes it back into its own context via the same filter logic, closing the loop.

@Component
public class FeignTenantInterceptor implements RequestInterceptor {
    @Override
    public void apply(RequestTemplate template) {
        String tid = TenantContextHolder.get();
        if (StringUtils.isNotBlank(tid)) {
            template.header("X-Tenant-Id", tid);
        }
    }
}

Async threads are the danger zone. Plain @Async or new Thread() loses ThreadLocal 100%. Must wrap Runnable with TaskDecorator:

public class TenantTaskDecorator implements TaskDecorator {
    @Override
    public Runnable decorate(Runnable runnable) {
        String context = TenantContextHolder.get();
        return () -> {
            try {
                TenantContextHolder.set(context);
                runnable.run();
            } finally {
                TenantContextHolder.clear();
            }
        };
    }
}

Register with the thread pool:

@Bean("saasExecutor")
public ThreadPoolTaskExecutor saasExecutor() {
    ThreadPoolTaskExecutor exec = new ThreadPoolTaskExecutor();
    exec.setTaskDecorator(new TenantTaskDecorator());
    exec.setCorePoolSize(10);
    exec.setMaxPoolSize(20);
    exec.initialize();
    return exec;
}

For teams demanding maximum stability, adopt Alibaba's TransmittableThreadLocal (TTL) — it enhances thread pools internally, more robust and broader coverage than handwritten decorators. Core principle unchanged: child thread must clean up; never rely on JVM auto-collection.

5. Connection Pool Governance & Slow SQL Prevention

In shared-table mode there's physically one database — no per-tenant connection pool isolation exists . HikariCP is globally shared. "Isolation" is actually application-layer rate limiting plus DB-layer indexes and constraints.

Don't memorize HikariCP formulas; tune per need. maximum-pool-size 15-30 usually suffices; larger increases context-switch overhead. connection-timeout 30s is ample. leak-detection-threshold must be enabled in production to catch code that borrows connections and never returns them.

Slow SQL defense rests on two hard rules:

Composite index is the floor . Every core business table must have a (tenant_id, business_primary_key/time) composite index. Single-tenant queries hit ref access; full table scans are eliminated. Without this index, shared tables will choke within a year.

Eliminate deep pagination . Beyond tens of millions of rows, LIMIT 1000000, 20 scans massive amounts of rows for the offset. Switch to cursor pagination on primary key or ordered field: WHERE id > ? ORDER BY id LIMIT 20 — performance differs by orders of magnitude.

6. Production Pitfalls & Tuning Records

Pitfall 1: Interceptor Order Reversed — Pagination Total Correct but List Cross-Tenant

Observed in production: paginated endpoint returned total count of all tenants' data while list contained only current tenant's subset. Root cause: interceptor registration order reversed. MyBatis-Plus pagination executes a COUNT wrapper first; if pagination interceptor runs before tenant interceptor, SQL becomes SELECT COUNT(0) FROM (originalSQL). When tenant interceptor later parses, JSqlParser no longer recognizes the main table structure, condition injection fails. Moving TenantLineInnerInterceptor before PaginationInnerInterceptor fixed it instantly. AST parsing order is irreversible — don't mis-order config.

Pitfall 2: Switching Data Source Inside Transaction — No Effect

Developer annotated service method with @Transactional, then mid-method called TenantContextHolder.set("tenantB") to query another tenant's data — still got tenant A's data. Reason: Spring's DataSourceTransactionManager already grabbed a Connection at transaction start and bound it to TransactionSynchronizationManager. Subsequent getConnection() calls on same thread are ignored, reusing the first connection. Only fix: tenant routing must be finalized before transaction begins . Cross-tenant operations require a new transaction ( REQUIRES_NEW) or a separate isolated service method.

Pitfall 3: ThreadLocal Leakage Causing Tenant Data Mix-Up

During late-stage load testing, logs showed tenant A requests receiving tenant B data. Investigation revealed an async callback threw an exception, skipping the finally cleanup, while the thread pool reused that worker. HikariCP's leak-detection only catches DB connection leaks, not context leaks. Solution: unified TenantContextRunner enforcing try-finally, all async paths forced through Decorator — issue eradicated.

Load Test Tuning Snippet

JMeter 500 concurrent mixed read/write. Initial QPS stalled, P99 latency ~1.2s. Flame graph showed JSqlParser parsing high-frequency SQL consuming ~15% CPU. Enabled MyBatis-Plus SQL caching (caches parse results) — CPU dropped immediately. Then occasional Connection refused appeared; slow query log revealed a full-table-scan report SQL holding connections until timeout. Added composite index, latency compressed to <300ms, QPS stabilized at 8000+. Zero cross-tenant access is the baseline; performance is tuned.

7. Production Specification Checklist

Architecture running is step one; long-term stability demands team discipline. Our hard rules:

Tenant metadata managed independently : Dedicated sys_tenant table storing status, quota, isolation level, routing key. Don't scatter tenant info across business tables — hot updates and dynamic routing depend on it.

Mandatory field & code generation : DB table templates require tenant_id as mandatory; code generator auto-adds @TableField(fill = FieldFill.INSERT). Missing field fails CI.

Strictly forbid bypassing interceptor : No hardcoded SQL in business code. JdbcTemplate or native MyBatis queries must go through gateway or manually append tenant condition. Where interceptor can't reach is where privilege escalation breeds.

Gateway-layer pre-validation : API Gateway or Nginx must verify X-Tenant-Id and token ownership match. Illegal cross-tenant requests blocked at traffic entrance, never reaching business layer.

Monitoring metrics tagged with tenant_id : Prometheus metrics include tenant_id as label. Grafana dashboards sliced by tenant show QPS, latency, error rate — noisy tenants instantly visible.

Per-tenant backup & restore : Shared tables can't afford full physical backups. Use logical backup filtered by tenant_id, combined with binlog for tenant-level point-in-time recovery (PITR) — large customer data loss becomes recoverable.

Multi-tenant architecture is never "just add a column". Dynamic routing and SQL interception solve "how to implement", but context lifecycle management, boundary enforcement, index constraints, and load-test validation determine whether the system survives production traffic long-term. Codify the rules, make interception transparent, then iterate at pace.