CODEWITHSUNDEEP
mysqllockinginnodbrow
FlamingMoe
FlamingMoe

Reputation: 2992

Row lock for update status

I have a table of "commands to do" with a status ('toprocess', 'processing', 'done')

I have several instances (amazon ec2) with a daemon asking for "commands to do".

The daemon asks for rows with status 'toprocess', then it processes, and at the end of each loop it changes the status to 'done'.

The thing is that, before starting that loop, I need to change all rows 'toprocess' to status 'processing', so other instances will not take the same rows, avoiding conflict.

I've read about innodb row locks, but I don't understand them very well ...

SELECT * from commands where status = 'toprocess' then I need to take the ID's of these results, and update status to 'processing' , locking these rows until they are updated.

How can i do it ?

Thank you

Upvotes: 2

Views: 4645

Answers (3)

nos
nos

Reputation: 229342

You'd use a transaction , and read the data with FOR UPDATE, which will block other selects that include the FOR UPDATE on the rows that gets selected

begin transaction;
 select * from commands where status = 'toprocess' for update;
 for each row in the result:
  add the data to an array/list for processing later. 
  update commands set status='processing' where id = row.id;

 commit;

process all the data

Read a bit about the FOR UPDATE , and InnoDB isolation levels.

Upvotes: 3

Galz
Galz

Reputation: 6842

A possible (yet not very elegant) solution may be to first UPDATE the record, then read its data:

Each deamon will have a unique ID, and the table will have a new column named 'owner' for that ID. Then the deamon will run something like "UPDATE table SET status='processing', owner='theDeamonId' where status='toprocess' ... LIMIT 1"

While the update runs the row is locked, so no other deamon can read it. After the update this row is Owned by a specific deamon, then it can run a SELECT to fetch all necessary data from that row (WHERE status='processing' AND owner= 'theDeamonId').

Finally, the last UPDATE will set the row to 'processed', and may (or may not) remove the owner field. Keeping it there will also enable some statistics about the deamons' work.

Upvotes: 1

JamesHalsall
JamesHalsall

Reputation: 13505

As far as I know you can't use MySQL to lock a row (using a built-in method). You have two options though:

  1. If your table should not be read by any other process until the locks are released then you can use table level locking as described here

  2. You can implement your own basic row locking by updating a value in each row you're processing, and then have all your other daemons checking whether this property is set (a BIT data type would suffice).

InnoDB locks at a row level for reading and updating anyway, but if you want to lock the rows for an arbitrary period then you may have to go with the second option.

Upvotes: 0

Related Questions