Объединение SELECT и INSERT в один запрос

[V.Terentev]

Объединение SELECT и INSERT в один запрос

Добрый день.

Возникла такая ситуация:
Есть часто вызываемый скрипт, в нём выполняется проверка наличия данных в таблице с помощью SELECT и по результатам этой проверки выполняется или нет INSERT в эту же таблицу. При частом вызове скрипта иногда возникает ситуация, когда 2 вызова происходят одновременно настолько, что SELECT второго происходит до INSERT первого и данные добавляются дважды. Даже если выполнять запросы последовательно одна строка за другой - задержки всё равно хватает чтобы иногда ловить одинаковые запросы.
Лочить таблицу нельзя. Сделать проверяемый флаг уникальным ключём нельзя. Версия MySQL - 4.0.26, в ней не поддерживаются вложенные SELECTы. Обновить MySQL также нельзя.

Знатоки MySQL, посоветуйте пожалуйста возможный синтаксис такого запроса для версии MySQL 4.0.26, если это вообще возможно.

 

[fixxxer]

http://dev.mysql.com/doc/refman/4.1/en/insert-select.html

-~{}~ 11.04.10 23:43:

а, в эту же таблицу.

ну например, можно придумать "искусственный" unique и использовать insert ignore/on duplicate key.../replace смотря что подойдет. а вообще, создается впечатление, что там не в порядке с архитектурой.

 

[V.Terentev]

А нет ли в старых версиях MySQL какого-нибудь универсального решения для объединения операции INSERT или UPDATE с условием - если условие верно, выполняем; нет - не выполняем?

Архитектура БД здесь не поддаётся никакому редактированию, т.к. это дополнение к существующей и довольно старой системе.

Общая картина примерно такая:
Запрос 1: "SELECT count(payment_id) FROM payments WHERE description = '".$description."' AND pay_number = '".$pay_number."'"
if(результат запроса 1 == 0, т.е. такой записи нет) {
Запрос 2: "UPDATE accounts SET money='".$new_money."' WHERE account_id='".$account_id."'"
Запрос 3: "INSERT INTO payments (description, pay_number, ...) VALUES (".$description.", ".$pay_number.", ...)"
}

 

[prolis]

Re: Объединение SELECT и INSERT в один запрос

Автор оригинала: V.Terentev
При частом вызове скрипта иногда возникает ситуация, когда 2 вызова происходят одновременно настолько, что SELECT второго происходит до INSERT первого и данные добавляются дважды. Даже если выполнять запросы последовательно одна строка за другой - задержки всё равно хватает чтобы иногда ловить одинаковые запросы.

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

-~{}~ 12.04.10 11:30:

Автор оригинала: V.Terentev
Запрос 2: "UPDATE accounts SET money='".$new_money."' WHERE account_id='".$account_id."'"
Запрос 3: "INSERT INTO payments (description, pay_number, ...) VALUES (".$description.", ".$pay_number.", ...)"
}

в чем смысл платежи начислять не по событию?

 

[V.Terentev]

Re: Re: Объединение SELECT и INSERT в один запрос

Автор оригинала: prolis
-селектоинзерт тоже бы тупил. Надо смотреть, почему селект так долго выполняется.
в чем смысл платежи начислять не по событию?

Это обычный механизм работы с платёжной системой - скрипт платёжной системы вызывает этот скрипт, который выполняет проверки и проводит платёж. Суть не в том, что селект выполняется долго, хотя он выполняется действительно сравнительно долго из-за огромных размеров базы, а в том, что платёжная система может послать 2 одинаковых запроса фактически в 1 миллисекунду из-зи особенностей её работы. И между такими запросами происходят гонки.

Хотелось бы переложить проверку на сторону сервера MySQL чтобы минимизировать или свести на нет задержку между проверкой и выполнением UPDATE и INSERT.

Вопрос в том, нет ли в старых версиях MySQL какого-нибудь универсального решения для объединения операции INSERT или UPDATE с условием - если условие верно, выполняем; нет - не выполняем?

Модет есть механизм каких-то встроенных функций, тогда как его использовать через PHP? Я не очень хорошо знаком с тонкостями MySQL, поэтому прошу помощи.

 

[Gas]

pay_number уникальных индекс? (в любом случае должен таким быть). Делаем insert (не нужно select делать) - если true, то update, если false - ничего не делаем.

 

[prolis]

Re: Re: Re: Объединение SELECT и INSERT в один запрос

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

 

[dimagolov]

Сделать проверяемый флаг уникальным ключём нельзя. Версия MySQL - 4.0.26, в ней не поддерживаются вложенные SELECTы. Обновить MySQL также нельзя.

Знатоки MySQL, посоветуйте пожалуйста возможный синтаксис такого запроса для версии MySQL 4.0.26, если это вообще возможно.

