Consistent read and update without table locking in MySQL/InnoDB

Question

I need to perform the following on MySQL/InnoDB. Assume I have a table consists of 3 cols (id, counter, status)

I need to:

1. Select rows where status = 0
2. Update them by setting status = 1, and increment counter by 1
3. Return the selected/updated id to client

Requirements:

• The rows to be update must exactly the same with selected, even new rows has been added after the 1st select
• No duplicated update on a row is allowed, even multiple scripts are running on the same data at the same time

What would be the best solution in term of performance and scalability (work for large table)?

Answer 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT  id
FROM    mytable
WHERE   status = 0
FOR UPDATE;
UPDATE  mytable
SET     status = 1,
        cnt = cnt + 1
WHERE   status = 0;
COMMIT;