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