SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 15.09.2011 08:31:50

Jura_K
Участник
Зарегистрирован: 15.09.2011
Сообщений: 6

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

Итак поставлена задача
Необходимо получить нарастающую последовательность номера
по таблице 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 задвоение на эту же тысячу.

Неактивен

 

#2 15.09.2011 09:54:44

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

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

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

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

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

Неактивен

 

#3 15.09.2011 15:27:46

Jura_K
Участник
Зарегистрирован: 15.09.2011
Сообщений: 6

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

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

 
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';

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

Неактивен

 

#4 15.09.2011 17:10:57

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

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

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

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

Неактивен

 

#5 16.09.2011 07:08:51

Jura_K
Участник
Зарегистрирован: 15.09.2011
Сообщений: 6

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

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

 
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';

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

Неактивен

 

#6 16.09.2011 07:59:27

Jura_K
Участник
Зарегистрирован: 15.09.2011
Сообщений: 6

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

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

Неактивен

 

#7 16.09.2011 08:03:50

Jura_K
Участник
Зарегистрирован: 15.09.2011
Сообщений: 6

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

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

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)

Неактивен

 

#8 16.09.2011 10:08:43

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

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

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

Неактивен

 

#9 16.09.2011 10:54:28

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson