10

A program will SELECT several records from a table and update each row, while it can be executed many times, which will lead to several process will complete the same task simultaneously.

How can I prevent two different processes to update the same row in the table. That's to say, how can I ensure each process can SELECT different records? Is there any locks on row-select level in MySQL? Or in this situation, is there any better solution to prevent a single row updating for many times?

Allen Koo
  • 1,996
  • 3
  • 14
  • 15
  • http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html ? – Joachim Isaksson Mar 17 '13 at 09:33
  • TRANSACTIONS, INNODB look for these two – swapnesh Mar 17 '13 at 09:33
  • @swapnesh I've tried INNODB, Transactions. when a process start transaction, and `use lock in share mode` won't prevent another process to get the same row, but just lock it for not updating and deleting. – Allen Koo Mar 17 '13 at 09:45
  • @JoachimIsaksson The solution may have weakness. If two process run almost the same time, does it exist any possibilities that the second process get the list before the row `status` field has been changed? – Allen Koo Mar 17 '13 at 09:50

1 Answers1

12

You can use a SELECT FOR UPDATE. Inside your transaction, start out selecting the rows that you want to "lock", something like this:

 SELECT * from TABLE where id = 123 FOR UPDATE;

If two different transactions try to do this at the same time, MySQL will make the second one wait until the first one has committed the transaction. That way, you'll be assured that the second transaction only looks at the row after the first one is done with it.

mjuarez
  • 16,372
  • 11
  • 56
  • 73
  • Thanks. Maybe I should change my program's logic to get the row one by one, not get a list first, because I want such kind of queue can process by several processes, not a single one with others waiting. – Allen Koo Mar 17 '13 at 09:54