How do I make sure I'm not updating the same record from multiple processes? Do I need table locks?

Question

I'm working on a project using a MySQL database as the back-end (accessed from PHP). Sometimes, I select a row, do some operations on it, and then update the record in the database.

I am worried that another user could have initiated a similar process on the same row right after the first select, and his changes could overwrite some of the changes the first user did (because the second user's select did not yet include those changes).

Is this an actual problem? Should I lock the table, and won't this severely impact my application's performance? Any other solutions?

Just to be thorough with my information, I also have some CRON jobs running that could also be modifying the same data.

Thanks!

Answer 1

I can think of two solutions, other than explicitly using transactions:

• Use SELECT .. FOR UPDATE : http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

• Manually change a value so the row is not select by other queries:

  SET @update_id := 0;

  UPDATE table_name SET status = 'IN_PROCESS', id = (SELECT @update_id := id) WHERE status = 'WAITING' AND [your condition] LIMIT 1;

  SELECT @update_id;

Here, the rows to be selected must have the value of status="WAITING". And when this query runs, it selects the ID, and changes the value of 'status', so the row can't be selected by other queries.