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."
- 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?