CODEWITHSUNDEEP
mysql
Zishan Neno
Zishan Neno

Reputation: 2817

mysql - Locking rows for select query?

I have a mysql table (table-A) that contains a list of unique product ids. The app which utilizes this table makes use of multiple threads, each of which selects a table row (top most), uses an API to grab the product data and update it to a different table (table-B). Once done, the thread deletes the corresponding product id row within table-A and selects another one to work on (looping until all rows in table-A have been deleted while table-B has been updated).

How do I prevent my app's threads from accidentally working on the same row from table-A? Is there a way to lock a row from being selected?

Example: The application's thread-1 selects row-1 from table-A. It takes about 10 to 15 seconds to grab and update all the related data into table-B from the API. While this is happening, thread-2 will fire off and check table-A to select a row to work on. In this case, I want only row-1 locked so that it does not thread-2 does not see/read it and instead picks row-2.

Upvotes: 3

Views: 3911

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

Native MySQL locking doesn't provide this functionality. You could use a column to perform your "locks".

Assuming each thread had a unique ID, you could create a column named thread_owner, with default 0.

One thread would grab a row like this:

UPDATE mytable
SET thread_owner = :my_threadID
WHERE thread_owner = 0
LIMIT 1

Then select the row like this (it might return none, if there were no rows to be processed):

SELECT *
FROM mytable
WHERE thread_owner = :my_threadID

Then process it, and finally delete it.

This solution would work on both MyISAM and InnoDB.

However, for InnoDB, it might be slow because each UPDATE statement is trying to lock all rows where thread_owner = 0, and unless you're sure you're locking all rows in the same order each time, it could even cause a deadlock. So, you might try explicitly locking the whole table in your UPDATE statement:

LOCK TABLES mytable WRITE;
UPDATE mytable
SET thread_owner = :my_threadID
WHERE thread_owner = 0
LIMIT 1;
UNLOCK TABLES;

That way, both MyISAM and InnoDB will work the same way.

Upvotes: 7

jcho360
jcho360

Reputation: 3759

try with "lock table" command, I used 2 windows to show you:

jcho_1> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)

jcho_2> select * from t1;

jcho_2 stay waiting to release table to be able to read, when jcho_1 do this:

jcho_1> unlock tables;
Query OK, 0 rows affected (0.00 sec)

jcho_2 is able with his query.

jcho_2> select * from t1;
+----------+-------------+--------------+---------------------+
| actor_id | first_name  | last_name    | last_update         |
+----------+-------------+--------------+---------------------+
|      206 | a           | b            | 0000-00-00 00:00:00 |
|       71 | ADAM        | GRANT        | 2006-02-15 04:34:33 |
|      132 | ADAM        | HOPPER       | 2006-02-15 04:34:33 |
...
|        0 | 0           | 0            | 0000-00-00 00:00:00 |
+----------+-------------+--------------+---------------------+
202 rows in set (1 min 27.67 sec)

enter image description here

enter image description here

Upvotes: 0

Related Questions