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

SELECT FOR UPDATE tells the RDBMS that you want to lock those rows so no one else can access them until you UPDATE and commit or roll them back and unlock them:

http://www.techonthenet.com/oracle/cursors/for_update.php

Answer 2

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

It has to do with locking the table in transactions. Let's say you have the following:

START TRANSACTION;
SELECT .. FOR UPDATE;
UPDATE .... ;
COMMIT;

after the SELECT statement runs, if you have another SELECT from a different user, it won't run until your first transaction hits the COMMIT line.

Also note that FOR UPDATE outside of a transaction is meaningless.

Answer 3

It creates a locking read so that nobody can update it until you are done, example

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

See here http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

Answer 4

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;

Answer 5

It will lock the rows (or the whole table) so that the rows can't be updated in another session concurrently. The lock is held until the transactions is committed or rolled back.