I have become maintainer of a program that uses PostgreSQL 6.5.2 for
database functionality. It is littered with code blocks that do the
following:
1. SELECT * FROM some_table WHERE foo = bar FOR UPDATE;
2. -- Choose a new value for some_field, which might or might not be based on its original value.
3. UPDATE some_table SET some_field = new_value WHERE foo = bar;
I'm worried about concurrent process synchronization. According to the
PostgreSQL docs on the LOCK command, SELECT ... FOR UPDATE acquires a "ROW
SHARE MODE" lock, which is not self-conflicting. This tells me that when
two processes execute the same code block concurrently, this can happen:
1. Process A selects the desired row for update. It now has a copy of the original values in that row.
2. Process B does the same. (This is allowed because ROW SHARE MODE locks do not conflict with each other.) It now
hasa copy of the original values in that row.
3. Process A chooses a new value for the desired field, based on the original value.
4. Process B does the same.
5. Process A updates the row with its new value, and exits.
6. Process B updates the row with its new value, overwriting the changes made by process A.
Is it true that SELECT ... FOR UPDATE only acquires a ROW SHARE MODE lock,
and that it isn't self-conflicting?
How can I acquire a self-conflicting row level lock?
What is the proper way to perform operations like those I'm describing?
Thank you,
Forest
