What's the purpose of SELECT ... *FOR UPDATE*?

Question

I'm confused as to why you would specify FOR UPDATE -- why does the database care what you're going to do with the data from the SELECT?

EDIT: Sorry, I asked the question poorly. I know the docs say that it turns things into a "locking read" -- what I'd like to know is "what cases exist where the observable behavior will differ between specifying FOR UPDATE and not specifying it -- that is, what specifically does that lock entail?

Answer 1

The specific case that this is designed to fix is when you need to read and update a value in a column. Sometimes you can get away with updating the column first (which locks it) and then reading it afterwards, for instance:

UPDATE child_codes SET counter_field = counter_field + 1;
SELECT counter_field FROM child_codes;

This will return the new value of counter_field, but that may be acceptable in your application. It would not be acceptable if you were trying to reset the field (and you therefore needed the original value) or if you had a complex calculation that could not be expressed in an update statement. In this case to avoid two connections racing to update the same column at the same time you need to lock the row.

If your RDBMS doesn't support FOR UPDATE then you can simulate it by performing a useless update e.g.

UPDATE child_codes SET counter_field = counter_field;
SELECT counter_field FROM child_codes;
UPDATE child_codes SET counter_field = 0;