Параллельная транзакция mysql?

Question

[Vitaly Serov]

Имеются несколько конкурентных процессов, работающих с одной таблицей mysql. Каждый процесс делает выборку из таблицы 100 записей и обрабатывает их.
SELECT * FROM table LIMIT 100 FOR UPDATE;
Если запустить транзакцию, то каждый процесс будет ждать завершения транзакции предыдущего процесса, а нужно, что бы выбирались только незаблокированные строки без ожидания окончания конкурентной транзакции.

Задача довольно избитая и уже не раз обсуждалась в сети, но результата пока нет. SELECT ... FOR UPDATE не помогает.

Пометка предварительно нужных записей уникальным PID - не подходит, т.к. таблица большая и нагрузка дополнительными UPDATE ... SET pid=*** - совсем не уместна.

Answer 1

[Confl1kt]

используйте очередь(redis, rabbitMQ)
загружаете все ид записей, которые необходимо обработать, в очередь
куча процессов достаёт из очереди по одной записи, пока они не закончатся, и обрабатывают

Comments

[Vitaly Serov]

очереди рассматриваю как последний вариант

Answer 2

[egor_nullptr]

Заводим вспомогательную таблицу с одной единственной записью, обозначающей максимальный ID обрабатываемых записей.

CREATE TABLE limit_id_table (
    max_id int unsigned not null
) ENGINE=innodb;

INSERT INTO limit_id_table values (0);

Перед запуском транзакции проводим UPDATE этой записи с возвратом обновленного значения, по этому значению определяем интервал ID для обработки.

UPDATE limit_id_table set max_id = @max_id := max_id + 100;
BEGIN;
SELECT * FROM table where id BETWEEN @max_id - 99 AND @max_id FOR UPDATE;
.....
COMMIT;

Comments

[Vitaly Serov]

хм, а штатных средств в mysql разве нет? разве FOR UPDATE не для этого придуман?

[egor_nullptr]

FOR UPDATE придуман для блокировки записей, но SELECT * FROM table LIMIT 100 FOR UPDATE будет всегда пытаться заблокировать одни и те же записи.

[Vitaly Serov]

как же тогда решается вопрос параллельности? например в postgresql можно решить через pg_try_advisory_lock() и pg_advisory_unlock() - хотя не очень надежно

[egor_nullptr]

Что значит как? В предложенном решении в разных транзакциях блокируются разные записи, поэтому параллельные транзакции не блокируют друг друга.

[Vitaly Serov]

ок, давай так:
CREATE TABLE test.foo (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB

INSERT iN TO test.foo SET title = 'title1';
INSERT iN TO test.foo SET title = 'title2';
INSERT iN TO test.foo SET title = 'title3';
INSERT iN TO test.foo SET title = 'title4';

открываем две консоли, заходим в mysql, и выполняем в обеих такое:
begin; select * from foo limit 2 for update; select sleep(5); commit;

Результат: 1 консоль
+----+--------+
| id | title |
+----+--------+
| 1 | title1 |
| 2 | title2 |
+----+--------+
3 rows in set (0.00 sec)
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)

Вторая консоль ждет несколько секунд и ...
Результат: 2 консоль
+----+--------+
| id | title |
+----+--------+
| 1 | title1 |
| 2 | title2 |
+----+--------+
3 rows in set (4.04 sec)

+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)

Query OK, 0 rows affected (0.00 sec)

Вывод:
второй процесс ждет завершение первой транзакции

[Vitaly Serov]

возможно это зависит от конфигурации mysql ?

[egor_nullptr]

Вы, кажется, не разобрались в предлагаемом мной решении с дополнительной таблицей для разделения записей, с которыми оперируют транзакции.

[Vitaly Serov]

делать "расческу" или как оно там называется никак нельзя, т.к. в примере указана простая таблица, на практике же, выборка проходит с сортировкой и фильтрацией, так что реальные id идут вперемешку, кроме того велика вероятность окончания процесса с ошибкой, так что захваченные строки останутся не обработанными и придется частенько запускать обнуление счетчика max_id, для повторного прохода

Answer 3

[Дмитрий Горячев]

Если разбить на более мелкие транзакции и проводить их по очереди?

Comments

[Vitaly Serov]

нет, по очереди не пойдет, т.к. вся фишка в параллельности

[Дмитрий Горячев]

Посмотри MySQL — SELECT FOR UPDATE

[Vitaly Serov]

при запросе -
BEGIN;
SELECT * FROM table LIMIT 100 FOR UPDATE;
COMMIT;

один процесс ждет, пока предыдущий не завершит транзакцию - нет параллельности

Answer 4

[stavinsky]

Если актуальность данных не сильно важна, возможно начать с уровня изоляции транзакций?