[MYSQL] Взять уникальную строку и обновить - транзакция

[myweb101]

Есть таблица в Innodb с полями id (автоинкрементное), login, pass, used (значение 1 или 0)

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

Написал процедуру, но что-то меня смущает:
1. Нужно ли использовать autocommit в 0 а потом возвращать 1?
2. FOR UPDATE - уместно?
3. Все верно?

Протестировал на 10 потоках на 1000 строках из таблицы, вроде все время берет уникальные значения.

SET autocommit=0;
START TRANSACTION;
    SET @peremennaia:= (SELECT full_login FROM mail WHERE used = 0 ORDER BY RAND() LIMIT 1 FOR UPDATE);
    SELECT id, full_login, password FROM mail WHERE full_login = @peremennaia;
    UPDATE mail SET used = 1 WHERE full_login = @peremennaia;
COMMIT;
SET autocommit=1;

 

[IgorSush]

Я в такие дебри мускуля не лез.

Делаю по-другому:
В потоке генерирую случайное число,
В первом запросе апдэйчу необходимую строку

UPDATE mail SET used="$big_random" WHERE used IS NULL AND ....​

Во втором получаю ее

SELECT * FROM mail WHERE used="$big_random"​

И потом по окончании потока обнуляю

UPDATE mail SET used=NULL WHERE used="$big_random"
​

Согласен, целых 3 транзакции, но для небольших объемов вполне.
Косяков не наблюдал

 

[Koqpe]

Я эмулирую работу со списком, взяли первую строку " id (автоинкрементное), login, pass, id_time", удалили и добавили в конец списка.

Берем самый "старый" аккаунт:

SELECT * FROM Innodb ORDER BY id_time ASC LIMIT 1

После взятия обновляем время использования аккаунта в таблице, поле id_time:

UPDATE Innodb SET id_time=NOW() WHERE `id` = 'полученный id в первом запросе'

 

[myweb101]

К сожалению все ваши варианты не подходят, кроме списка, если только использовать его в одном проекте и я еще создавал отдельный проект, который выгружал этот список, а если проектов несколько, но с ним тоже иногда возникали непонятки, он иногда бывал недоступен ил др. ошибки. Бд с блокировкой строк работает лучше.
Создайте БД с 5к строками и запустите в 50 потоков, когда останется строк меньше, чем потоков, то без блокировки строк тут не обойтись, т.к. в один момент потоки могут взять одинаковые значения. Легко проверить, сделав запись полученных значений в файл, а потом посмотрев повторы. Чем больше поток или обращений к БД, тем больше вероятность получить дубль. Да такое возникает очень редко....но
Делала с блокировкой таблицы с myisam, тоже неплохо, но один раз не выполнилось и вся таблица была в режиме lock, пришлось использовать Innodb.
И как я понял из доков SET autocommit=1; тут не обязательно.

 

[Koqpe]

Вы ставите разные задачи в первом и втором Ваших постах.

Под Вашу первую задачу мой способ работы с таблицей MYSQL будет:

все время берет уникальные значения.

Во всяком случае меня он устраивает

 

[Koqpe]

del

 

[IgorSush]

... т.к. в один момент потоки могут взять одинаковые значения. Легко проверить, сделав запись полученных значений в файл, а потом посмотрев повторы.

Как так? Ведь на время апдэйта таблица лочится средствами мускуля, а когда разлочивается, used взятой строки уже не NULL и для следующих апдэйт запросов эта строка "занята". Одновременный доступ исключен.
Второй транзакцией мы эту строку находим по $big_random, а если ее нет - результат пустой, тут тоже не вижу проблемы.

По этой схеме работал с нагрузкой гораздо серьезнее, чем в 50 потоков, монитор показывал 150+ запросов в секунду, косяков не наблюдал. Причем строки брал не рэндомом, а по порядку, отсортированными по timestamp

 

[myweb101]

Спасибо, да конечно с update я согласен, а вот с select можно одновременно получит одинаковые значения. Ну и так ни кто и не ответил на вопрос, транзакцию я верно написал или нет.

 

[kfil]

