跳转到内容

第 3 章 数据库、事务、锁与数据模型

来自《系统架构与工程化学习》 · 原稿更新 2026-11-09 · 服务器只读阅读视图

3.1 数据归属与持久化

建表前先确定唯一身份、数据归属、关联关系和权威事实源;字段格式只解决怎么存。

我踩过的坑

  • 给聊天消息的正文用了限定长度的 VARCHAR(n),没想清长文本与时间字段的边界。
  • 被问到会话、消息、工具调用的表关联时,只回答“字段用 JSON”,没说明谁属于谁。
  • 对 ORM 的理解停在“建立 Python 对象与数据库的映射”。

正确理解

  • 同一个用户从网页和微信两个入口进来,应该共享稳定的 user_id 和会话数据,而不是两套互不认识的记录。入口标识到内部主键的转换,在明确的身份边界里完成。
  • PostgreSQL 的 TEXT 适合长度不固定的消息,不指定长度的 VARCHAR 也行,要避免的是随手设一个过小的 VARCHAR(n)。
  • 跨时区的事件时间存成 TIMESTAMPTZ,由客户端按用户时区展示。它记录的是时间点,不会保存原始的地理时区名称。
  • 表关系看业务归属:一个 Session 有多条 Message,一条 Message 可以关联多次 ToolCall,用外键或可验证的业务关联键表达。长期 Memory 可能跨会话,不必都挂在 session_id 下。字段格式解决怎么存,外键和约束解决对象怎么关联。
  • ORM 还帮你复用 SQL 构造、绑定参数,降低手拼 SQL 的注入风险。但直接拼接不可信的 SQL 字符串照样会注入,用了 SQLAlchemy 不等于自动安全。
  • 模型定义和类型提示,也不能让所有字段写错都在 Python 编译阶段被发现,要靠运行时校验、数据库约束和测试兜底。

3.2 事务的边界

事务保证相关更新不能只做一半,但管不到外部 API、MQ 和其他服务。

我踩过的坑

  • 以为无事务的转账风险是“A 扣两次”。
  • 以为强相关的消息记录和工具日志不应该在同一事务里。

正确理解

  • 事务防的是 A 已经扣款、B 还没入账就异常退出,钱只转了一半。重复扣款是另一个问题,要靠业务幂等解决。
  • 同一个数据库里必须一起成功的记录,可以放进一笔事务。外部 Tool/API 调用不在本地事务里,不能为了等它完成长时间锁着数据库;跨服务要用状态机、幂等、Outbox 或补偿。
  • 把多次跨服务操作包成一个聚合 RPC,不会自动变成全局事务。服务拥有者提供一个业务语义清楚的入口,能减少调用方误用;但只有同一数据库里的部分能一起提交,内部再调其他服务、发消息或拿外部锁,仍然要幂等、状态机、Outbox/Saga 或补偿。
  • 文件系统与状态数据库分别更新可能只成功一半;例如 Experience 晋升 Skill,要用事务、补偿或对账。历史记忆 GC 从 63→11 个文件只是数量变化,质量取决于新摘要校验、旧资料备份和恢复能力。
  • 历史资料里“一次 RPC 就保证释放会话、预约、设备锁全部原子完成”,是没有成立的推断。

隔离级别与 MVCC(AI 补充,待学习)

  • 原子性管“全做或全不做”,隔离级别管“并发事务能看到彼此多少”,是两个问题。
  • 从弱到强:读未提交 → 读已提交 → 可重复读 → 串行化。越强越不容易读到异常,冲突和等待的代价也越高。
  • 三种典型现象:脏读读到别人没提交的数据;不可重复读同一行两次读到不同值;幻读同一条件两次查到的行数不同。
  • 默认级别不同:PostgreSQL 是读已提交,MySQL InnoDB 是可重复读。同名级别的具体行为也有差异,要查所用数据库的文档。
  • MVCC(多版本并发控制):更新时保留旧版本,读操作按自己的快照读对应版本,所以读写通常不用互相等。
  • MVCC 不能替代写冲突处理。两个事务都读到余额 100、再各自写回,仍可能丢失更新;要用 SELECT ... FOR UPDATE、版本号或更高的隔离级别,见第 3.3 节。
