🏗️ Section 17 · Question #14

What problems arise when using shared database

The Shared Database Anti-Pattern occurs when multiple independent microservices connect directly to the same physical or logical database instance, reading and mutating the same...

🏗️ Microservices 📄 Source Markdown

🟢 Junior Level

The Shared Database Anti-Pattern occurs when multiple independent microservices connect directly to the same physical or logical database instance, reading and mutating the same database tables via direct SQL queries.

       ┌───────────────────┐       ┌────────────────────┐
       │   Order Service   │       │    User Service    │
       └─────────┬─────────┘       └─────────┬──────────┘
                 │ (Direct SQL)              │ (Direct SQL)
                 └─────────────┬─────────────┘
                               ▼
                    ┌─────────────────────┐
                    │   SHARED DATABASE   │ ◄── ❌ CATASTROPHIC ANTI-PATTERN!
                    │ (users, orders, ...)│
                    └─────────────────────┘

The Real-World Analogy

Imagine five independent chefs operating five different restaurants who all share a single communal refrigerator and spice rack without speaking to one another. One chef throws away a container of spice thinking it is old, instantly ruining the signature dishes of all other four chefs!

The Inevitable Outcome: A “Distributed Monolith”

A shared database strips microservices of all their intended benefits while retaining all the operational penalties of distributed systems:

  • You suffer from network latency, complex distributed tracing, and distributed deployments.
  • You lose independent team deployments, schema refactoring autonomy, and localized fault isolation.

🟡 Middle Level

1. The 5 Systematic Failure Mechanisms of Shared Databases

Failure Mechanism Database Underlying Cause Production Fallout
1. Schema Tight-Coupling One team alters table columns (ALTER TABLE users DROP COLUMN phone;). Dependent microservices crash instantly in production with unhandled SQL exceptions.
2. Connection Pool Starvation An unhedged analytics query grabs 200 connections in the shared database pool. Mission-critical Auth and Checkout services receive zero available connections $\to$ 💥 HTTP 503 Outage.
3. Lock Contention & Escalation Service A updates 50,000 rows in a long transaction; database escalates to a Table Lock. Service B’s high-speed point reads (SELECT ... WHERE id = 1) freeze, causing thread pool starvation.
4. Single Point of Failure (SPOF) The shared database suffers storage exhaustion, hardware failure, or high CPU thrashing. 100% of all company microservices collapse simultaneously (unbounded blast radius).
5. Migration Tool Collisions Two services launch concurrently in Kubernetes, running Flyway/Liquibase migrations simultaneously. Collisions on the shared migration history table trigger deadlocks and abort pod deployments.

2. Anatomy of a Silent Production Outage

// Service A (Order Service) queries the 'discount' column in its User entity:
@Entity
@Table(name = "users")
public class UserEntity {
    @Id
    private Long id;

    @Column(name = "discount")
    private BigDecimal discount;
}

// Service B (User Service) refactors its domain to use a Loyalty Points system:
// V5__drop_discount_column.sql:
// ALTER TABLE users DROP COLUMN discount;

// RESULT: Service B deploys successfully.
// 2 minutes later, Service A crashes on every user checkout with:
// org.postgresql.util.PSQLException: ERROR: column "discount" of relation "users" does not exist!

🔴 Senior Level

1. The Hidden Distributed Deadlock Hazard

A catastrophic scenario exclusive to shared databases across network services is the Cross-Network Distributed Deadlock:

