Форум пользователей MySQL

Jura_K · 15.09.2011 08:31:50

Итак поставлена задача
Необходимо получить нарастающую последовательность номера
по таблице InnoDB, при этом не должно быть пропусков номеров и ни в коем случае не должно быть задвоений. (При вставке новой записи с новым GroupID должна получить новый номер +1)

CREATE TABLE T1(

  PNum INT(11) UNSIGNED DEFAULT NULL,

  ID VARCHAR(25) NOT NULL DEFAULT '',

  GroupID VARCHAR(25) DEFAULT NULL

  PRIMARY KEY (ID),

  INDEX GroupID (GroupID),

  INDEX PNum (PNum)

)

ENGINE = INNODB

CHARACTER SET cp1251

COLLATE cp1251_general_ci;

Как бы это получить без существенного снижения производительности.
Т.е. я делаю триггер примерно следующего вида.

CREATE

DEFINER = 'root'@'localhost'

TRIGGER T1_before_insert

BEFORE INSERT

ON T1

FOR EACH ROW

BEGIN

  DECLARE NumUD  INT;

    SELECT  MAX(PNum) + 1  INTO  Num_D   FROM   T1;

    IF isnull(Num_D) THEN

      SET Num_D =1;

    END IF;

    SET NEW.PNum = Num_D;

  END IF;

END

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

Думал на тему лочить переменную со вставляемым номером, а вторая запись будет при неудачном Get_lock() по циклу наращивать номер до того времени пока не получит блокировку, но при Rollback первой записи будет появляться пропуск.
Сделать
SELECT MAX(PNum) + 1 INTO Num_D FROM T1 FOR UPDATE;
Тоже вроде как нет гарантии что после получения максимального значений кто нибудь его еще раз не получит до окончания вставки первой записи.
Подскажите в какую сторону смотреть ?
В крайнем случае лучше получить 1 пропуск на 1000 записей чем 1 задвоение на эту же тысячу.

paulus · 15.09.2011 09:54:44

А какая разница — есть пропуски или нет?

С Вашим алгоритмом Вам надо сериализовывать все данные. То есть по сути получается
однопоточное приложение. Будет очень низкая производительность, причем не понятно,
для чего.

Можно, например, выполнить SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, и после
этого у Вас будут подходящие условия для того, чтобы не было пропусков (подозреваю,
что и в случае обычного AUTO_INCREMENT). Но только ждите низкой производительности
и жестких блокировок везде.

Jura_K · 15.09.2011 15:27:46

Ну хорошо
А если сделать после того как произведена вставка
проверку на дубль

SELECT DISTINCT GroupID FROM T1 WHERE Pnum=Полученный_номер;
и если будет возвращено количество записей больше 1
то делаем

UPDATE T1,

  (SELECT max(PNum)+1 AS maxNum

   FROM

     T1) AS tmp_t1

SET

  T1.PNum = maxNum

WHERE

  T10.ID = 'Значение_ID';

Как этот вариант ?

paulus · 15.09.2011 17:10:57

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

Уникальность данных в столбце обеспечивается уникальным индексом над этим
столбцом. Смысла в такой проверке нет. А вот то, что Вы не ответили на вопрос, —
неприятно

Jura_K · 16.09.2011 07:08:51

Ну хорошо
А если сделать после того как произведена вставка
проверку на дубль

SELECT DISTINCT GroupID FROM T1 WHERE Pnum=Полученный_номер;
и если будет возвращено количество записей больше 1
то делаем

UPDATE T1,

  (SELECT max(PNum)+1 AS maxNum

   FROM

     T1) AS tmp_t1

SET

  T1.PNum = maxNum

WHERE

  T10.ID = 'Значение_ID';

Как этот вариант ?

Jura_K · 16.09.2011 07:59:27