Есть таблица в Innodb с полями id (автоинкрементное), login, pass, used (значение 1 или 0)

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

Написал процедуру, но что-то меня смущает:
1. Нужно ли использовать autocommit в 0 а потом возвращать 1?
2. FOR UPDATE - уместно?
3. Все верно?

Протестировал на 10 потоках на 1000 строках из таблицы, вроде все время берет уникальные значения.

SET autocommit=0;
START TRANSACTION;
    SET @peremennaia:= (SELECT full_login FROM mail WHERE used = 0 ORDER BY RAND() LIMIT 1 FOR UPDATE);
    SELECT id, full_login, password FROM mail WHERE full_login = @peremennaia;
    UPDATE mail SET used = 1 WHERE full_login = @peremennaia;
COMMIT;
SET autocommit=1;

вот какое у меня родилось решение

start transaction;
lock tables baza write;
set @free_id=-1;
SELECT id from `baza` where used is null  limit 1 into @free_id;
UPDATE `baza` SET used = 1 WHERE used is null and id=@free_id;
select @free_id;
unlock tables;
commit;

транзакция возвращает id первой свободной записи , если же такие отсутствуют то -1

ps
в строку коннект нужно добавить allowuservariables=True
psps
без локов таблицы всё равно проходят параллельные запросы..
upd
можно обойтись без локов если в конце селекта написать for update тем самым заблокировав аналогичные запросы
окончательный вариант такой

start transaction;
set @free_id=-1;
SELECT id from `baza` where used is null  limit 1 into @free_id for update;
UPDATE `baza` SET used = 1 WHERE used is null and id=@free_id;
select @free_id;
commit;

 

[one]

А в чем особенность предложенных решений? Разве не достаточно брать ближайшую строку со значением 0 и менять его на 1 тем самым указать что данный аккаунт занят.

 

[kfil]

А в чем особенность предложенных решений? Разве не достаточно брать ближайшую строку со значением 0 и менять его на 1 тем самым указать что данный аккаунт занят.

особенность это то чтобы в многопотоке не пересекались значения (если просто использовать два запроса то между ними может вклинится запрос другого потока и как следствие в двух потоках будут аналогичные данные )

 

[progrlab]

start transaction;
set @free_id=-1;
SELECT id from `baza` where used is null limit 1 into @free_id for update;
UPDATE `baza` SET used = 1 WHERE used is null and id=@free_id;
select @free_id;
commit;

1.А данный код вы как есть из кубика "Работа с Базами данных" запускали ?
2.Или оформляли в хранимую процедуру и потом уже ее использовали ?
3.Можно из экшена "Работа с Базами данных" вызвать хранимую процедуру или только с помощью C# ?

 

[kfil]

1.А данный код вы как есть из кубика "Работа с Базами данных" запускали ?
2.Или оформляли в хранимую процедуру и потом уже ее использовали ?
3.Можно из экшена "Работа с Базами данных" вызвать хранимую процедуру или только с помощью C# ?

Запускал с помощью С# в виде одного запроса, в результате он возвращал значение @free_id. Хранимые процедуры не использовал.
Вот пример запроса:

string query="start transaction; set @free_id=-1; SELECT id from `table` where used = 0  ORDER BY RAND() limit 1 into @free_id for update; UPDATE `table` SET used = 1 WHERE used = 0 and id=@free_id; select @free_id; commit;";
string rez=ZennoPoster.Db.ExecuteScalar(query,null,ZennoLab.InterfacesLibrary.Enums.Db.DbProvider.MySqlClient, project.Variables["SqlConnect"].Value);

Переменная rez будет содержать либо номер свободного id либо если все заняты -1.
Можно использовать также для выбора свободного id довольно сложные условия.
Такой код нормально работает в многопотоке.

 

[progrlab]

Спасибо за подсказки и идеи.
Сделал так :
1. на сервере БД MySQL - хранимая процедура.

PROCEDURE ok_database.accnt_id()
  MODIFIES SQL DATA
