在Postgresql中选择未锁定的行

Question

在Postgresql中选择未锁定的行

50

有没有一种方法可以在PostgreSQL中选择未锁定的行？我有一个多线程应用程序将执行以下操作：

Select... order by id desc limit 1 for update

在一个表上执行此查询时，如果有多个线程运行，则它们都尝试获取同一行。

其中一个获取了行锁，另一个被阻塞，然后在第一个更新该行后失败。我真正想要的是第二个线程获取第一个匹配WHERE子句且尚未锁定的行。

澄清一下，我希望每个线程在执行选择操作后立即更新第一行可用行。

因此，如果有ID为1、2、3、4的行，则第一个线程将进入，选择具有ID=4的行并立即更新它。

如果在该事务期间出现第二个线程，则我希望它获取具有ID=3的行并立即更新该行。

由于WHERE子句会匹配已锁定的行(例如我的示例中的ID = 4)，因此使用Share或nowait将无法实现这一点。基本上，我想要的是在WHERE子句中添加"AND NOT LOCKED"之类的内容。

Users

-----------------------------------------
ID        | Name       |      flags
-----------------------------------------
1         |  bob       |        0
2         |  fred      |        1
3         |  tom       |        0
4         |  ed        |        0

如果查询是 "Select ID from users where flags = 0 order by ID desc limit 1"，当返回一行时，下一步是 "Update Users set flags = 1 where ID = 0"，我希望第一个线程获取ID 4的行，下一个线程获取ID 3的行。

如果在选择语句中添加 "For Update"，那么第一个线程将获得该行，第二个线程将被阻塞，然后返回空值，因为一旦第一个事务提交，WHERE子句就不再满足条件。

如果我不使用 "For Update"，那么我需要在后续的更新操作中添加 WHERE 子句(WHERE flags = 0)，以便只有一个线程可以更新这行。

第二个线程将选择与第一个线程相同的行，但第二个线程的更新将失败。

无论哪种方式，第二个线程都无法获得一行并更新，因为我无法让数据库给第一个线程行4，给第二个线程行3，因为这些事务是重叠的。

- idontwanttortfm

14个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- idontwanttortfm · Answer 1

由于我还没有找到更好的答案，所以我决定在我的应用程序中使用锁定来同步访问执行此查询的代码。

- woeye · Answer 2

I faced the same problem in our application and came up with a solution that is very similar to Grant Johnson's approach. A FIFO or LIFO pipe was not an option because we have a cluster of application servers accessing one DB. What we do is a

SELECT ... WHERE FLAG=0 ... FOR UPDATE

immediately followed by a

UPDATE ... SET FLAG=1 WHERE ID=:id

as soon as possible in order to keep the lock time as low as possible. Depending on the table column count and sizes it might help to only fetch the ID in the first select and once you've marked the row to fetch the remaining data. A stored procedure can reduce the amount of round-trips even more.

- HUAGHAGUAH · Answer 3

你想要达成什么目标？你能更好地解释一下为什么既不是未锁定行更新也不是完整事务会实现你想要的功能吗？

更好的做法是，你可以避免争用，只需让每个线程使用不同的偏移量即可。如果表格的相关部分经常被更新，这种做法可能效果不佳；在高插入负载期间，仍然会发生冲突。

Select... order by id desc offset THREAD_NUMBER limit 1 for update

- HUAGHAGUAH · Answer 4

以下怎么样？它可能比其他示例更原子化，但仍应进行测试以确保我的假设不错。

UPDATE users SET flags = 1 WHERE id = ( SELECT id FROM users WHERE flags = 0 ORDER BY id DESC LIMIT 1 ) RETURNING ...;

你可能仍然会被卡在PostgreSQL内部使用的锁定方案中，以在同时进行UPDATE时提供一致的SELECT结果。