sql - 锁定选定的行，以便其他事务在读取时忽略它们

Question

这个问题是关于优化的。所讨论的场景如下:

我有一个表，其中包含多个事务应读取和执行的数据。该表由三列组成:{id, message, status}。

ID   MESSAGE          STATUS
1    First Message    NEW 
2    Second Message   MOD
3    Third Message    FIN
....

如图所示，状态是以下三种状态之一:{new, mod, fin}。它们指示一行是新行、此时正在处理还是已成功处理。我这样做是为了让多个事务在这张表上工作。事务读取所有“NEW”消息并将状态更改为“MOD”，以指示它们正在处理并锁定它们。之后，它们被处理。成功处理的行将设置为“FIN”，失败的行将设置为“NEW”，以便下一个事务将再次尝试这些行。

除了状态改变之外，该表是只读的。要处理的实际信息永远不会改变。

上述过程有效，但我认为它不必要地复杂。我在这里寻找的是一个更简单的解决方案。我已经阅读过不同的隔离级别，但它们基本上都会导致行以某种方式被锁定，以便其他事务必须等待。我不想要那个。相反，我希望其他查询忽略那些已经读取的行。

最后，回到问题:是否可以在读取时锁定行，以便它们将被其他事务忽略，而不是等待这些行被解锁的事务？

我想要这样的东西:假设该表包含 30 行。

• 事务“reader1”读取 30 行，(id 1-30)。这些行现在已锁定(最好对其他人不可见)。
• 事务“writer”插入 20 个新行。 (编号 31-50)
• 此时，“reader1”还没有完成。不必等待“reader1”完成，事务“reader2”只读取新的 20 行，因为其他行 (1-30) 被“reader1”隐藏了
• “reader1”和“reader2”完成。它们完成的顺序无关紧要，因为它们处理的是分离的数据集。

有没有办法在不需要状态栏的情况下做到这一点？

---

• 使用的 DBMS 是 IBM 的 DB2
• 通过 ODBC 访问

Answer 1

Is there a way to do this without the need of a status column?

简而言之，不是，但不是因为锁定/并行问题。

实际上，您只需要该状态列，以便当您的处理系统最终出现故障时，您知道在它恢复正常时需要再次处理哪些行。它实际上使并行工作变得更容易，这是一个附带的好处。

至于你的问题的其余部分:

I've already read about different isolation levels but they all basically cause the rows to be lock in a way so that other transactions have to wait. I dont want that. Instead I would like those already read rows to be ignored by the other queries.

...好吧，你很幸运，因为无论如何你都不想发生这种情况。您的主要问题是，为了锁定具有仅 读取的行，您不仅必须防止其他进程读取这些行(因为只读锁通常是共享的)，还必须防止行也不会被插入。但是，如果相反，您使用写入 锁定一行，交错进程将变得非常简单和容易。

你只需要 Cursor Stability :

However, if any data in the row was changed, the lock is held until the change is committed.

Under this isolation level, no other application can update or delete a row while an updatable cursor is positioned on that row. Under CS, access to the uncommitted data of other applications is not possible. However, non-repeatable reads and phantom reads are possible.

...允许其他处理器更新相似数据是不可重复读取的意义所在。我们实际上希望它们发生!我们不希望幻读(如果两行读取相同的数据然后尝试更新它就会发生这种情况)，但我们可以围绕它设计我们的应用程序。

执行此操作的一种方法是启动您的工作单元(这可能是隐式的或显式的，具体取决于平台)，然后发出更新语句:

UPDATE Transactions SET status = 'MOD'
WHERE status = 'NEW'

然后只需检索更新的行，这样您就知道要处理什么:

SELECT id, message
FROM Transactions
WHERE status = 'MOD'

将它们标记为已完成:

UPDATE Transaction SET status = 'FIN'
WHERE status = 'MOD'

最后提交更改:

COMMIT

(请注意，此版本有一个小弱点 - 如果系统在更新到 'FIN' 之后但在提交之前出现故障，该行将重置为 'NEW'，这可能是它自己的问题。但至少 'MOD' 会正确回滚)