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?
2
Answers
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
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).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.