FOR UPDATE and LOCK IN SHARE MODE
Hi. I'm using MySQL 5.1.32 an apple-darwin and in fedora, InnoDB.
I have a InnoDB table with rows, say 10000, and I want to lauch a group of workers (say 2) that continuously take a small set of rows (batches of 10 rows), update the info, then get another set, etc until there's no more rows to work on. The table has a boolean column saying if the row was processed or not, so I can have control of the unseen rows.
The question is to avoid the workers on updating the same rows. While worker 1 is issuing a SELECT query to get a batch of 10 docs, I need to make sure that if worker 2 is also fetching more rows to process, it picks other 10 rows.
I'm experimenting with FOR UPDATE and LOCK IN SHARE MODE, but I'm not getting the desired behavior. Note that the workers are identical, so the SELECT statements are similar, they just want the next X unlocked rows to work on. Here is what I'm getting:
create table t(id int primary key) engine=innoDB;
Query OK, 0 rows affected (0.05 sec)
insert into t values(1),(2),(3),...,(50)
Query OK, 50 rows affected (0.04 sec)
SESSION 1: SET AUTOCOMMIT=0
SESSION 2: SET AUTOCOMMIT=0
session 1: START TRANSACTION
SESSION 1: select * from t limit 10 FOR UPDATE;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0.00 sec)
session 2: START TRANSACTION
SESSION 2: select * from t limit 10 FOR UPDATE;
It it hangs, waiting for the commit on session 1. I want it to give me rows 11 to 20, but it doesn't happen like that. Of course, if I commit on session 1, the waiting SELECT on session 2 gives me rows 1 to 10, that's even worse.
With LOCK in SHARE MODE:
Session 1: set autocommit=0;
Session 1: start transaction;
session 1: select * from t limit 10 LOCK IN SHARE MODE;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (4.91 sec)
session 2: start transaction;
session 2: select * from t limit 10 LOCK IN SHARE MODE;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
So it doesn't look either that it would be helpful.
Any ideas?