Search code examples
mysqlinnodbselect-for-update

SELECT FOR UPDATE

Possible Duplicate:
SELECT … FOR UPDATE and MAX()

Which rows in this query lock ?

select max(id) from table where id like '9%' for update

what occur if another user run this query ?

this is related question.

Solution

  • If you have no index on id, this will lock all of the records. But I guess you have such index. So this will lock all records that are matching, including some records in between (if you are locking 3 and 5, 4 is also being locked)

    A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

    edit In the case of SELECT max(id) FROM ... you don't need to read any rows from the table, because this information can be optained from the index. If you want to lock exactly one row, the correct query would be SELECT * FROM table WHERE id = SELECT max(id) FROM table