Postgres, MVCC, and Locking

Question

I have a series of SQL statements that look like the following:

BEGIN;
SELECT counter FROM table WHERE id=X FOR UPDATE;
REALLY COMPLEX QUERY;
UPDATE table SET counter=Y WHERE id=X;
END;

I'd like to prevent the counter from being read while I recalc its value, but according to the Postgres docs "Row-level locks do not affect data querying; they block only writers to the same row."

Questions:

1. What's the point of an "exlusive" row lock if it doesn't prevent reads? Is it only to prevent other transactions from taking share locks?
2. If I read the row with SELECT ... FOR SHARE, does that achieve the same affect as an "exlusive" lock?
3. Is is possible to turn off MVCC for a table/schema/database and allow in-place writes?

Answer 1

to 1) Any other session will read the data modified by your transaction as it was before your "BEGIN" statement as long your transaction didn't commit. As soon your transaction committed, it will read the new value of the counter. The point being that others don't have to wait and will always see a consistent data base.

to 2), 3) Why don't you try it with "ACCESS EXCLUSIVE"? (see http://www.postgresql.org/docs/current/static/explicit-locking.html)

EDIT: If you dislike locking the whole table with an "ACCESS EXCLUSIVE" lock, you could also use an "Advisory Lock" (see section 13.3.4 in the link above).

Answer 2

If the reads you want to block are simultaneous executions of the same transaction just with different data, use an UPDATE statement with returning clause.

Check out docs on UPDATE statement. To answer your question about the point of an exclusive row lock, it's to prevent data inconsistency from simultaneous updates. Readers get a consistent view of the database at all times.

Answer 3

In the code sample you provided, all reads of that row will see the old value until that transaction commits.

1) Consider the code sample being run twice concurrently, with the same value of X. If instance A has run select ... for update then it has locked that row until it commits. Instance B, doing the same, will block on trying to take the lock the same way. Only when A commits, can B continue. B will then be getting the value A left behind in its final update.

If Y depends on what value the select ... for update query read, or another read of it in the 'complex' part, then this will have the same effect as if they were run serially - you don't get the race condition where one of the results will be discarded.

If Y is purely the result of the complex queries in the middle, then running it in parallel without select ... for update they will both do the same update.

2) for share will allow other selects on that row to proceed, but will cause anything else trying to select ... for update or update ... to block until it's done. If the sample code was run twice, concurrently, they would deadlock on reaching the update statement, causing one of them to be aborted.

3) No. Doing this is dangerous, since a reader could read a half-updated field. (Or read the rest of a field updated after the start of reading.) It can also break consistency, showing the reader a value updated after the start of its transaction.