Postgres: order of `FOR UPDATE OF table` retrieval

Question

I'm wondering about data consistency when relying on FOR UPDATE OF <table> in a JOIN. I'll try to make a simple example:

Transaction 1 does:

BEGIN;
SELECT 1 FROM a WHERE a.id = 5 FOR UPDATE;
UPDATE b SET val = 'other' WHERE b.a_id = 5;
COMMIT;

Transaction 2 does this very closely after:

BEGIN;
SELECT b.val FROM a, b WHERE a.id = 5 AND b.a_id = 5 FOR UPDATE OF a;
[...]

Is there a chance that transaction 2 acquires the lock, but still gets an outdated b.val? The lock is on the row from a -- does it wait on that lock before retrieving the row from b?

Because of there is no lock in second transaction for table `b`, yes, there is chance you get outdated `b.val`. — Oto Shavadze, Aug 13 '18 at 18:40

score 0 · Answer 1 · answered Aug 15 '18 at 17:26

The comments on the question confirm my suspicion -- the lock doesn't delay retrieval of other rows, from other tables. This is a pretty specific scenario, but I welcome informative answers showing easy ways to accomplish this. I ended up splitting the 2nd SELECT out into two queries -- the first SELECT obtains lock, and then a 2nd SELECT retrieves b rows, ensuring they are updated.

Postgres: order of `FOR UPDATE OF table` retrieval

1 Answers1