SuperAPI AI Directory

consistency-multitenancy

General & other

View repository

Updated 10-16 09:30

About this skill

Consistency & Multi-tenancy

References: Pat Helland, "Life beyond Distributed Transactions"; Martin Kleppmann, "Designing Data-Intensive Applications"; AWS SaaS multi-tenancy whitepaper; Stripe / Slack / Notion multi-tenant practices.

Applicable Scenarios

  • Determine transaction boundaries and consistency requirements
  • Design idempotent writes, concurrent conflict handling, optimistic locking
  • Design multi-tenant fields, tenant isolation, composite unique constraints
  • Design soft delete, archiving, audit fields, and data lifecycle
  • Evaluate consistency solutions across tables, services, and databases
  • Row-Level Security (RLS) configuration
  • Tenant Per Schema vs Shared Schema selection

Core Principles

1. 把隔离边界和一致性要求显式写入 schema 与约束
   "tenant_id 字段" ≠ "多租户隔离"
   还需要:查询过滤 + 唯一约束 + 权限边界 + 测试

2. 事务不应无限扩大
   边界应等于业务不变量边界

3. 幂等和唯一性优先由数据库约束兜底
   不靠应用代码判重

4. 不在事务里做外部调用
   HTTP / 邮件 / 第三方 API 都不能放事务

5. 强一致 vs 最终一致:业务驱动
   涉及钱:强一致
   不影响财务:最终一致 + 补偿

6. 多租户三层防御:应用层 + 数据库层 + 测试层
   单层防御必定漏

7. 软删除是甜蜜陷阱
   破坏唯一约束、查询要带 WHERE deleted_at IS NULL

Transactions (ACID) Quick Reference

A - Atomicity(原子性):要么全成,要么全败
C - Consistency(一致性):事务前后业务规则一致
I - Isolation(隔离性):并发事务互不干扰
D - Durability(持久性):提交后数据不丢

Isolation Levels (PostgreSQL / MySQL)

Level Dirty Read Non-Repeatable Read Phantom Read Write Skew
Read Uncommitted ✓ ✓ ✓ ✓
Read Committed (default) ✗ ✓ ✓ ✓
Repeatable Read (PG/MySQL default variant) ✗ ✗ ✗ ✓
Serializable ✗ ✗ ✗ ✗
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
建议:
  - 默认 Read Committed 已能满足大多数业务
  - 关键金融场景用 Serializable + 重试
  - 慎用 Read Uncommitted(一般不需要)

Transaction Boundary Design

Wrong: Transaction Too Large

# 反模式:事务包含外部调用
@transaction.atomic
def create_order(user_id, items):
    order = Order.objects.create(...)
    OrderItem.objects.bulk_create(...)
    
    # 外部 HTTP 调用 → 事务持续 30 秒
    payment_result = call_payment_gateway(order)
    
    if payment_result.success:
        order.status = 'paid'
        order.save()

Right: Minimize Transactions + Saga

# 阶段 1:本地事务(仅 DB 操作)
@transaction.atomic
def create_order(user_id, items):
    order = Order.objects.create(status='pending', ...)
    OrderItem.objects.bulk_create(...)
    return order

# 阶段 2:异步外部调用
def process_payment(order_id):
    order = Order.objects.get(id=order_id)
    result = call_payment_gateway(order)
    
    # 阶段 3:本地事务更新状态
    if result.success:
        order.update(status='paid')
    else:
        order.update(status='failed')
        # 补偿动作(释放库存、退款)
        compensate(order)

Idempotency Design

Idempotency-Key Pattern (Essential for Write Endpoints)

-- 幂等性表
CREATE TABLE idempotency_keys (
  key varchar(128) PRIMARY KEY,
  request_hash varchar(64) NOT NULL,
  response_body jsonb,
  status varchar(32) NOT NULL,  -- 'processing' / 'completed' / 'failed'
  created_at timestamptz DEFAULT now(),
  expires_at timestamptz NOT NULL DEFAULT (now() + INTERVAL '24 hours')
);

CREATE INDEX idx_idempotency_expires ON idempotency_keys(expires_at);

Business Unique Constraint (Strongest Idempotency)

-- 同一订单不能支付两次
CREATE TABLE payments (
  id bigserial PRIMARY KEY,
  order_id bigint NOT NULL,
  amount_cents bigint NOT NULL,
  status varchar(32) NOT NULL,
  created_at timestamptz DEFAULT now(),
  
  -- 业务唯一:每个订单只能有一条成功的支付
  CONSTRAINT uq_order_payment UNIQUE (order_id, status)
    DEFERRABLE INITIALLY DEFERRED
);

