mysql - 在通用 SQL 中同时检索(选择)或创建(插入)新行而不会发生冲突

Question

我有一个系统，它有一个复杂的主键用于与外部系统交互，还有一个快速、小的不透明主键供内部使用。例如:外部键可能是一个复合值 - 类似于(给定名称 (varchar)、姓氏 (varchar)、邮政编码 (char))，而内部键可能是一个整数(“客户 ID”)。

当我收到带有外部 key 的传入请求时，我需要查找内部 key - 这是棘手的部分 - 如果我还没有给定外部 ID 的内部 key ，则分配一个新的内部 key 。

显然，如果我一次只有一个客户端与数据库对话，这很好。 SELECT customer_id FROM customers WHERE given_name = 'foo' AND ... ，然后 INSERT INTO customers VALUES (...)如果我没有找到值。但是，如果可能同时有许多来自外部系统的请求，并且许多请求可能同时到达一个以前闻所未闻的客户，那么就会出现竞争条件，多个客户端可能会尝试 INSERT新行。

如果我要修改现有行，那会很容易；只是 SELECT FOR UPDATE首先，在执行 UPDATE 之前获取适当的行级锁.但在这种情况下，我没有可以锁定的行，因为该行尚不存在!

到目前为止，我已经提出了几个解决方案，但每个解决方案都有一些非常重要的问题:

在 INSERT 上捕获错误，从顶部重新尝试整个事务。如果交易涉及十几个客户，这是一个问题，特别是如果传入的数据可能每次都以不同的顺序谈论相同的客户。有可能陷入相互递归的死锁循环中，每次冲突都发生在不同的客户身上。您可以通过重试尝试之间的指数等待时间来缓解这种情况，但这是一种处理冲突的缓慢且昂贵的方法。此外，这使应用程序代码相当复杂，因为一切都需要重新启动。

使用保存点。在 SELECT 之前启动一个保存点, 在 INSERT 上捕获错误，然后回滚到保存点和 SELECT再次。保存点不是完全可移植的，它们的语义和功能在数据库之间略有不同；我注意到的最大区别是，有时它们似乎会嵌套，有时它们不会嵌套，所以如果我能避开它们就好了。不过，这只是一个模糊的印象——它不准确吗？保存点是否标准化，或者至少实际上是一致的？此外，保存点使在同一事务上并行执行操作变得困难，因为您可能无法确切知道将回滚多少工作，尽管我意识到我可能只需要忍受它。

获取一些全局锁，例如使用 LOCK 语句 ( oracle mysql postgres ) 的表级锁。这显然会减慢这些操作的速度并导致大量的锁争用，所以我宁愿避免它。

获取更细粒度但特定于数据库的锁。我只熟悉Postgres's way of doing this ，这在其他数据库中绝对不受支持(这些函数甚至以“pg_ ”开头)，因此这又是一个可移植性问题。此外，postgres 这样做的方式需要我以某种方式将 key 转换为一对整数，它可能无法完全适应。有没有更好的方法来获取假想对象的锁？

在我看来，这一定是数据库常见的并发问题，但我还没有找到很多关于它的资源；可能只是因为我不知道规范的措辞。在任何标记的数据库中，是否可以使用一些简单的额外语法来做到这一点？

Answer 1

我不清楚为什么不能使用 INSERT IGNORE，它可以正常运行并且您可以检查是否发生了插入(修改的记录)。如果插入“失败”，那么您知道该键已经存在并且您可以执行 SELECT。您可以先执行 INSERT，然后执行 SELECT。

或者，如果您使用 MySQL，请使用支持事务的 InnoDB。这将使回滚更容易。