Как защитить работающий столбец в Oracle/PostgreSQL (что-то вроде блокировки MAX-результата или что-то в этом роде)

Оба Oracle и PostgreSQL поддерживает так называемые последовательности и идеально подходит для решения вашей задачи. Вы можете иметь обычный столбец int, но определить одну последовательность для каждой группы и выполнить один запрос, например

--PostgreSQL
insert into table (id, ... ) values (nextval(sequence_name_for_group_xx), ... )

--Oracle
insert into table (id, ... ) values (sequence_name_for_group_xx.nextval, ... )

Приращения в последовательностях являются атомарными, поэтому вашей проблемы просто не будет. Это всего лишь вопрос создания необходимых последовательностей, по одной на группу.

• значения счетчика или существующие строки иногда изменяются

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

Еще одним вариантом, вероятно, может быть ручная блокировка, хотя мне это не кажется приятным.

Ручная блокировка в этом случае довольно проста: просто возьмите SHARE UPDATE EXCLUSIVE или более сильную блокировку на столе, прежде чем выбирать максимальную. Однако это убьет одновременную производительность.

• иногда мне хотелось бы, чтобы было несколько «групп значений» счетчиков (как в случае с упомянутым КОДОМ): с разными значениями для КОДА счетчики были бы независимыми.

Это приводит меня к правильному решению этой проблемы: последовательности. Настройте несколько последовательностей, по одной для каждой «группы значений», для которой вы хотите получить идентификаторы в своем диапазоне. Подробную информацию о последовательностях и как их использовать; похоже, они идеально подходят для вас. Последовательности никогда не будут давать одно и то же значение дважды, но могут пропускать значения: если транзакция получает значение «2» из последовательности и прерывается, следующая транзакция получит значение «3», а не «2».

Последовательный ответ распространен, но может быть неправильным. Жизнеспособность этого решения зависит от того, что вам действительно нужно. Если то, что вы семантически хотите, это «какое-то гарантированно уникальное число», то для этого и нужна последовательность. Однако, если вы хотите убедиться, что ваше значение увеличивается ровно на единицу при каждой вставке (как вы просили), то НЕ ИСПОЛЬЗУЙТЕ ПОСЛЕДОВАТЕЛЬНОСТЬ! Я сам уже попал в эту ловушку. Последовательность не гарантируется! Они могут пропускать числа. В зависимости от того, какую оптимизацию вы настроили, они могут пропустить МНОГО чисел. Даже если у вас все настроено правильно, так что вы не должны пропустить ни одного числа, это не гарантируется, и последовательности не для этого. Таким образом, вы только напрашиваетесь на неприятности, если вы (неправильно) используете их таким образом.

Лучшее решение на один шаг — связать выбор со вставкой, например:

INSERT INTO table(code, c, ...) 
VALUES ('XX', (SELECT MAX(c) + 1 AS c FROM table WHERE code = 'XX'), ...);

(Я не тестировал этот запрос, но я почти уверен, что он должен работать. Мои извинения, если это не так.) Но выполнение чего-то подобного отражает семантическое намерение того, что вы пытаетесь сделать. Однако это неэффективно, потому что вам нужно выполнить сканирование для MAX, и вывод, который я делаю из вашего примера, заключается в том, что у вас есть небольшое количество значений кода по сравнению с размером таблицы, поэтому вы собираетесь сделать дорогое, полное сканирование таблицы при каждой вставке. Это нехорошо. Кроме того, это даже не дает вам гарантии ACID, которую вы ищете. Выбор не привязан транзакционно к вставке. Вы не можете «заблокировать» результат функции MAX(). Таким образом, у вас все еще может быть две транзакции, выполняющие этот запрос, и они обе выполняют подвыборку и получают одинаковое максимальное значение, обе добавляют одну, а затем обе пытаются вставить. Это гораздо меньшее окно, но технически у вас все еще может быть состояние гонки.

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

CREATE TABLE code_counts (
    code VARCHAR(2), --or whatever
    count NUMBER
);

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

UPDATE code_counts SET count = count + 1 WHERE code = 'XX' RETURNING count INTO :count;
INSERT INTO table(code, c, ...) VALUES ('XX', :count, ...);
COMMIT;

Суть в том, что обновление блокирует таблицу счетчиков и резервирует это значение для вас. Затем ваша вставка использует это значение. И все это фиксируется как одно транзакционное изменение. Вы должны сделать это в транзакции. Наличие отдельной таблицы подсчета позволяет избежать полного сканирования таблицы при выполнении SELECT MAX().... По сути, это повторно реализует последовательность, но также гарантирует вам последовательное, упорядоченное использование.

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

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

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

Редактировать: Извините, я не прочитал весь ваш вопрос. Как насчет решения ваших других проблем с нормализацией? Просто создайте вторую таблицу для каждого конкретного типа (для каждого x, где A = 'x'), где у вас есть еще один автоматический приращение. Последовательности, отредактированные вручную, могут быть другим столбцом в той же таблице, которая использует сгенерированную последовательность в качестве основы (т. е. если pk = 34, у вас может быть другой столбец mypk='34Changed').

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

Во-первых, вы должны создать счетчик последовательности:

CREATE SEQUENCE SEQ_TABLE_1 START WITH 1 INCREMENT BY 1;

Таким образом, вы можете использовать его как значение по умолчанию:

CREATE TABLE T (
  COD NUMERIC(10)  DEFAULT NEXTVAL('SEQ_TABLE_1') NOT NULL,
  collumn1...
  collumn2...
);

Теперь вам не нужно беспокоиться о последовательности вставки строк:

INSERT INTO T (collumn1, collumn2) VALUES (value1, value2);

С Уважением.