Вопрос: SQL - одновременная вставка ключей в таблицу


Вероятно, тривиальный вопрос, но я хочу получить наилучшее решение.

Проблема:

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

  1. Рабочий A читает таблицу, если существует ключ (SELECT). Нет ключа.
  2. Рабочий B читает таблицу, если существует ключ (SELECT). Нет ключа.
  3. Рабочий А вставляет ключ.
  4. Рабочий B вставляет ключ.
  5. Рабочий А совершает.
  6. Рабочий B совершает. Исключение выбрасывается по мере нарушения уникального ограничения

Ключевыми таблицами являются простые пары. Первый столбец является целым числом автоинкремента, а второй - ключ varchar.

Какое лучшее решение такой проблемы параллелизма. Я считаю, что это обычная проблема. Один из способов - обработать исключение, но почему-то я не считаю, что это лучший способ справиться с этим.

База данных, которую я использую, если Firebird 2.5

РЕДАКТИРОВАТЬ :

Некоторая дополнительная информация, чтобы все было ясно.

  1. Синхронизация на стороне клиента не является хорошим подходом, поскольку вставки происходят из разных процессов (рабочих). И когда-нибудь я мог бы иметь рабочих на разных машинах, поэтому даже мьютексы - это не-хот.
  2. Первичный ключ и первые столбцы такой таблицы - поле автоинкремента. Нет проблем. Поле varchar является проблемой, поскольку это то, что клиент вставляет.

Типичная такая таблица - таблица пользователей. Например:

1 2056
2 1044
3 1896
4 5966
...
 

Каждый рабочий проверяет, существует ли пользователь «xxxx», и если он не вставляет его.

EDIT 2 :

Только для справки, если кто-то пойдет по тому же маршруту. IB / FB возвращает пару кодов ошибок (я использую компоненты InterBase Express). Проверка дублирования значения выглядит следующим образом:

except
  on E: EIBInterBaseError do
  begin
    if (E.SQLCode = -803) and (E.IBErrorCode = 335544349) then
    begin
      FKeysConnection.IBT.Rollback;
      EnteredKeys := False;
    end;
  end;
end;

3


источник


Ответы:


С Firebird вы можете использовать следующую инструкцию:

UPDATE OR INSERT INTO MY_TABLE (MY_KEY) VALUES (:MY_KEY) MATCHING (MY_KEY) RETURNING MY_ID
  • предполагая, что существует BEFORE INSERT триггер, который будет генерировать MY_ID, если вставляется значение NULL.

Здесь документация ,

Обновить : Вышеприведенное заявление избежит исключений и приведет к успешному завершению каждого утверждения. Однако, в случае многих повторяющихся значений ключа, это также вызовет множество ненужных обновлений. Этого можно избежать с помощью другого подхода: просто обработайте исключительное исключение ограничения на клиенте и проигнорируйте его. Детали зависят от того, какую библиотеку Delphi вы используете для работы с Firebird, но должно быть возможно изучить SQLCode, возвращенный сервером, и игнорировать только конкретный случай уникального нарушения ограничений.


6



Я не знаю, доступно ли что-то подобное в Firebird, но в SQL Server вы можете проверить при вставке ключа.

insert into Table1 (KeyValue) 
select 'NewKey'
where not exists (select *
                  from Table1
                  where KeyValue = 'NewKey')

2



Первый вариант - не делайте этого.

Не делай этого; Если WORKERS не выполняют необычайную работу (мы говорим о компьютерах, поэтому требование 1 секунды за запись квалифицируется как «необычайный объем работы»), просто используйте один поток; Еще лучше, сделайте всю работу в хранимой процедуре, вы будете поражены ускорением, полученным за счет переноса данных по любому протоколу в ваше приложение.

Второй вариант - использование очереди

Убедитесь, что ваши рабочие потоки не работают на одном ID. Настройте очередь, нажмите все идентификаторы, которые нуждаются в обработке в этой очереди, каждый рабочий поток Отмените идентификатор из этой очереди. Таким образом, вам гарантировано, что в одной и той же записи одновременно не работают два работника. Это может быть сложно реализовать, если ваши работники не являются частью одного и того же процесса.

Крайнее средство

Настройте систему резервирования на базе БД, чтобы рабочий поток мог отметить ключ для «работы в процессе», чтобы ни один из двух рабочих не работал над одним и тем же ключом. Я бы установил таблицу следующим образом:

CREATE TABLE KEY_RESERVATIONS (
  KEY INTEGER NOT NULL, /* This is the KEY you'd be reserving */
  RESERVED_UNTIL TIMESTAMP NOT NULL /* We don't want to keep reservations for ever in case of failure */
);

Каждый из ваших работников будет использовать короткие транзакции для работы над этой таблицей: выберите ключ-кандидат, который не находится в таблице KEY_RESERVATIONS. Попробуйте ВСТАВИТЬ. Не удалось? Попробуйте другой КЛЮЧ. Периодически удаляйте все зарезервированные ключи со старыми временными метками RESERVED_UNTIL. Убедитесь, что транзакции для работы с KEY_RESERVATIONS настолько коротки, насколько это возможно, так что два потока, одновременно пытающиеся зарезервировать один и тот же ключ, будут работать быстро.


2



Это то, с чем вам приходится иметь дело в оптимистической (или нет) схеме блокировки.

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

Однако это означает, что вам придется иметь дело с тем, что вы не можете получить доступ к таблице (обрабатывать исключения с блокировкой таблицы).

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

Комбинация этих двух команд необходима, если у вас несколько экземпляров пользователей / приложений с несколькими потоками.


1



Синхронизируйте свои потоки, чтобы сделать невозможным вставить одно и то же значение или использовать метод генерации боковых ключей db (я не знаю Firebird, поэтому я даже не знаю, есть ли там, например, на MsSQL Server есть столбец идентификаторов или идентификаторы GUID решить проблему, потому что вряд ли она будет генерировать две идентичные)


0



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

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

Дополнительная информация о правильной реализации autoinc в Firebird здесь: http://www.firebirdfaq.org/faq29/


0