Search code examples
mysqlselect-for-update

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

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?

Solution

  • 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.