sql - 使用标准 SQL 高效检测并发插入

Question

要求

我有下表(伪DDL):

CREATE TABLE MESSAGE (
    MESSAGE_GUID GUID PRIMARY KEY,
    INSERT_TIME DATETIME
)

CREATE INDEX MESSAGE_IE1 ON MESSAGE (INSERT_TIME);

多个客户端同时向该表中插入行，可能每秒多次。我需要设计一个“监控器”应用程序，它将:

1. 首先，获取表中当前的所有行。
2. 之后，定期检查是否有新插入的行，然后获取这些行仅。

可能有多个 Monitors 同时运行。所有 Monitors 都需要查看所有行(即当插入一行时，它必须被所有当前运行的 Monitors“检测到”)。

此应用程序最初是为 Oracle 开发的，但我们需要使其可移植到每个主要的 RDBMS，并希望尽可能避免特定于数据库的内容。

问题

天真的解决方案是简单地找到在第 1 步中选择的行中的最大 INSERT_TIME，然后...

SELECT * FROM MESSAGE WHERE INSERT_TIME >= :max_insert_time_from_previous_select

...在第 2 步中。

但是，我担心这可能会导致竞争条件。考虑以下场景:

1. 事务 A 插入一个新行但尚未提交。
2. 事务 B 插入一个新行并提交。
3. Monitor 选择行并查看最大 INSERT_TIME是B插入的。
4. 事务 A 提交。此时A的INSERT_TIME其实就是早于 B 的(A 的 INSERT 实际上执行之前B 的，在我们甚至不知道谁将首先提交之前)。
5. Monitor 选择比 B 的 INSERT_TIME 更新的行(作为步骤 3 的结果)。由于 A 的 INSERT_TIME 早于 B 的插入时间，因此跳过 A 的行。

因此，永远不会获取事务 A 插入的行。

有什么想法可以设计客户端 SQL 甚至更改数据库架构(只要它具有适度的可移植性)，从而避免此类并发问题，同时仍保持良好的性能？

谢谢。

Answer 1

在不使用任何特定于平台的变更数据捕获 (CDC) 技术的情况下，有几种方法。

选项 1

每个 Monitor 都会注册一种对 MESSAGE 表的订阅。写入消息的代码然后为每个 Monitor 写入每个 MESSAGE 一次，即

CREATE TABLE message_subscription (
  message_subscription_id NUMBER PRIMARY KEY,
  message_id RAW(32) NOT NULLL,
  monitor_id NUMBER NOT NULL,
  CONSTRAINT uk_message_sub UNIQUE (message_id, monitor_id)
);

INSERT INTO message_subscription
  SELECT message_subscription_seq.nextval,
         sys_guid,
         monitor_id
    FROM monitor_subscribers;

每个 Monitor 然后会在处理完消息后从其订阅中删除该消息。

选项 2

每个 Monitor 都维护着它处理过的最近消息的缓存，该缓存至少与运行时间最长的事务一样长。例如，如果 Monitor 维护了它在过去 5 分钟内处理过的消息的缓存，它会在你的 MESSAGE 表中查询所有晚于它的 的消息LAST_MONITOR_TIME。然后，Monitor 将负责记录它选择的一些行已经被处理过。 Monitor 只会处理不在其缓存中的 MESSAGE_ID 值。

选项 3

就像选项 1 一样，您为每个 Monitor 设置订阅，但您使用某种排队技术将消息传送到 Monitor。这不如其他两个选项可移植，但大多数数据库可以通过某种队列将消息传递给应用程序(即，如果您的 Monitor 是 Java 应用程序，则为 JMS 队列)。这避免了您通过构建自己的队列表来重新发明轮子，并在应用程序层中为您提供了一个标准接口(interface)来进行编码。