来源

PostgreSQL 事务隔离 · MySQL InnoDB 隔离级别。

3.3 锁与幂等的分工

并发写冲突、重复执行、多个 Worker 抢同一任务、事务做一半,是四个不同问题。

图解:四种问题,不用同一种锁解决

四种问题,不用同一种锁解决(概念示意)

读图: 先问自己想防哪种错误,再选机制。事务不自动消除重复请求;幂等也不等于对所有并发更新加锁。

想防的问题 常用方式
同一行并发写冲突 悲观锁或乐观版本号,并设计冲突后的处理方式
同一业务请求执行两次 幂等键、唯一约束、状态校验
多个 Worker 同时处理一项任务 原子的领取操作、互斥机制或租约
一个事务只完成一半 数据库事务

我踩过的坑

  • 问过状态管理是不是像悲观锁,用来防止同时修改同一份文件。
  • 以为用 INVITE_REWARD_1_% 能精确查到编号 1 的邀请奖励,结果混进了编号 10、11。

状态管理与防重复

  • 状态管理不只是防并发修改,还要记录任务做到哪、执行过没有、失败后怎么继续。竞争写入和重复执行是两个问题;而且“检查做过没有”和“标记为已做”之间也要保证并发安全,只查一遍不能防重复。
  • 外部邮件、短信不能靠数据库回滚撤销。旧稿写“先写库占位,发邮件失败就 rollback”,可邮件发成功后数据库提交失败,重试时照样会重复发送。要按业务重要性组合使用事务内登记、Outbox、重试去重和状态核对。
  • 导入真实文件时,先核对文件里最早、最晚日期和有效行数,跟界面选的范围是否一致。同一来源用稳定 ID 防重复导入;手工记录和第三方来源之间,只能列出相似交易让人确认,不能因为金额接近就自动删除。

乐观锁

  • 乐观锁在同一条 SQL 里校验旧版本并写入新版本:UPDATE ... SET ..., version=version+1 WHERE id=? AND version=?。受影响行数为 0,说明没按预期更新,应用要重新读取、提示冲突或按规则重试,不能直接返回“更新成功”。
  • MyBatis-Plus 的 @Version 和 OptimisticLockerInnerInterceptor 只在它支持的更新方法里比较版本,手写 SQL、批量更新不一定自动安全,要在真实更新路径核对版本条件、受影响行数和冲突处理。旧稿提到给多张表批量加版本列,没有复测过,不能当作已经全面保护。
  • 更新 0 行不一定是版本冲突,记录不存在、其他筛选条件不符也会这样,有的驱动还区分“匹配行”和“实际改变的行”。所以 API 不宜一律返回 409,确认不存在时更接近 404;但额外查一次是否存在,本身也有竞态。

命名锁

  • 跨请求互斥也可以按业务键用命名锁,但要有超时和归属校验。互斥和幂等仍是两件事。
  • MySQL 命名锁是会话级,不是事务锁。旧邀请奖励/验证码实践采用 GET_LOCK(name, 0) 为相同业务键做非阻塞排他,但必须在同一 MySQL 连接检查返回值(1 成功、0 等待超时、NULL 异常),并在 finally 中用对应会话释放锁。
  • COMMIT、ROLLBACK 不会自动释放 GET_LOCK;连接池换连接、断链、跨多个独立 MySQL 实例都可能破坏原本的假设。锁不代替数据库唯一约束、幂等键和状态转换验证。
  • 拿不到锁时是否立刻拒绝,是产品策略,不要求统一返回 429。

LIKE 通配符

  • INVITE_REWARD_1_% 混进编号 10、11,原因是 LIKE 里的 _ 是单字符通配符,不是普通字符。要按数据库方言转义 _ 和 %,必要时用 ESCAPE。更稳的做法是把受邀者 ID 和奖励类型存成独立字段做等值查询。换成正则也要处理转义、范围和索引,不是通用修复。
