CODEWITHSUNDEEP
phpmysqlconcurrencyinnodb
Molfar
Molfar

Reputation: 1501

Mysql lock concurrent read/update of row

I have table, and many (too many) requests for selecting from it a single row. After selecting a row, the script run update query to set a flag that is that row had been "selected". But as we have too many requests per time, in period between one thread select a row, and update its flag, another thread have time to select the same row.

Select query get one row from the table, ordering it by some field and using LIMIT 0, 1. I need that DB just skip the row, that had been selected before.

The engine is InnoDB.

Upvotes: 0

Views: 2143

Answers (1)

Steve Mayne
Steve Mayne

Reputation: 22858

Just before you start a transaction, call the following:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

This will ensure that if you read a row with a flag, it'll still be that way when you update it within the same transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT id_site
INTO @site
FROM table1 WHERE flag = 0 ORDER BY field LIMIT 0,1;

UPDATE table1 SET flag = 1 WHERE id_site = @site;

COMMIT;

Upvotes: 2

Related Questions