Sql Select 组中的第一行，但在并发环境中

我让同一应用程序的多个进程彼此同时运行.这些过程中的每一个都执行一些计算.

我有一个"测试"表，如下所示:

 id | a | b |      c
----+---+---+-------------
 1  | a | b | Helloooo!!!
 2  | a | b | Helloooo...
 3  | c | d | World!!!
 4  | c | d | World...

每个进程都需要从表"test"中检索组中的第一行(GROUP BY列"a"和"b")，以便对其执行一些计算，但是一个进程不应该能够 Select 已经被另一个进程捕获的行.例如:

 id | a | b |      c
----+---+---+-------------
 1  | a | b | Helloooo!!!                  <------ Captured by process #1 and it won't have been available until the transaction is finished by process #1.
 2  | a | b | Helloooo...                  <------ Unavailable.
 3  | c | d | World!!!                     <------ Should be captured by concurrent process #2.
 4  | c | d | World...                     <------ Unavailable.

没有行级锁的我的SQL查询:

WITH t AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY id ASC) AS rn
           FROM tests)
SELECT *
FROM t
WHERE rn = 1;

我试着这样做:

WITH groups AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY id ASC) AS rn FROM tests)
SELECT * FROM (SELECT * FROM groups WHERE rn = 1) subquery FOR UPDATE SKIP LOCKED LIMIT 1;

但这两个流程检索相同的行(事务打开而不提交):

• 进程#1.

• 进程#2.