来源

MyBatis-Plus 乐观锁插件 · 旧 Wiki 乐观锁更新影响行数为0时如何区分版本冲突与记录不存在.md · MySQL 8.4 命名锁参考

批量上限尽早校验(待学习)

先检查输入数量,再拿共享锁、查库或发外部请求。回归至少测恰好到上限、超一项和重复输入,并确认超限没有占用共享操作。单批上限约束一轮的占用,多轮累计还要看限流或配额。

批量操作审查

3.4 索引、查询计划与连接池

索引按真实查询和执行计划选;系统变慢不一定慢在 SQL。

我踩过的坑

  • 以为索引加在“常见的列”上。
  • 以为连接池有 50 个用户就得设 50 条连接。
  • 系统变慢时第一反应是 SQL 太慢。
  • 用户与角色/分组的关联表用联合唯一索引,逻辑删除只改 deleted=1,旧记录仍占着唯一键,再分配同一关系就报 Duplicate entry。

正确理解

  • 索引能加快适合它的查询,也会增加写入和存储成本。慢查询先看真实 SQL、过滤条件和执行计划,再决定加索引还是调参数。
  • 索引按真实查询的 WHERE/JOIN/ORDER BY、选择性、覆盖关系和执行计划来选,不看列是否“常用”。
  • 连接池复用数据库连接,用户等待模型回答时不应一直占着数据库连接。高峰卡住也可能是长事务、锁等待和连接池排队。
  • 连接数粗略估算:占用 ≈ SQL 到达率 × 平均持有连接时间,再结合 P95/P99、数据库最大连接数和压测调整。旧笔记里“50 个用户用 10~20 条连接”只是示意,没有压测依据。
  • 关联表撞唯一键那次,当时在中间表用 @Delete 物理删除来释放关系。别的表能不能这么做,先确认审计需求、删除语义和索引设计,案例见附录 A.3.1。

索引怎么起作用(AI 补充,待学习)

  • 常见的 B+ 树索引按键排序存放,所以能快速定位、做范围查询和排序。
  • 最左前缀:联合索引 (a, b, c) 能用于查 a、a + b、a + b + c;只查 b 通常用不上。
  • 覆盖索引:查询要的列都在索引里,就不必再回表读整行。
  • 对索引列做函数运算、以通配符开头的 LIKE '%x',常常用不上索引。实际走没走,用 EXPLAIN 看。
  • 来源:MySQL 联合索引。
来源

旧 Wiki 10-wiki/personal/wiki/queries/数据库连接池大小应如何根据并发与SQL耗时确定.md

3.5 迁移与代码版本配套

涉及表结构的 ORM 改动必须执行对应迁移,实体改动与迁移一起验收。

我踩过的坑

  • 生图产品给 User 增加 has_install_reward 字段,却没同步生产表,查询触发 Unknown column,接口表现为 500/503。
  • 合并两个分支后,迁移重复添加已有列,报 Duplicate column。
  • 给已有消息表增加 Token 数量列时,我说“没有以前的数据,所以设 NULL”。

正确理解

  • 发布前核对 DDL、默认值、索引,以及新旧代码的兼容范围。遇到 Unknown column 这类报错,先核对数据库的真实结构。
  • Liquibase 可以用 preConditions 检查前置条件。只有确认数据库状态已经符合这条迁移的预期,才考虑 MARK_RAN;有差异要核对约束和迁移历史,不能把失败统统跳过。
  • 旧消息依然存在,只是旧记录没有这个字段的值。不知道旧 Token 数量,可以保留 NULL;需要 NOT NULL 时,先准备符合业务含义的回填或默认策略,不能把“未知”写成 0。
  • Alembic 迁移必须在发布流程里显式执行,或由自动化部署执行。ORM 改了,数据库不会自己跟着改。