Optimistic Locking (version field)

-- 表设计
CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  status varchar(32) NOT NULL,
  version integer NOT NULL DEFAULT 1,
  ...
);

-- 更新时校验版本
UPDATE orders
SET status = 'paid', version = version + 1
WHERE id = ? AND version = ?;

-- 影响 0 行 → 版本冲突 → 应用层重试或返回 409

Pessimistic Locking (SELECT FOR UPDATE)

-- 高冲突场景:直接锁行
BEGIN;
SELECT * FROM accounts WHERE id = ? FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = ?;
COMMIT;
何时用:
  乐观锁:冲突率低(< 5%),重试代价小
  悲观锁:冲突率高,必须串行

Three-Layer Defense for Multi-Tenant Isolation

First Layer: Application Layer

# 中间件:每个查询都带 tenant_id
class TenantMiddleware:
    def process_request(self, request):
        request.tenant_id = get_tenant_from_token(request)

# ORM 自动过滤
class TenantAwareManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(tenant_id=current_tenant_id())

Second Layer: Database Layer (RLS / Views)

-- PostgreSQL Row Level Security(强烈推荐)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::bigint);

-- 应用每次连接时设置
SET LOCAL app.current_tenant_id = '123';
SELECT * FROM orders;  -- 自动过滤

Third Layer: Testing Layer

# 跨租户越权测试用例
def test_cross_tenant_isolation():
    user_a = create_user(tenant=1)
    user_b = create_user(tenant=2)
    
    order_a = create_order(user=user_a)  # tenant 1 的订单
    
    # 用 user_b 的身份尝试访问 user_a 的订单
    response = client.get(f'/orders/{order_a.id}', as_user=user_b)
    assert response.status_code == 404  # 或 403

Multi-Tenant Data Models

Shared Schema (Most Common)

-- 所有租户共享表,靠 tenant_id 区分
CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  ...
);

-- 复合唯一(必须含 tenant_id)
CREATE UNIQUE INDEX uq_orders_number
  ON orders(tenant_id, order_number);

Advantages:

  • Simple
  • High resource utilization (a single database supports thousands of tenants)
  • One-time upgrade

Disadvantages:

  • A single tenant failure may affect everyone
  • Performance interference between large tenants and small tenants
  • Cross-tenant query risk

Suitable for: small and medium SaaS tenants, shared functionality

Schema Per Tenant (Independent Schema per Tenant)

-- 每个租户一个 schema
CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.orders (...);

CREATE SCHEMA tenant_globex;
CREATE TABLE tenant_globex.orders (...);

Advantages:

  • Stronger isolation
  • Fine-grained backup and recovery
  • Controllable performance for large tenants

Disadvantages:

  • Complex upgrades (every schema must be migrated)
  • High resource consumption
  • Complex schema switching at the application layer

Suitable for: hundreds of tenants, enterprise edition

Database Per Tenant

每个租户独立数据库实例

优势:
  - 完全隔离
  - 性能 / 备份 / 安全独立

劣势:
  - 资源消耗大
  - 运维复杂

适合:大型企业客户、合规要求强(PII / 医疗)

Uniqueness Constraint Design

Multi-Tenant Scenario

-- 错:用户邮箱全局唯一
CREATE UNIQUE INDEX uq_users_email ON users(email);
-- 不同租户不能有同名邮箱

-- 对:租户内唯一
CREATE UNIQUE INDEX uq_users_email_tenant
  ON users(tenant_id, email);

Soft Delete Scenario

-- 错:软删后无法重新注册
CREATE UNIQUE INDEX uq_users_email
  ON users(tenant_id, email);
-- 同 email 的删除用户阻止新注册

-- 对:部分索引
CREATE UNIQUE INDEX uq_users_email_active
  ON users(tenant_id, email)
  WHERE deleted_at IS NULL;

Soft Delete vs Hard Delete Decision

软删除(deleted_at):
  ✅ 用户可恢复
  ✅ 关联数据完整
  ✅ 审计需要
  ❌ 唯一约束复杂
  ❌ 查询都要带 WHERE
  ❌ 数据膨胀

硬删除:
  ✅ 简单
  ✅ 性能好
  ❌ 不可恢复
  ❌ 关联数据连锁删除

归档(删除前移到归档表):
  ✅ 主表干净
  ✅ 历史数据可查
  ❌ 跨表查询复杂

