I have multiple processes of the same application running at the same time as each other. Each of these processes performs some computations.
I have a "tests" table that looks like this:
id | a | b | c
----+---+---+-------------
1 | a | b | Helloooo!!!
2 | a | b | Helloooo...
3 | c | d | World!!!
4 | c | d | World...
Each process needs to retrieve the first row in a group (group by columns "a" and "b") from the table "tests" to perform some computations over it but one process shouldn’t be able to select the row that has been already captured by another process. For example:
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.
My SQL query without row-level locks:
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;
I tried to do something like this:
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;
But both of the processes retrieves the same row (transactions are opened and aren’t committed):
- Process #1.
- Process #2.
>Solution :
It might not be clear to the planner which rows of which actual table table you’re trying to lock.
This seems to work:
BEGIN TRANSACTION;
WITH groups AS (
SELECT id,
ROW_NUMBER() OVER w1 AS rn
FROM tests
window w1 as (PARTITION BY a, b ORDER BY id ASC))
SELECT *
FROM tests inner join groups using (id) WHERE groups.rn = 1
FOR UPDATE SKIP LOCKED LIMIT 1;
That being said, I’m still trying to work out what exactly in your initial query prevented the expected locking behaviour.