3.6 数据库选型与备份

按查询问题、并发和运维要求选型;备份能不能恢复,要看实际副本和验证结果。

选型

  • 单机、本地工具用 SQLite;多用户并发、权限和运维要求更复杂时用 PostgreSQL/MySQL;大规模 OLAP 分析才可能需要 ClickHouse 这类列式数据库。
  • JSONB、全文索引、pgvector、独立搜索引擎按要解决的查询问题选,不要为了“先进”每种都装上。
  • 结构明确、要类型约束和频繁过滤的字段用普通列。JSONB 适合结构有弹性的内容,它也能建索引,不能一概说 JSONB 查询都比普通列慢。
  • SQLite 不是“只能存临时数据”。它能可靠保存本地长期数据,有事务和索引,选不选它看并发写入量、运维和备份要求。

事件日志与快照

  • 会话原始事件可以用 JSONL append-only 保留顺序、方便追溯。数据库的 WAL(预写日志) 是存储引擎做崩溃恢复用的,两者不是一回事。
  • 追加写也不保证断电时最多坏最后一行。缓冲没落盘、磁盘满、写到一半、多进程交错写,都可能损坏或丢失多条记录。要设计事件 ID、序列约束、确认时机和 flush/fsync 策略,恢复时能识别不完整的尾部。
  • 快照能缩短回放时间,但要和源事件版本一致。关键交易仍要靠事务和恢复验证。旧方案里的主链/sidechain 拆分只是候选设计,没有生产验收。

SQLite WAL

  • SQLite 的 WAL 模式让读写更容易共存,但仍然只有一个写者,不是能无限并发写。还要注意 busy_timeout 和 Checkpoint(把 WAL 合并回主库),长读事务或维护不当会让 WAL 文件越来越大。WAL 不是异地备份,也不代表一定更快。
  • SQLite WAL 的路径存在性不等于运行进程握着同一份文件,案例见附录 A.3.2。

备份与保留

  • 运行中备份优先用 SQLite Backup API(如 Python connection.backup()),备份后验证能打开、关键记录完整。WAL 模式下只复制正在写的主 .db 文件,可能漏掉还没 checkpoint 的已提交事务;单纯 copy2 只有在停写并正确复制 WAL 文件时才可靠。
  • 备份保留期管的是额外的备份副本,当前数据库里的数据不会因此被删。旧方案是最近 30 天每天一份、最近 12 个月每月一份:前者方便近期回退,后者留下稀疏的历史节点,几个月后才发现误删时还能找回。
  • 同步不能代替备份,一次误删会同步到所有设备。能恢复到哪些时间点,要看实际留下了哪些有效副本,并做过恢复验证。
来源

旧 Wiki SQLite-WAL模式的单写者约束与读写并发边界是什么.md · SQLite 官方 WAL 说明 · 旧库 90-归档/需要学习的.md 第 628—638 行(这是 692 行旧稿,不是辅助材料中的同名长稿)

3.7 搜索与向量检索

关键词、向量和图查询解决不同问题,先用真实查询验证现有检索是否不足。

