How to lock a row for select in MySQL

mysql locks
Allen Koo picture Allen Koo · Mar 17, 2013 · Viewed 7.6k times · Source

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?

Answer

mjuarez picture mjuarez · Mar 17, 2013

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.

Related questions

Dropping table makes MySQL hang

When I try to drop a table, MySQL hangs. I don't have any other open sessions. How to resolve this? I have waited for 10 hours and the process has not terminated.

Read More
InnoDB SELECT ... FOR UPDATE statement locking all rows in a table

MySQL Server version 5.1.41 with InnoDB plugin enabled. I have the following three tables for invoices: invoices, invoice_components and invoice_expenses. Table invoices has invoice_id primary key. Both invoice_components and invoice_expenses are linked to table invoices with …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More