Как синхронизировать выгрузку данных из приложения на удаленный сервер MySQL?

Question

[Иван Мельников]

Есть таблица

CREATE TABLE t1(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
link CHAR(36) NOT NULL UNIQUE
);

Внешнее приложение (1С:Предприятие) пишет в эту таблицу через функцию:

CREATE FUNCTION insert_t1 (_link char(36)) RETURNS INT UNSIGNED
BEGIN
INSERT INTO t1(link) VALUE(_link);
RETURN LAST_INSERT_ID();
END

Эта функция возвращает сгенерированный id. Проблема в том, что иногда случается так, что запись в таблицу делается, а приложение не получает обратно сгенерированный id. В итоге, приложение пытается повторно сделать запись, из-за чего возникает ошибка из-за дубля уникального ключа.
Как избежать таких неприятностей? С помощью транзакции? Но, как я понимаю, она тут не поможет. Возвращаемый id после успешного выполнения транзакции может потеряться где-нибудь в сети и не дойти до приложения. Или я ошибаюсь?

Answer 1

[Rsa97]

Вставляйте через INSERT IGNORE, тогда не будет дублирования. Затем делайте SELECT по link и возвращайте полученный id.

Comments

[Иван Мельников]

А транзакция тут не поможет?

[Rsa97]

Иван Мельников, А смысл? INSERT IGNORE - операция атомарная, если поле link уникально, то SELECT вернёт id единственной записи с таким значением.

[Иван Мельников]

Rsa97, как я понял, вы предлагаете следующую функцию:

CREATE FUNCTION insert_t1 (_link char(36)) RETURNS INT UNSIGNED
BEGIN
DECLARE _id INT UNSIGNED;
INSERT IGNORE INTO t1(link) VALUE(_link);
SELECT id FROM t1 INTO _id WHERE link = _link;
RETURN _id;
END

Но как я понимаю, при каждом вызове этой функции придется делать SELECT по всей таблице, а это уже на порядки снижает скорость записи. В имеющейся таблице уже есть порядка 10 млн. записей и нужно записать в неё ещё около 5 млн. Или может можно сделать, чтобы SELECT вёл поиск с конца таблицы к её началу и тогда уже на 1-2-й строке он найдет сделанную запись?

[Rsa97]

Иван Мельников, У вас создан индекс UNIQUE по link, значит поиск будет быстрым, по индексу.

[Иван Мельников]

Rsa97, и всё же, можно ли решить данную задачу с помощью транзакции или транзакция тут ничем не поможет?

[Rsa97]

Иван Мельников, Транзакция тут бесполезна. Она нужна, в первую очередь, когда есть несколько команд, последовательно вставляющих/изменяющих данные, для сохранения связности данных в случае ошибки в одной из команд.

Answer 2

[Виталий Архипов]

Генерируйте уникальный ключ на клиенте, с сервера получайте не id, а подтверждение записи. Если получено не было, отправляйте запрос заново, но уже что то вроде insert or select, а сервер пусть возвращает успех если запись найдена или пытается записать.
Для ключа сделайте специальное поле в таблице с UNIQUE INDEX.