[Service A] ──► 1. Opens DB Transaction: SELECT ... FOR UPDATE (Locks User #10)
     │
     ▼ 2. Synchronous HTTP Call: GET /process-order
[Service B]
     │
     ▼ 3. Executes SQL: UPDATE users SET ... WHERE id = 10 (BLOCKED waiting for Service A's lock!)
  1. Step 1: Service A opens a database transaction and acquires an exclusive row lock on User #10.
  2. Step 2: Holding the database transaction open, Service A makes a synchronous HTTP call to Service B.
  3. Step 3: To service the HTTP request, Service B attempts to update User #10 in the shared database, blocking indefinitely while waiting for Service A to commit.
  4. The Deadlock: Service A is waiting on an HTTP response from Service B; Service B is waiting on a database lock release from Service A. Because the circular dependency spans an HTTP network socket and an internal database lock table, relational database deadlock detection algorithms are completely blind to the cycle. Both services remain deadlocked until HTTP socket timeouts fire, exhausting Tomcat worker threads across both services.

2. Zero-Downtime Migration: Strangler Fig + Debezium CDC

Migrating from a legacy shared database to Database per Service on a live 24/7 enterprise system:

[Legacy Shared DB] ──(1. CDC: Debezium / Kafka Connect)──► [New Dedicated User DB]
       ▲                                                              │
       │                                                              ▼
[Order Service] ──(2. API Call: GET /users/42)──► [User Service] ─────┘
  1. Establish Data Ownership: Formally declare that User Service is the sole authoritative owner of the users table.
  2. Encapsulate Behind Public APIs: Revoke SQL access to users for all other microservices; refactor callers to use REST or gRPC endpoints exposed by User Service.
  3. Provision Dedicated Database: Provision a new, isolated PostgreSQL database instance (user_db).
  4. Real-Time CDC Replication: Deploy Debezium + Kafka Connect to tail the Write-Ahead Log (WAL) of the old shared database, streaming every row mutation to user_db with sub-10ms replication lag.
  5. Dark Launch / Shadow Reads: Configure User Service to read from user_db in parallel, logging and alerting on any payload discrepancies with the old database.
  6. Traffic Cutover: Switch application write connections to user_db, decommission the Debezium replication pipeline, and drop the legacy tables from the shared database.

4 Tricky Questions

1. How does a “Hidden Distributed Deadlock” occur across microservices sharing a single database?

Answer: A hidden distributed deadlock occurs when locking dependencies span both network transport protocols (HTTP/gRPC) and database transaction locks. For instance, Service A opens a database transaction and acquires an exclusive row lock (SELECT FOR UPDATE), then executes a synchronous HTTP call to Service B while keeping the transaction open. Service B attempts an UPDATE on that same database row and blocks. Service A is waiting for Service B’s HTTP socket to return, while Service B is waiting for Service A’s database lock to release. Because relational database deadlock detection algorithms only inspect local engine locks—and have no visibility into external network sockets—the deadlock is never broken automatically, tying up thread pools until network timeouts expire.

2. Why doesn’t Schema per Service on a single PostgreSQL instance protect against runtime production failures?

Answer: While separating tables into logical schemas (orders_schema, users_schema) resolves DDL migration collisions, it provides zero physical hardware isolation:

  1. Shared CPU & Memory: A runaway analytical query or heavy sequential scan in one schema saturates 100% of host CPU cores, starving transactional queries in all other schemas.
  2. Shared Buffer Pool (RAM): A heavy batch scan in one schema flushes hot, cached index pages belonging to mission-critical authentication schemas out of PostgreSQL shared_buffers.
  3. Shared Write-Ahead Log (WAL): A massive write burst in one schema saturates the physical disk write channel for the database WAL, throttling commit throughput for every service on the instance.

3. How should Flyway/Liquibase migrations be structured if a team is temporarily forced to live with a shared database?

Answer:

  1. Custom Migration History Tables: Each microservice must configure a distinct, isolated migration metadata table to prevent lock contention: 
    spring:
  flyway:
    table: flyway_schema_history_orders # Dedicated table instead of default
  2. The Expand-Contract (Parallel Run) Pattern: Schema modifications must be strictly backward compatible over two separate release cycles:
    • Phase 1 (Expand): Add the new column (full_name) alongside the legacy column (name). Applications write to both columns.
    • Phase 2 (Contract): Once all microservices have been upgraded to read from full_name, a subsequent release drops the legacy name column.

4. What should you do if two microservices constantly require synchronous transactional validation of each other’s data?

Answer: If two data domains are so tightly coupled that they cannot operate without synchronous cross-database transactional checks, it is a definitive sign of improperly partitioned Domain-Driven Design (DDD) Bounded Contexts. Architectural Remediations:

  1. Merge the Microservices: Re-evaluate domain boundaries and merge the two microservices back into a single deployable service. Consolidating over-fragmented microservices restores single-database ACID transactions and eliminates distributed failure modes.
  2. Adopt the Saga Pattern: If business independence must be preserved, decouple the synchronous dependency by transitioning to an asynchronous Saga with semantic pending locks and compensating transactions.

🎯 Interview Cheat Sheet

Must Know

  • Definition: The anti-pattern where multiple microservices connect directly to a single shared database instance.
  • The Core Danger: Creates a Distributed Monolith that inherits all the complexity of distributed systems with none of the benefits.
  • 5 Catastrophic Traps: Schema tight-coupling, connection pool starvation, lock contention/deadlocks, lack of data ownership, and single point of failure (total blast radius).
  • Rule of Thumb: Services must interact exclusively via public APIs (REST/gRPC) or events (Kafka), never through direct SQL queries.
  • Migration Blueprint: Split the database using the Strangler Fig Pattern combined with Debezium Change Data Capture (CDC).

Common Follow-up Questions

  • “Is a shared database ever acceptable?” $\to$ Only in the earliest days of a startup with 1–2 developers where domain boundaries are still being discovered.
  • “What is a Distributed Deadlock?” $\to$ A cyclic dependency spanning both network HTTP requests and database row locks, which relational engines cannot detect.
  • “How do you handle cross-service joins?” $\to$ Use API Composition at the Gateway/BFF or asynchronous event-driven denormalization via Kafka.

Red Flags (DO NOT Say)

  • ❌ “A shared database is fine as long as developers communicate schema changes in Slack.” (Informal human coordination fails during fast-paced deployments and cannot prevent runtime connection starvation).
  • ❌ “Creating SQL Views in a shared database is a clean way to share data between microservices.” (Views mask tight coupling and break silently when underlying tables are modified).
  • ❌ “Microservices should execute cross-database joins across different schemas in application code.” (Transfers massive amounts of raw data over the network and crushes JVM memory).