在Postgresql中选择未锁定的行 [英] Select unlocked row in Postgresql
问题描述
有没有办法在Postgresql中选择未锁定的行?我有一个多线程应用程序可以做到:
Is there a way to select rows in Postgresql that aren't locked? I have a multi-threaded app that will do:
Select... order by id desc limit 1 for update
在桌子上.
如果多个线程运行此查询，它们都将尝试拉回同一行.
If multiple threads run this query, they both try to pull back the same row.
一个获取行锁，其他阻塞，然后在第一个更新行后失败.我真正想要的是第二个线程获取与WHERE子句匹配且尚未锁定的第一行.
One gets the row lock, the other blocks and then fails after the first one updates the row. What I'd really like is for the second thread to get the first row that matches the WHERE clause and isn't already locked.
为澄清起见，我希望每个线程在执行选择后立即更新第一个可用行.
To clarify, I want each thread to immediately update the first available row after doing the select.
因此，如果存在带有ID: 1,2,3,4的行，则第一个线程将进入，选择带有ID=4的行并立即对其进行更新.
So if there are rows with ID: 1,2,3,4 , the first thread would come in, select the row with ID=4 and immediately update it.
如果在该事务期间出现第二个线程，我希望它获得ID=3行并立即更新该行.
If during that transaction a second thread comes it, I'd like it to get row with ID=3 and immediately update that row.
For Share无法完成此操作，也不能使用nowait来完成，因为WHERE子句将与锁定的行(ID=4 in my example)相匹配.基本上，我想要的是WHERE子句中的"AND NOT LOCKED".
For Share won't accomplish this nor with nowait as the WHERE clause will match the locked row (ID=4 in my example). Basically what I'd like is something like "AND NOT LOCKED" in the WHERE clause.
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抓行.
If the query is "Select ID from users where flags = 0 order by ID desc limit 1" and when a row is returned the next thing is "Update Users set flags = 1 where ID = 0" then I'd like the first thread in to grab the row with ID 4 and the next one in to grab the row with ID 3.
如果我将"For Update"附加到选择中，则第一个线程获取行，第二个线程阻塞，然后不返回任何内容，因为一旦第一个事务提交，便不再满足WHERE子句.
If I append "For Update" to the select then the first thread gets the row, the second one blocks and then returns nothing because once the first transaction commits the WHERE clause is no longer satisfied.
如果我不使用"For Update"，那么我需要在后续更新中添加WHERE子句(WHERE标志= 0)，因此只有一个线程可以更新该行.
If I don't use "For Update" then I need to add a WHERE clause on the subsequent update (WHERE flags = 0) so only one thread can update the row.
第二个线程将选择与第一个相同的行，但是第二个线程的更新将失败.
The second thread will select the same row as the first but the second thread's update will fail.
无论哪种方式，第二个线程都无法获取行并更新，因为我无法使数据库将事务重叠的第4行分配给第一个线程，将第3行分配给第二个线程.
Either way the second thread fails to get a row and update because I can't get the database to give row 4 to the first thread and row 3 to the second thread the the transactions overlap.
推荐答案
此功能SELECT ... SKIP LOCKED已在Postgres 9.5中实现. http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/
This feature, SELECT ... SKIP LOCKED is being implemented in Postgres 9.5. http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/
这篇关于在Postgresql中选择未锁定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
