Получите уникальный порядковый номер последовательности одновременно из PostgreSQL
Мы разрабатываем систему управления заказами, идентификатор заказа спроектирован как bigint с Postgresql, а структура размещения реализована следующим образом:
Возьмем 2015072201000010001 в качестве примера идентификатора заказа, первые восемь мест считаются датой, которая здесь равна 20150722, следующие семь мест считаются кодом региона, который здесь равен 0100001, а последние четыре места относятся к порядковому номеру, указанному вышеупомянутым. регион и дата.
Таким образом, каждый раз, когда создается новый порядок, прикладной уровень логики php будет запрашивать PostgreSQL с помощью следующего оператора sql:
select id from orders where id between 2015072201000010000 and 2015072201000019999 order by id desc limit 1 offset 0
затем увеличьте идентификатор для нового заказа, после этого вставьте заказ в базу данных PostgreSQL.
Это нормально, если одновременно выполняется только один процесс генерации заказа. Но при сотнях одновременных запросов на генерацию ордеров существует много шансов, что идентификаторы ордеров будут конфликтовать, поскольку механизм блокировки чтения / записи базы данных PostgreSQL.
Допустим, есть два запроса заказа A и B. A пытается прочитать последний идентификатор заказа из базы данных, затем B также читает последний идентификатор заказа, затем A записывает данные в базу данных, и, наконец, B записывает в базу данных неудачно, так как первичный ключ идентификатора заказа сталкивается.
Любые мысли о том, как сделать это действие генерации заказа одновременно осуществимым?
Решение
В случае многих одновременных операций, ваш единственный вариант — работать с последовательностями. В этом сценарии вам нужно будет создать последовательность для каждой даты и региона. Это звучит как большая работа, но большая часть может быть автоматизирована.
Создание последовательностей
Вы можете назвать свои последовательности после даты и региона. Так что сделайте что-то вроде:
CREATE SEQUENCE seq_201507220100001;
Вы должны создать последовательность для каждой комбинации дня и региона. Сделайте это в функции, чтобы избежать повторения. Запускайте эту функцию один раз в день. Вы можете сделать это заранее или, что еще лучше, делать это по расписанию ежедневно, создавая последовательности завтрашнего дня. Предполагая, что вам не нужно возвращать заказы в предыдущие дни, вы можете отбросить вчерашние последовательности в той же функции.
CREATE FUNCTION make_and_drop_sequences() RETURNS void AS $$
DECLARE
region text;
tomorrow text;
yesterday text;
BEGIN
tomorrow := to_char((CURRENT_DATE + 1)::date, 'YYYYMMDD');
yesterday := to_char((CURRENT_DATE - 1)::date, 'YYYYMMDD');
FOREACH region IN
SELECT DISTINCT region FROM table_with_regions
LOOP
EXECUTE format('CREATE SEQUENCE %I', 'seq_' || tomorrow || region);
EXECUTE format('DROP SEQUENCE %I', 'seq_' || yesterday|| region);
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Используя последовательности
В вашем PHP-коде вы, очевидно, знаете дату и регион, для которого необходимо ввести новый идентификатор заказа. Создайте другую функцию, которая генерирует новое значение из правой последовательности на основе даты и региона:
CREATE FUNCTION new_date_region_id (region text) RETURN bigint AS $$
DECLARE
dt_reg text;
new_id bigint;
BEGIN
dt_reg := tochar(CURRENT_DATE, 'YYYYMMDD') || region;
SELECT dt_reg::bigint * 10000 + nextval(quote_literal(dt_reg)) INTO new_id;
RETURN new_id;
END;
$$ LANGUAGE plpgsql STRICT;
В PHP вы тогда вызываете:
SELECT new_date_region_id('0100001');
который даст следующий доступный идентификатор для указанного региона на сегодня.
Другие решения
Обычный способ избежать блокировки идентификаторов в Postgres — через последовательности.
Вы можете использовать последовательности Postgresql для каждого региона. Что-то вроде
create sequence seq_0100001;
тогда вы можете получить число с помощью:
select nextval('seq_'||regioncode) % 10000 as order_seq
Это означает, что номера заказов не будут сбрасываться в 0001 каждый день, но у вас есть тот же диапазон 0000 -> 9999 для номеров заказов. Это обернется.
Таким образом, вы можете в конечном итоге:
2015072201000010001 -> 2015072201000017500
2015072301000017501 -> 2015072301000019983
2015072401000019984 -> 2015072401000010293
В качестве альтернативы вы могли бы просто сгенерировать последовательность для каждой комбинации дня / региона, но вам нужно было бы отказаться от последовательностей предыдущих дней в начале следующего дня.
Попробуйте использовать тип UUIDv1, который представляет собой комбинацию метки времени и адреса MAC. Вы можете автоматически генерировать его на стороне сервера, если для вас важен порядок вставок. В противном случае идентификаторы могут быть сгенерированы на любом из ваших клиентов перед вставкой (вам может понадобиться синхронизировать их часы). Просто помните, что с помощью UUIDv1 вы можете раскрыть MAC-адрес хоста, на котором был сгенерирован UUID. В этом случае вы можете подделать MAC-адрес.
Для вашего случая вы можете сделать что-то вроде
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuid_generate_v1(),
created_at timestamp NOT NULL DEFAULT now(),
region_code text NOT NULL REFERENCES...
...
);
Узнайте больше на http://www.postgresql.org/docs/9.4/static/uuid-ossp.html