upsert 源码深读MySQL 存储过程如何靠异常处理器实现并发安全的 Upsert【免费下载链接】upsertUpsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE.项目地址: https://gitcode.com/gh_mirrors/ups/upsertUpsert 是一款 Ruby 数据库工具库支持在 MySQL、PostgreSQL 和 SQLite3 中实现存在即更新不存在则插入的合并写入Upsert。本文以 upsert 的源码为例带你深读它生成的 MySQL 存储过程看它如何仅靠两个异常处理器 一个重试循环就写出了一个并发安全的 Upsert 实现。一、先搞懂 Upsert一个写法的两种命运在数据库里更新一行通常是两步操作先查这行存在吗再决定存在就UPDATE不存在就INSERT。但问题出在并发上——两个连接同时查完、发现都不存在、双双执行INSERT后到的那个就会被唯一键拒之门外抛出一个1062 错误ER_DUP_ENTRY即重复键。 所以 Upsert 的本质难题不是怎么写 SQL而是怎么在竞争环境下优雅地接住冲突而不是崩溃。upsert 库Gemfile 中可安装的用法非常简洁Upsert.upsert(table).row({selector: 值}, {setter: 值})一行搞定剩下的脏活累活由它替你完成。二、为什么放弃 ON DUPLICATE KEY UPDATEMySQL 官方提供了INSERT ... ON DUPLICATE KEY UPDATE看起来天生就是 Upsert。但 upsert 库从 1.0.0 版本起就主动放弃了它理由写在 CHANGELOG 里MySQL upserts wont fail if you have a multi-key selector and no multi-column UNIQUE index to cover them也就是说当你的匹配条件是多列组合、而表上并没有覆盖这些列的联合唯一索引时ON DUPLICATE KEY UPDATE会误判为新行而重复插入。为了正确性作者选择自己造一个真正的合并函数——MySQL 上就是一个存储过程Stored Procedure。三、源码导读存储过程是怎么被造出来的3.1 命名让每个表 条件 字段组合都有专属函数合并函数的生成逻辑在 lib/upsert/merge_function.rb 中。它会按如下规则拼出函数名组成部分作用NAME_PREFIX固定前缀upsert2_9_10取自 lib/upsert/version.rb 的版本号点号换下划线表名非法字符统一替换为_SEL 选择列用SEL标记匹配条件列SET 设置列用SET标记要写入的列如果拼出来超过 62 个字符MAX_NAME_LENGTH就用 CRC32 校验值截断保证函数名合法且几乎不冲突。同一张表、不同条件组合会生成不同的函数互不干扰。3.2 核心文件一行行看懂 CREATE PROCEDURE整个 MySQL 版存储过程的模板都在 lib/upsert/merge_function/mysql.rb 的create!方法里。它先把旧的同名过程删掉再按模板动态拼出参数selector 列 setter 列和 SQL最终生成类似这样的结构CREATE PROCEDURE upsert2_9_10_users_SEL_email_SET_name( p_email VARCHAR(255), p_name VARCHAR(255) ) BEGIN DECLARE done BOOLEAN; REPEAT BEGIN DECLARE ER_DUP_UNIQUE CONDITION FOR 23000; -- SQLSTATE 大类完整性约束 DECLARE ER_INTEG CONDITION FOR 1062; -- 具体错误码重复键 DECLARE CONTINUE HANDLER FOR ER_DUP_UNIQUE BEGIN SET done FALSE; -- 有人抢先插入了标记未完成 END; DECLARE CONTINUE HANDLER FOR ER_INTEG BEGIN SET done TRUE; -- 走到这里说明插入成功 END; SET done TRUE; SELECT COUNT(*) INTO count FROM users WHERE email p_email; IF count 0 THEN UPDATE users SET name p_name WHERE email p_email; ELSE INSERT INTO users (name, email) VALUES (p_name, p_email); END IF; END; UNTIL done END REPEAT; END三个值得注意的细节done哨兵变量它不是循环计数而是整个并发防御机制的开关。操作符来自 lib/upsert/column_definition/mysql.rb 的equality方法NULL 安全的等号比较条件列为 NULL 时也能正确匹配。CONTINUE而非EXIT处理器捕获异常后不跳出而是让执行继续往下走把是否重试的决定权交给外层REPEAT ... UNTIL done循环。四、并发安全的关键竞态窗口如何被接住源码里有一行非常诚实的注释把整个设计点破了-- Race condition here. If a concurrent INSERT is made after the SELECT but before the INSERT below well get a duplicate key error. But the handler above will take care of that.也就是说作者不回避竞态而是承认它的存在然后用异常处理器去兜底。完整时序如下连接 A执行SELECT COUNT(*)发现 0 行走INSERT分支连接 B几乎同时执行了同样的SELECT也发现 0 行也准备INSERTA 先插成功B 的INSERT撞上唯一键抛出1062B 的ER_DUP_UNIQUEFOR 23000捕获的是 SQLSTATE 完整性约束大类比只捕 1062 更保险处理器触发done FALSEREPEAT循环发现done为 FALSE回到循环开头再来一遍——这次SELECT COUNT(*)一定能查到 A 插进去的那行于是走UPDATE分支写入存在即更新的语义圆满达成 ✅异常触发时机处理器动作含义ER_DUP_UNIQUE(23000)INSERT 撞上唯一键并发竞争done FALSE→ 重试循环有人抢先了改走 UPDATEER_INTEG(1062)其他完整性错误done TRUE→ 结束循环按失败处理不再空转这就是全文最精髓的设计把检查-再执行check-then-act的竞态窗口转换成先执行、失败了再补偿的异常驱动模型。REPEAT ... UNTIL在这里不是普通的循环而是数据库层面的乐观锁重试。五、最后一道防线函数不见了自动重建存储过程毕竟是动态创建的DBA 清理、升级、切换数据库时都可能导致它消失。upsert 在调用侧也埋了一个异常处理器——只不过这次是在 Ruby 层见 lib/upsert/merge_function/Mysql2_Client.rbrescue Mysql2::Error e if e.message ~ /PROCEDURE.*does not exist/i if first_try create! # 重建存储过程 retry # 重试一次 else raise e # 第二次还失败说明真有问题不再纠缠 endfirst_try标志保证最多只重建重试一次避免函数创建又立刻丢失这类环境故障导致无限循环。JDBCJRuby版本 lib/upsert/merge_function/Java_ComMysqlJdbc_JDBC4Connection.rb 有同样的兜底逻辑只是捕获的异常类型换成了MySQLSyntaxErrorException。顺带一提清理函数也有专门入口lib/upsert/merge_function/mysql.rb 里的ClassMethods#clear会先SHOW PROCEDURE STATUS找出所有upsert2_9_10前缀的过程再逐一DROP PROCEDURE IF EXISTS不会误伤你自己写的存储过程。六、小结这套源码教会我们的 3 件事承认竞态而非假装没有在查-再-写之间永远存在窗口期与其加全局锁不如设计好失败后的补偿路径。异常处理器是控制流的一部分CONTINUE HANDLER 重试循环是存储过程里实现乐观并发控制的经典写法比锁更轻量。动态 DDL 要有自愈能力数据库函数/过程这类隐形依赖调用侧最好带上不存在就重建的兜底。想继续对比的话可以看看同目录下 lib/upsert/merge_function/postgresql.rbPostgreSQL 版合并函数PG 9.5 会直接降级为原生的INSERT ... ON CONFLICT和 lib/upsert/merge_function/sqlite3.rbSQLite3 版直接使用INSERT OR IGNORE语义三套方言、同一种并发安全的哲学对照阅读收获更大。【免费下载链接】upsertUpsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE.项目地址: https://gitcode.com/gh_mirrors/ups/upsert创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考