How can I exclude reading rows that were selected in the first transaction when executing the second transaction in PostgreSQL?

Question

I have "user" table with 3 columns. This table has 2 records with value

user_name	user_age	user_address
Kelvin	18	New York
Thomas	21	Paris

The first transaction

BEGIN ;
    SELECT u from "user" u where  u.user_age > 10 limit 1 for update ;
    SELECT pg_sleep(15);
END;

the second transaction start after the #1 transaction about 1s.

BEGIN ;
    SELECT u from "user" u where  u.user_age > 10 limit 1 for update ;
END;

I expect that the first transaction will return the first row (Kevin, 18, New York). And the second transaction will return the second row (Thomas, 21, Paris).

But when I execute the 2 transactions above in two separate tabs of DBeaver. Both of the transaction return the first row.

What did I do wrong? And how can I achieve my expectation?

Answer 1

Well, your query is poorly formed in the first place because you don't supply an ORDER BY so there's no guarantee which one you will get in either process.

What you should see if you did have an ordering though is that the second process hangs, waiting until the first commits or rolls back.

What you want is something along the lines of

SELECT ... FOR UPDATE SKIP LOCKED

However, you may want to use FOR NO KEY UPDATE (assuming you aren't updating keys) because that may well be a lighter lock to take (particularly if you have foreign keys pointing at theis table AIUI).

Answer 2

Your expectation is wrong - as explained in other answer.

The explanation why do you read the same row in two connections though locked with for update is most probably in using the Auto-Commit mode of DBeaver (that immediately after the query releases the lock).

You'll have to switch to Manual Commit mode to see that the second session is waiting.