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:
- 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?
- If I read the row with SELECT ... FOR SHARE, does that achieve the same affect as an "exlusive" lock?
- Is is possible to turn off MVCC for a table/schema/database and allow in-place writes?
SERIALIZABLE
for precisely this purpose, no manual locking required. This proves, yet again, that the program suite is not SQL-compliant.