оригинальный набор ограничений, особенно с учетом того, что текущая архитектура неправильно считает ДЕНЬГИ.

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

 

[V.Terentev]

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

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

В любой задаче нужно убедится что выбранное решение самое простое из подходящих. Всё бы было очень просто, если бы была возможность именно объединить SELECT, UPDATE и INSERT в одном запросе. Вроде MySQL-процедуры. Возможно ли это в 4.0.26 ?

 

[dimagolov]

V.Terentev, если есть транзакции, то можно. хотя непонятно требование "Лочить таблицу нельзя" и тип таблицы.

 

[Gas]

pay_number не уникальный индекс...идентификаторы разных платёжных систем

а не в поле description лежит какая именно это платёжная система? должна же быть в этой таблице связка полей, которая однозначно идентифицирует что это за счёт (номер + платёжная система), тогда на эту связку уникальный индекс ставить.

V.Terentev, если есть транзакции, то можно.

а как именно они помогут, for update не поможет (записи то в базе может не быть)?

 

[Fortop]

Gas
Делаем insert (не нужно select делать) - если true, то update, если false - ничего не делаем.

Я бы все же сначала сделал select, а потом решал что делать insert или update

 

[dimagolov]

а как именно они помогут

действительно, только уникальный индекс по description и pay_number поможет. такое изменение структуры можно сделать без каких-то затруднений.
даже если делать проверку, что после insert не появилось дубликата и делать rollback, то будем иметь шанс откатить обе конкурирующие транзакции.

 

[V.Terentev]

Ок. Всем большое спасибо за ответы. Придётся работать через буферную таблицу - по факту платежа записывать в отдельную таблицу с нужной структурой, которую можно лочить. А, скажем, в конце дня сливать в таблицу основной системы.

 

[fixxxer]

а чем не подходит unique(payment_system_id, payment_id)?

 

[V.Terentev]

Имеющуюся таблицу вообще нельзя никак трогать. Это часть готового биллинга - невозможно прогнозировать последствия изменений структуры.

 

[dimagolov]

Имеющуюся таблицу вообще нельзя никак трогать. Это часть готового биллинга - невозможно прогнозировать последствия изменений структуры.

Логика не просматривается. Есть доказанная дырка, которая приводит к некорректному подсчету денег. Но сделать изменение (которое можно протестировать на копии, и которое никак не изменит поведение системы кроме как закроет дырку, при попытке внести дубль будет возвращаться ошибка) нельзя. Потому что кто-то что-то собрался прогнозировать, а на это не способен.

Кроме того V.Terentev, Вы все время употребляете термин "лочить", что говорит о том, что Вы не понимаете что предлагается сделать для решения проблемы. Однако цирк в том, что никакое другое решение (кроме уникального индекса) не поможет. Потому что буферная таблица не даст гарантии уникальности записей, потому что возникнет проблема с определением какие данные нужно, а какие нет сливать в проблемную таблицу.

 

[V.Terentev]

Есть старая архаичная система - биллинг. В его таблицах масса важных данных. Часть его исходников закрыта. Сделать пару номер-описание уникальным ключём в его таблице нельзя, т.к. структура биллинга не гарантирует уникальности этой пары - бывают и другие платежи, внутренние, где эта пара вообще никак не регламентирована. Эти модули взаимодействия с платёжными системами - гораздо более позднее дополнение.

"Лочить" употреблялось в применении к решению с использованием буферной таблицы. В начале выполнения скрипта плятёжной системы буферная таблица будет залочена и "второй" запрос не сможет выполнится как бы одновременно он не пришёл с первым. Гонка запросов закончится на этапе лока таблицы. Второй будет отбит. При сливе в старую таблицу уникальность уже будет гарантирована.

 

[dimagolov]

При сливе в старую таблицу уникальность уже будет гарантирована.

во-первых можно таблицу не лочить, а сделать уникальный индекс с тем же результатом.
во-вторых непонятно, почему нельзя таблицу лочить, это требование никак не обосновывали. хорошо бы привести структуру обращений к этой таблице (сколько select, insert & update и как часто)
во-третьих, в буферной таблице будет храниться полная копия основной таблицы включая primary key? то есть вставка в основную производиться не будет никак иначе кроме как через буферную? иначе ведь гарантировать уникальность пары невозможно.
ну и последнее:

структура биллинга не гарантирует уникальности этой пары - бывают и другие платежи, внутренние, где эта пара вообще никак не регламентирована

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

 

[fixxxer]

Ну если есть какое-то говнолегаси, сделанное через зад, его нельзя трогать, да еще и работа с финансами, то тут уж фактическая работа со своей базой и экспорт-импорт в эту легаси-фигню просто единственное разумное решение.