Проблема SQL при объединении двух баз данных с внешними ключами

Question

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

Все работает хорошо, но моя проблема заключается в следующем: например, есть 2 таблицы Stock а также Orders, в Orders есть столбец, который является stock_id,

Если в рабочем пространстве A создается новый "Stock X" с автоматически увеличенным "stock_id" =23, а в рабочем пространстве B создается новый "Stock Y" с автоматически увеличивающимся "stock_id" =23, то в рабочее пространство B добавится "Stock X" рабочей области A и рабочая область B добавит "Stock Y" рабочей области B, но у каждой будет свой идентификатор в каждой базе данных.

Проблема возникает, когда рабочая область A делает заказ на stock_id=23, который равен "Stock X", когда этот запрос отправляется в центральную базу данных, а затем отправляется в рабочую область B, он вставляет заказ, но stock_id=23 будет ссылаться на " Сток Y".

Я был бы очень признателен за помощь в этом спасибо:)

Answer 1

Редакция:

Ваш оригинальный дизайн используется AUTO INCREMENT столбцы для первичного ключа. Проблема в том, что, как вы видите, когда ваши данные одновременно попадают в отдельные базы данных, которые в конечном итоге будут объединены, вы в конечном итоге создаете дубликаты ключей.

Один альтернативный вариант - использовать последовательность в каждой отдельной базе данных. К сожалению, последовательности не доступны в mysql (многие другие базы данных, такие как Oracle, db2, имеют их - они допускают вставки в распределенную базу данных без коллизий). AUTO INCREMENT столбцы не допускают тот тип репликации, который вы хотите.

Итак, у вас есть два варианта.

1) Добавьте дополнительную часть первичного ключа, который устанавливает location_id (согласно первому ответу).

2) Или сгенерируйте свои идентификаторы для вставок вручную, используя собственную последовательность, а не используя AUTO INCREMENT колонны.

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

Главное, что когда вы делаете первую вставку, вы используете последовательность # из сохраненного процесса. Когда вы эффективно реплицируете данные во 2-й БД, вы используете сгенерированную последовательность # в строке исходного БД. И последовательность будет поддерживаться в каждом отдельном дБ с разными начальными точками для предотвращения столкновений.

Например, в каждой базе данных вы бы хотели две части:

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

Пример реализации будет:

Таблица последовательности:

CREATE TABLE sequences (
  name varchar(30) NOT NULL,
  value int(10) unsigned,
  PRIMARY KEY (name)
) ENGINE=InnoDB

Функция (ы) последовательности:

delimiter //
create function get_next_value(p_name varchar(30)) returns int
  deterministic
  sql security invoker
begin
  declare current_val integer;
    UPDATE sequences
    SET value = (@current_val:=value) + 1
    WHERE name = p_name;

  return @current_val;
end //
delimiter ;

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

Я не писал эту функцию - я свободно копирую ее отсюда: http://www.bigdbahead.com/?p=185 куда я бы вас отослал, для более подробной информации. (И если этот пользователь когда-нибудь найдет меня здесь, я с радостью позволю ему написать ответ и дать ему соответствующую оценку здесь).

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

insert into sequences ('orders', 1);

и в местоположении B вы бы инициализировали это с помощью:

insert into sequences ('orders', 1000000);

А потом в обеих базах данных, по вставке в ordersвы бы сделали:

insert into orders (order_id, . . .)
select mysql.get_next_value('user_id'), . . . <hardcoded-values>

-

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

Answer 2

Если каждое из рабочих пространств A и B имеет разные записи для идентификатора 23, тогда единственным вариантом будет создание вторичного ключа для каждой записи, когда записи обновляются в центральной БД. Этот ключ может быть затем возвращен обратно в базы данных рабочей области при их обновлении, что позволит получить доступ к их элементам с действительно уникальным идентификатором.

Однако я настоятельно рекомендую вам не использовать этот метод!

Правильный способ сделать это - создать веб-приложение или приложение для настольного компьютера, используемое в рабочих пространствах 1 и 2, которое будет подключаться к центральной базе данных и использовать его для любого доступа к данным. Хороший дизайн базы данных обычно означает наличие как можно меньшего числа дублирующих копий данных. Имея несколько копий заказов, запасов и т. Д., Копирующихся в трех разных базах данных, вы настраиваете себя на повреждение и / или потерю данных в будущем - проблему, которую будет становиться все труднее и труднее исправить по мере роста вашей базы данных. размер. Исправьте структурную проблему сейчас, прежде чем база данных станет больше!

Answer 3

Я думал об этом ответе, но я все еще смущен, если лучше пойти с ним, чем с другими решениями выше. Все запросы в рабочей области B выполняются непосредственно в локальной базе данных, а также отправляются в центральную базу данных, а затем отправляются в рабочую область A, однако запросы в рабочей области A не выполняются непосредственно в локальной базе данных, они отправляются в центральную базу данных, а затем в центральную базу данных. отправляет их в рабочую область B, и когда рабочая область B выполняет эти запросы, то она уведомляет центральное и затем уведомляется рабочая область A, затем она может выполнять запросы, которые были сохранены. Таким образом, рабочая область B может нормально выполнять свои запросы, но рабочая область A может выполнять свои запросы только тогда, когда ей известно, когда рабочая область B выполнила эти запросы, поскольку перед тем, как рабочая область B выполняет запросы A, она отправляет свою собственную новую запрашивает, а затем выполняет запросы A, поэтому после того, как A получает уведомление о том, что B выполнил свои запросы, он проверяет, отправил ли B какие-либо новые собственные запросы, и выполняет их, а затем выполняет свои собственные запросы. Таким образом, все автоматически введенные идентификаторы будут одинаковыми в обоих рабочих пространствах. Пример:

Рабочая область A:

         q1= insert into stock (name) values ('A')    not executed

         q2= insert into stock (name) values ('B')    not executed

База данных A: (ПУСТО)

Рабочая область A отправляет q1 и q2 в центральную станцию ​​и ожидает, когда центральная станция подтвердит, что B выполнил эти запросы, чтобы он мог выполнить их сам

Рабочая область B:

         q3= insert into stock (name) values ('C')    executed id=1

         q4= insert into stock (name) values ('D')    executed id=2

База данных B: (1, "C"), (2, "D")

Рабочая область B после отправки q3 и q4 в центральный офис уведомляется о q1 и q2,

Рабочая область B выполняет q1 и q2

          q1= insert into stock (name) values ('A')   executed id=3

          q2= insert into stock (name) values ('B')   executed id=4

База данных B: (1, "C"), (2, "D"), (3, "A"), (4, "B")

Рабочая область A уведомлена, что B выполнила свои q1 & q2, но ей сказали, что она должна выполнить q3 & q4, прежде чем она сможет выполнить q1 & q2

Рабочая область A:

          q3= insert into stock (name) values ('C')    executed id=1

          q4= insert into stock (name) values ('D')    executed id=2

          q1= insert into stock (name) values ('A')    executed id=3

          q2= insert into stock (name) values ('B')    executed id=4

База данных B: (1, "C"), (2, "D"), (3, "A"), (4, "B")