Пере до мной встала интересная задача, с которой я не могу справиться.
Условие:
Есть таблица с данными. Нужно в многопоточном режиме выбирать из таблицы по одной записи и обновлять количество выборов для нее. Каждая запись должна быть выбрана не больше двух раз. Так-же между выбором и обновлением есть микрозадержка.
На практике есть скрипт, который выполняется в многопоточном режиме и выбирает запись из mysql таблицы innodb, которая была выбрана не более 2-х раз до этого, основываясь на поле count. После выбора записи для нёё, поле count увеличивается на 1.
$connect = mysqli_connect($host, $dbuser, $dbpass);
if($connect){
mysqli_select_db($connect, $dbname);
for ($i = 1; $i <= 10; $i++) {
//Выбираем подряд записи, у которых count < 2
$sql = mysqli_query($connect, "SELECT id FROM threadsTest WHERE id >= {$i} AND count < 2 LIMIT 1;");
if (mysqli_num_rows($sql) > 0){
$row = mysqli_fetch_assoc($sql);
//Задержка после выбора 0.1 сек
usleep(100000);
//Обновление count
mysqli_query($connect, "UPDATE threadsTest SET count = count + 1 WHERE id = {$row['id']}");
}
}
mysqli_close($connect);
}
Но очевидно, что в такой ситуации поля выбираются большее количество раз, т.к. апдейт просто не успевает выполнятся и нарушается логика работы.
Моё решение - после выборки создавать дополнительный файл для записи, если его нет и инкрементить в него с блокировкой счетчик выборок. Сразу после выборки проверять этот файл и если счетчик в нём 2, пробовать выбрать следующую запись. Однако у этого способа есть недостаток. Проверка происходит уже после выборки и приходится перебирать намного большее количество записей. В моем тесте это увеличило время работы в 100 раз и это очень зависимо от количества потоков и количества данных в таблице.
Что можно предпринять в такой ситуации, сохранив логику работы и приемлемую скорость выполнения?
Буду рад любым свежим идеям и предложениям :)
Кому интересно попробовать разобраться в этом, вот тестовый скрипт:
https://yadi.sk/d/mnHA4grn39xBaT
create_table.php - создаёт тестовую таблицу с полями id и count, заполняет её данными
clear_table.php - обнуляет count в тестовой таблице
thread.php - сам скрипт, который делает выборку и инкрементит count
run_threads.sh - bash скрипт, который запускает thread.php в 300 потоков
UPD:
Удалось решить задачу с помощью блокировки строк FOR UPDATE и добавления 2х полей.
Добавил поле lock - tinyint
Добавил поле type - tinyint, добавил type в индекс
Код выше приобрел такой вид:
$connect = mysqli_connect($host, $dbuser, $dbpass);
if($connect){
mysqli_select_db($connect, $dbname);
mysqli_query($connect, "SET AUTOCOMMIT = 0;"); //Отключаем автокоммит
for ($i = 1; $i <= 10; $i++) {
$rand = rand(1,10); //Это сделал для ускорения обработки, что бы каждый запущенный скрипт выбирал более-менее разные строки
mysqli_query($connect, "BEGIN;");//Стартуем транзакцию
//Выбираем подряд записи, у которых count < 2, type = от 0 до 10, и поле lock = 0, блокируем запись с помощью FOR UPDATE
$sql = mysqli_query($connect, "SELECT id FROM threadsTest WHERE id >= {$i} AND type = {$rand} AND `lock` = 0 LIMIT 1 FOR UPDATE;");
if (mysqli_num_rows($sql) > 0){
//Ставим указатель блокировки вручную, чтобы, пока эта запись обрабатывалась, другие скрипты ее не извлекали
mysqli_query($connect, "UPDATE threadsTest SET `lock` = 1 WHERE id = {$row['id']};");
//Коммитим апдейт снимая блокировку с записи
mysqli_query($connect, "COMMIT;");
$row = mysqli_fetch_assoc($sql);
//Задержка после выбора 0.1 сек
usleep(100000);
//Обновляем count, снимаем указатель блокировки
mysqli_query($connect, "BEGIN;");
mysqli_query($connect, "UPDATE threadsTest SET count = count + 1, `lock` = 0 WHERE id = {$row['id']}");
mysqli_query($connect, "COMMIT;");
}
}
mysqli_close($connect);
}
Таким макаром удалось добиться выполнения всех скриптов, без превышения count, и с приемлемым временем выполнения.
Если начальный вариант 10 итераций в 300 потоков выполнялся ~1-2 сек., то этот вариант в том же количестве выполняется за ~2-4 сек. Без type = rand ~8-10 сек.
Средний
