Postgresql – Postgres, MVCC, and Locking

lockingpostgresql

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?

Best Answer

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).

Related Question