MYSQL How can I make sure row does not get UPDATED more than once?

Question

I have mutliple workers SELECTing and UPDATing row.

id status 10 new 11 new 12 old 13 old

Worker selects a 'new' row and updates its status to 'old'. What if two workers select same row at the same time? I mean worker1 selects a new row, and before worker one updates its status, worker2 selects the same row?

Should I SELECT and UPDATE in one query or is there another way?

Answer 1

You could LOCK the table before your read, and unlock it after your write. This would eliminate the chance of two workers updating the same record at the same time.

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

Answer 2

Depending on your database storage engine (InnoDB, MyIsam, etc), you may be able to lock the table while a person is modifing it. It would then keep simotanious actions to the same table.

Answer 3

could you put conditions in your PHP logic to imply a lock? Like set a status attribute on a row that would prevent the second user from performing an update. This would possibly require querying the database before an update to make sure the row is not locked.

Answer 4

You can use LOCK TABLES but sometimes I prefer the following solution (in pseudo-code):

// get 1 new row
$sql = "select * from table where status='new' limit 0, 1";
$row = mysql_query($sql);

// update it to old while making sure no one else has done that
$sql = "update table set status='old' where status='new' and id=row[id]";
mysql_query($sql);

// check
if (mysql_affected_rows() == 1)
  // status was changed
else
  // failed - someone else did it