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

IgorSush · 18.04.2017

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

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

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 · 18.04.2017

Я эмулирую работу со списком, взяли первую строку " 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 в первом запросе'

Koqpe · 18.04.2017

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

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

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

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

Koqpe · 18.04.2017

IgorSush · 18.04.2017

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

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

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

kfil · 23.10.2017

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;

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

Код:

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 · 23.10.2017

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

kfil · 23.10.2017

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

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

progrlab · 03.11.2017

kfil сказал(а):
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 · 04.11.2017

progrlab сказал(а):
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 · 09.11.2017

Спасибо за подсказки и идеи.
Сделал так :
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 · 10.01.2018

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

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

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

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

Gfoblin · 10.01.2018

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

proffman · 10.01.2018

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

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

Gfoblin · 10.01.2018

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

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

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

progrlab · 11.01.2018

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

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

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

workoles · 03.02.2018

progrlab сказал(а):
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 · 03.02.2018

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

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

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

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Client

Кто просматривает тему: (Всего: 1, Пользователи: 0, Гости: 1)