10

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?

frederj
  • 1,483
  • 9
  • 20
Billy ONeal
  • 104,103
  • 58
  • 317
  • 552

5 Answers5

19

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.

  • Yes -- I read the docs -- but what does that do for the user of the RDBMS? – Billy ONeal Mar 23 '11 at 20:44
  • 2
    Ah -- so this would let you do something like `START TRANSACTION; SELECT MAX(id+1) AS newid FROM thetable FOR UPDATE; INSERT INTO thetable (id) VALUES (newid); COMMIT;` -- and you'd know the value returned for `MAX() + 1` couldn't conflict with anyone else? – Billy ONeal Mar 23 '11 at 20:51
3

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;
Neil
  • 54,642
  • 8
  • 60
  • 72
2

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

duffymo
  • 305,152
  • 44
  • 369
  • 561
0

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • Huh? The statement is finished at the semicolon! (What could go between "FOR UPDATE" and the semicolon?) – Billy ONeal Mar 23 '11 at 20:45
  • When it is done running man!. The statement is terminated at the semicolon – SQLMenace Mar 23 '11 at 20:46
  • I don't understand -- the statement is terminated at the semicolon yes -- which means that in your example above `FOR UPDATE` does not affect the `UPDATE` statement -- because they're separate statements separated by semicolons (hence my confusion as to why the directive would need to exist). – Billy ONeal Mar 23 '11 at 20:48
  • When both statements are finished running you are done..maybe that makes it clearer – SQLMenace Mar 23 '11 at 20:53
  • How does the RDBMS know when that is though? When the connection is closed? – Billy ONeal Mar 23 '11 at 21:18
  • 4
    When the transaction ends. ie. COMMIT. If you have auto-commit on and are running single statements it's meaningless. – gtd Mar 15 '12 at 11:33
0

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.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132