推荐:
  - 用户数据:软删除(GDPR 30 天后硬删)
  - 业务数据:软删除 + 6 个月归档
  - 临时数据(缓存 / 会话):硬删除
  - 审计日志:永不删除

Data Lifecycle

-- 推荐字段
CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  -- ... 业务字段 ...
  
  -- 生命周期字段
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,                            -- 软删
  archived_at timestamptz,                           -- 归档
  retention_until timestamptz,                       -- 保留期限
  
  -- 审计字段
  created_by bigint REFERENCES users(id),
  updated_by bigint REFERENCES users(id),
  deleted_by bigint REFERENCES users(id),
  
  -- 乐观锁
  version integer NOT NULL DEFAULT 1
);

Standard Process

1. 识别业务不变量和并发写入场景
   - 哪些状态不能同时存在
   - 哪些操作不能重复执行
   - 哪些字段必须唯一
   ↓
2. 判断强一致、最终一致或补偿机制
   - 涉及钱 → 强一致
   - 通知 / 报表 → 最终一致
   - 跨服务 → Saga
   ↓
3. 设计事务边界、锁策略、唯一约束、版本字段
   - 事务最小化
   - 数据库约束兜底
   - 乐观锁优先
   ↓
4. 设计租户字段、复合约束、默认过滤规则
   - 三层防御
   - 复合唯一含 tenant_id
   - RLS 兜底
   ↓
5. 设计删除、归档、审计和恢复策略
   - 软删 + 部分索引
   - 归档表
   - 审计日志
   ↓
6. 输出一致性和隔离检查清单

Multi-Tenant Checklist

Item Checkpoint
Tenant Key Does every tenant data table have an explicit tenant_id field
Query Does it filter by tenant_id by default (middleware / RLS)
Uniqueness Does the unique constraint include the tenant dimension
Backend operations Do cross-tenant operations have auditing and authorization
Testing Are there cross-tenant privilege escalation regression test cases
Backup Is single-tenant backup and restore feasible
Monitoring Are single-tenant performance / errors observable
Deletion How is data handled when a tenant is deprovisioned (GDPR)

Supporting templates

  • templates/consistency-checklist-template.md — Consistency + Multi-tenancy + Soft Delete + Auditing + RLS configuration complete checklist

Quality self-check

□ 列出业务不变量
□ 说明事务边界和失败补偿
□ 幂等键、唯一约束、版本字段明确
□ 多租户三层防御(应用 + 数据库 + 测试)
□ 唯一约束包含 tenant_id
□ 软删除有部分唯一索引
□ 删除、归档、审计、恢复策略明确
□ 事务不包含外部调用
□ 隔离级别选择有依据
□ 跨服务一致性方案(Saga / 事件)
□ RLS 配置(PG)兜底
□ 与安全工作流协作评审

Common pitfalls

  1. Has tenant_id but the unique constraint does not include tenant_id——Same email conflicts across tenants
  2. Backend management interfaces bypass tenant filtering——Unauthorized access
  3. Long transaction wrapping external calls——Lock held for 30 seconds, affecting concurrency
  4. Idempotency relies only on the front end preventing duplicate clicks——Backend is unprotected
  5. After soft delete, the unique constraint conflicts with the restore logic——Cannot register again
  6. Optimistic lock version is not incremented——Update succeeds but concurrency cannot be detected
  7. Isolation level set to Read Uncommitted——Reads uncommitted data
  8. Use distributed transactions for strong consistency across tables——2PC has poor performance; Saga should be used
  9. Multi-tenant shared tables have no RLS fallback——One leak in the application layer leaks everything
  10. Audit logs in the main table——Main table bloats, auditing is polluted
  11. Deletion cascades to all related tables——Deleting one user affects 50 tables
  12. Retention period is unclear——GDPR violation
  13. No solution for cross-tenant backup and restore——A single-tenant issue affects everyone
  14. Tests do not cover cross-tenant privilege escalation——Only discovered after launch

Collaboration with other skills

上游:
  schema-design → 提供表结构基础
  api-designer → 提供权限边界

下游:
  index-access-pattern → 设计租户维度索引
  data-operations-safety → 评估跨租户操作和数据修复风险
  security-engineer → 评审敏感数据和权限边界
  backend-engineer → 实现租户中间件
  qa-engineer → 跨租户越权回归用例

Related references

  • references/consistency-multitenancy-guide.md — In-depth ACID, Saga pattern, comparison of three SaaS multi-tenant architectures, RLS in practice, the truth about PostgreSQL isolation levels, real cases from Slack / Notion