Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...