mysql - Locking rows for select query?

Question

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.

see transactions, optimistic/pessimistic locking, etc – biziclop Jun 22 '12 at 13:52 — biziclop, Jun 22 '12 at 13:52

Marcus Adams · Accepted Answer · 2016-11-29T15:28:36.080

7

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.

edited Nov 29 '16 at 15:28

answered Jun 22 '12 at 14:32

Marcus Adams

53,009
9
91
143

Innodb supports row level locking, myisam does not. Myisam has *concurrent* inserts, but that isn't the same thing. See [entry 9 here](https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines). – Spencer Rathbun Jun 22 '12 at 14:59
@SpencerRathbun, did you mean to post this comment to my post? How is it relevant? – Marcus Adams Jun 22 '12 at 17:43
You are indicating that the `UPDATE` would have a problem because of locking all rows on InnoDB, which is incorrect since InnoDB does not lock the entire table, just the entries to update. – Spencer Rathbun Jun 22 '12 at 17:58
@SpencerRathbun, ah, I was thinking that all rows would have `thread_owner = 0`, but that's not really true after the first pass. Thank you! I fixed my answer to clarify. – Marcus Adams Jun 22 '12 at 18:36
No problem, I thought you had a good answer, just needed to clarify it. – Spencer Rathbun Jun 22 '12 at 18:40
This answer is the best one; I've deleted mine. – Andrew Kozak Jun 22 '12 at 18:44
Thank you all. This answer helped do the job. – Zishan Neno Jun 23 '12 at 10:09
Could make sense so use "read committed" to unlock all the unused rows? https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/ – Terix Nov 29 '16 at 11:47

score 0 · Answer 2 · answered Jun 22 '12 at 14:06

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

mysql - Locking rows for select query?

2 Answers2

Linked