Прошу прощения за то, что не ответил на вопрос.
Сам раньше скептически относился к подобного рода идеям,
Т.е. всегда говорил какая разница есть пропуск или нету, но вот в один прекрасный момент решили - что программа должна сама присваивать номера документам и они должны быть без пропусков и конечно же без задвоения.
Раньше человек сидел и записывал номер в журнал (регистрировал документ и после этого запись вводилась в БД). Причем журналы разные и в каждый журнал регистрировался свой тип документа присваивался свой порядковый номер + предикат символьное значение.
И вот заказчик выдает такое требование, номера должны присваиваться автоматически и после этого карточка на документ распечатываться и записываться в журнал.(В таблице у меня и хранится этот номер в типе VARCHAR и там делается этот предикат, пример привел максимально упростив, дабы получить направление).
В последствии на присвоенный номер делается куча ссылок т.е. заполняются другие документы.
Так вот заказчику доказать что так не должно быть - потому что так не делают очень и очень сложно. Тем более он хочет постепенно отказываться от журнала (а сейчас все как было, но плюс система должна выдавать этот номер).
Вводить в базу записи хотят в многопользовательском режиме (поэтому однопоточный вариант пока не рассматриваем).
Итак это все предыстория.
Решение сделал так:
Первоначальное присвоение номера делается на триггере.
После COMMIT
Запускается хранимая функция, которой передается идентификатор записи.
Хранимая функция по ID записи проверяет на возможный дубль номера, если дубль найден по циклу делает UPDATE номера, на следующий максимальный номер. И возвращает найденный номер, либо при отсутсвии дублей возвращает присвоенный триггером номер.
Я понимаю что в принципе даже в этом случае возможен обрыв между COMMIT и вызовом этой функции.
Но количество записей в один журнал не превышает 100 в день, т.е. случайность одномоментного ввода двух карточек в базу мала, тем более я вызовом функции стараюсь ее еще уменьшить в несколько раз.
Хотел бы услышать замечания по предложенной реализации с учетом того, что в настоящее время изменить идеологию получения этого номера не получится.

Jura_K · 16.09.2011 08:03:50

По поводу индекса.
Индекс тут не поможет

PNum GroupID ID
1 - X1 - 123
1 - X1 - 124
1 - X1 - 125
2 - O1 - 126
2 - O1 - 127
3 - Y1 - 128
3 - Y1 - 129
3 - Y1 -130
все указанное выше правильно (для системы),
теперь вставляется ошибочный номер (тот который уже есть но с другим GroupID)
2 - N1 - 131
Вот я не вижу какой идекс не даст этого сделать даже составной.
На вопрос почему по GroupID повторяются Pnum объясню - ведется история корректировки. (номер и служебные поля не корректирвуются, однако могут корректироваться другие поля)

Отредактированно Jura_K (16.09.2011 08:05:25)

evgeny · 16.09.2011 10:08:43

Расскажите чем вам обычный AUTO_INCREMENT не подходит ?

paulus · 16.09.2011 10:54:28

Индекс уникальный на (ID) решает эту проблему. Задача с пропусками у Вас
надуманная: просто вставляйте данные и не усложняйте себе жизнь. Шанс, что
одновременно два человека создадут документ, а потом еще и откатят один
из них — нулевой. В качестве дополнительной страховки — не давайте откатывать
документы

Форум пользователей MySQL

#1 15.09.2011 08:31:50

Нарастающая последовательность

#2 15.09.2011 09:54:44

Re: Нарастающая последовательность

#3 15.09.2011 15:27:46

Re: Нарастающая последовательность

#4 15.09.2011 17:10:57

Re: Нарастающая последовательность

#5 16.09.2011 07:08:51

Re: Нарастающая последовательность

#6 16.09.2011 07:59:27

Re: Нарастающая последовательность

#7 16.09.2011 08:03:50

Re: Нарастающая последовательность

#8 16.09.2011 10:08:43

Re: Нарастающая последовательность

#9 16.09.2011 10:54:28

Re: Нарастающая последовательность

Board footer