BEGIN
  set @rid = -1;
  START TRANSACTION;
  SELECT  row_id INTO @rid FROM accounts_table at WHERE at.`condition`='READY' ORDER BY at.time_id ASC LIMIT 1 FOR UPDATE;
  UPDATE accounts_table at set at.time_id=NOW(),  at.`condition`='WORK'
  WHERE at.row_id=@rid and at.`condition`='READY';
  SELECT @rid;
  COMMIT;
END

2. В Zennoposter - два кубика.
C#

string query = "call ok_database.accoutn_id();";
string res = ZennoPoster.Db.ExecuteScalar(query,null,ZennoLab.InterfacesLibrary.Enums.Db.DbProvider.MySqlClient,
                                        project.Variables["MySQLConnectString"].Value);
                                       
return res;

И кубик работы с БД

SELECT <тут перечисление полей>
FROM accounts_table
WHERE row_id=@A
ORDER BY time_id ASC LIMIT 1;

 

[proffman]

Я в такие дебри мускуля не лез.

Запускал в 600 потоков, часто информация либо теряется, либо дубли, либо и то и то. Из 1700к строк, бывало что 99 нехватило при удалении дублей. На 100 потоках парочку раз пробовал, более менее работает нормально. Но ИМХО, очень часто случаи, когда дубль недопустим.

Сделал так :
1. на сервере БД MySQL - хранимая процедура.
.....
2. В Zennoposter - два кубика.
.....
И кубик работы с БД

В MySql только начал разбираться, почти сутку сидел. Да, мощная это штука... С твоим кодом пока не пробовал, а хочется и его еще на тест поставить... Как у тебя ситуация сейчас с многопотоками, решил проблему с данной хранимой процедурой?

 

[Gfoblin]

Вот нашел пока такое
for update
Всё это работает только на таблицах типа InnoDB
Может кому поможет

 

[proffman]

Вот нашел пока такое
for update
Всё это работает только на таблицах типа InnoDB
Может кому поможет

Можно пример для InnoDB? как раз он стоит

 

[Gfoblin]

Можно пример для InnoDB? как раз он стоит

Так выше любой, как я понял, в конец добавляем for update

1. SELECT row_id INTO @rid FROM accounts_table at WHERE at.`condition`='READY' ORDER BY at.time_id ASC LIMIT 1 FOR UPDATE;

 

[progrlab]

Запускал в 600 потоков, часто информация либо теряется, либо дубли, либо и то и то. Из 1700к строк, бывало что 99 нехватило при удалении дублей. На 100 потоках парочку раз пробовал, более менее работает нормально. Но ИМХО, очень часто случаи, когда дубль недопустим.

В MySql только начал разбираться, почти сутку сидел. Да, мощная это штука... С твоим кодом пока не пробовал, а хочется и его еще на тест поставить... Как у тебя ситуация сейчас с многопотоками, решил проблему с данной хранимой процедурой?

Многопоток отрабатывает корректно. Можно с хранимой процедурой. Можно и без нее. Просто у меня привычка логику по максимому в БД выносить
Главное работать через транзакции и блокировки. SELECT FRO UPDATE, start transaction и т.д.
И действительно это работает не на всех движках. На InnoDB - работает.

 

[workoles]

• PROCEDURE ok_database.accnt_id()

• MODIFIES SQL DATA

• BEGIN

• set @rid = -1;

• START TRANSACTION;

• SELECT row_id INTO @rid FROM accounts_table at WHERE at.`condition`='READY' ORDER BY at.time_id ASC LIMIT 1 FOR UPDATE;

• UPDATE accounts_table at set at.time_id=NOW(), at.`condition`='WORK'

• WHERE at.row_id=@rid and at.`condition`='READY';

• SELECT @rid;

• COMMIT;

• END

Подскажи пожалуйста что в этом коде означают "at" и "at."...
не могу никак разобрать. Спасибо

 

[doc]

Подскажи пожалуйста что в этом коде означают "at" и "at."...
не могу никак разобрать. Спасибо

at - это псведоним accounts_table (имя произвольное)
at.имя_столбца - это тоже самое, что просто имя_столбца, но с явным указанием какой таблице он принадлежит. В основном эта явность необходима, когда запрос работает сразу не несколькими таблицами, что не перепутать принадлежность