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

mysql select-for-update
Billy ONeal picture Billy ONeal · Mar 23, 2011 · Viewed 7.3k times · Source

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

Mark picture Mark · Mar 23, 2011

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.

Related questions

When to use SELECT ... FOR UPDATE?

Please help me understand the use-case behind SELECT ... FOR UPDATE. Question 1: Is the following a good example of when SELECT ... FOR UPDATE should be used? Given: rooms[id] tags[id, name] room_tags[room_id, tag_id] room_id and …

Read More
Why use SELECT FOR UPDATE?

I have question regarding what purpose we are using SELECT FOR UDPATE? What does it do exactly? I have 2 tables, from that I need to select rows from table and update the same rows. For example: Select Query SELECT * from …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More