What is the purpose of SELECT ... * FOR UPDATE *?

Question

I am confused why you specify FOR UPDATE - why in the database they care about what you are going to do with the data from SELECT ?

EDIT: Sorry, I asked the question poorly. I know that the documents say that this turns things into a “read lock” - I would like to know “what are the cases where the observed behavior will be different from indicating FOR UPDATE and not indicating it — that is, in particular, whether this castle?

Answer 1

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

This is due to table locking in transactions. Let's say you have the following:

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

after executing a SELECT statement, if you have another SELECT from another user, it will not work until your first transaction hits the COMMIT line.

Also note that FOR UPDATE outside the transaction does not make sense.

Answer 2

The specific case where this is intended to be fixed is when you need to read and update a value in a column. Sometimes you can get away with updating a column first (which blocks it), and then after that it reads, for example:

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

This will return the new counter_field value, but it may be acceptable in your application. It would be unacceptable if you tried to reset the field (and therefore you need the original value), or if you had a complex calculation that could not be expressed in the update statement. In this case, to avoid the two compounds participating in the race for updating the same column, you need to lock the row.

If your RDBMS does not support FOR UPDATE, you can simulate it by performing a useless update, for example.

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

Answer 3

SELECT FOR UPDATE tells RDBMS that you want to lock these rows so that no one else can access them until you UPDATE and complete or unscrew them and unlock:

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

Answer 4

It creates a read lock so that no one can refresh it until you are done, for 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 5

It will lock the rows (or the entire table) so that the rows cannot be updated in another session at the same time. The lock is maintained until the transactions are completed or rolled back.