0

I have 2 tables, one called user, stores users, and one called process that stores the list of active processes updating users.

What I want to do is be able to select 50 out-of-date users, lock them by inserting a record in the process table with the user id range that I am updating, so that if a second process is fired up, it will not start processing the same users.

To sum-up:

  1. Select in the process table the last processed id
  2. Select in the user table 50 ids > last processed id
  3. Insert in the process table a new record with the ids range I am about to process

I want to make sure that if 2 processes (P1 and P2) are fired up at the same time, the 3 steps are executed consecutively for each process. i.e:

  • P1 step 1
  • P1 step 2
  • P1 step 3
  • P2 step 1
  • P2 step 2
  • P2 step 3

and not

  • P1 step 1
  • P2 step 1
  • P1 step 2
  • etc.

How should I do that? Transactions or Table locks? Or even something else?

My tables are InnoDB ones.

Thank you!

Fabien Warniez
  • 2,731
  • 1
  • 21
  • 30

1 Answers1

0

Row level locks. The advantage of InnoDB is it supports row level locks. You need to lock the records you're interested in updating to that transaction. You should use a transaction per batch, that way you can release them aggressively when you're done.

See: http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html

hsanders
  • 1,913
  • 12
  • 22
  • Thanks for your answer. The thing is that each update can take a very long time and I don't want to keep anything locked in the DB for that long. I am just looking for a way to make sure my 3 steps are executed consecutively. – Fabien Warniez Sep 19 '12 at 20:02