MySQL Forums
Forum List  »  Newbie

FOR UPDATE and LOCK IN SHARE MODE
Posted by: Nuno Cardoso
Date: October 14, 2009 05:45AM

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?

Options: ReplyQuote


Subject
Written By
Posted
FOR UPDATE and LOCK IN SHARE MODE
October 14, 2009 05:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.