How to lock a row for select in MySQL [duplicate]

Question

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?

Arezzini

dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html ? – Ie

TRANSACTIONS, INNODB look for these two – Tonneau

@Tonneau 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. – Arezzini

@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? – Arezzini

Answer 1

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.

Diphenylamine

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. – Arezzini