正确理解

  • 关键词/全文搜索适合精确匹配,向量搜索适合语义相似,需要时可以混合召回,并在检索链路里做权限过滤。
  • 图查询解决多跳依赖、实体关系路径,和全文搜索是两个问题。图索引如果能从 Markdown 重建,就不该取代 Markdown 真源;先用真实查询证明现有检索不够,再考虑图数据库。
  • 存储层要算索引、查询和数据更新的成本。RAG 的召回、重排和生成见《AI Agent 学习》。
  • FTS5 外部内容表(如 content='memories'、content_rowid='id')按源表行 ID 对齐;filename 同名不代表业务唯一,案例见附录 A.3.3。
  • FTS5 中文检索效果主要看分词(Tokenize)策略,不是“必须编译 C 扩展”。SQLite 有 unicode61、trigram 等内建 Tokenizer,也可以预分词或用自定义扩展。内建的不一定够用,要拿精确术语、相邻词、中英混合查询实测。
  • 也不能简单承诺 FTS5 每次搜索都是 O(log N)、索引膨胀固定 20%~50%,这取决于词表、索引和查询方式。
  • FTS5 不会自动索引所有历史会话。旧 Agent 笔记按会话的 role/content/timestamp 建索引、中文按字预处理后再 MATCH,前提是当时真的建了表、同步规则和分词策略。历史会话能不能搜到、有没有漏掉删除或新写入的数据,要查真实索引、重建或触发器和访问权限。LIKE 可以做特殊场景的回退,但语义、排序和性能都和 MATCH 不同。
  • 增量同步时,“这次没采到”不等于“源端已删除”,权限不足、采集失败、清单没拉全都不能触发清理。正文、索引和来源范围要按同一版本更新;停用的来源恢复时,要防止旧内容被重新灌回来。
  • 过滤和 TopN 的顺序会改变结果。目标是“最终拿到 N 个合格候选”,就先过滤资格再取 TopN,或者设计过滤后的补位;如果 N 是昂贵计算的预算,顺序可以反过来。定规则前,先拿五个候选手算两种顺序。
  • 同名、同缩写只能当搜索候选,不能证明是同一个实体。要靠稳定 ID、链、类型、上下文这些可靠依据确认;证据不够就保留“未匹配”,不把推测写成确定关联。
  • 本地知识库搜索快、问答慢,要拆开测,别把模型延迟算到检索头上。某次本机单轮实测:rg 检索约 0.13 秒,search 接口约 0.44 秒,完整 LLM ask/chat 约 18.65 秒。找章节直接检索,要综合解释再付生成的成本。这是一次历史测量,不是长期指标。
来源

SQLite FTS5 外部内容表、同步触发器与重建说明 · SQLite 官方 FTS5 Tokenizers

3.8 图片向量服务迁移

接口兼容不等于向量兼容,预处理差异会改变模型输入和历史相似度排名。

2026-07-30 的项目复盘:图片向量服务切换时,补边、缩放、压缩、下载重试和代理规则都需要对照。

差异 原实现 新实现
下载内容为空 会重试 第一次为空就失败
返回信息不规范 容错下载 直接报错
图片尺寸异常 极扁长图片原本被拒绝 可能强行变成正方形
防盗链与代理 有模拟浏览器和代理池 依赖部署机代理,可能取不到同一份图片

正确理解

  • 需要复用历史向量时,由公共向量服务的兼容入口统一沿用原来的预处理规则。完全独立的新数据集合,不必套用历史规则。
  • 切换时还要核对模型版本、维度、归一化、距离度量和索引条件。要不要重算历史向量、重建索引,排名能否保持一致,当时还没有实验结论。
  • 原图与归档图的获取问题见第 8.6 节。
本章原始笔记

《数据库》

  • 案例:数据平台的 OLTP / OLAP 划分、数仓分层和存储选型(StarRocks、ClickHouse、TiDB)见数据平台。

3.9 代码回滚也要验证旧版本能读新数据(待学习)

SSO 成功后页面提示账号未启用,可能是数据库缺字段被统一包装成找不到用户;沿认证、用户查询和结构逐层查,不急着提权。复用配置不表示数据库匹配当前分支,迁移先确认目标与备份。

新增可选字段写入后,旧严格 Schema 仍可能拒读。回退先停写、备份,按明确范围处理不兼容元数据,再切代码并回读列表与详情;不能只写 revert。兼容发布如何减少清理仍待学习。

来源与当时边界

原始记录 2026-10-30 · 原始记录 2026-10-31

3.10 复合键逐字段约定大小写(待学习)

一部分标识需要归一,不代表其余字段也能转小写;录制端与跨语言消费端必须对完整键采用同一契约。用“应保留的字段有大写、另一字段需归一”的混合输入检查键与保存值,全小写样例暴露不了错误。

来源与当时边界

原始记录 复合唯一键的大小写规