Я знаю, что здесь много. Я попытался довольно хорошо задокументировать это внутри кода и здесь и там. Он использует хранимые процедуры. Вы можете, естественно, вытащить код и не использовать этот метод. Он использует основную таблицу, в которой находятся следующие доступные инкременты. Он использует безопасные INNODB
блокировки намерений для параллелизма. У него есть таблица повторного использования и сохраненные процедуры для ее поддержки.
Он никоим образом не использует таблицу myTable
. Это показано там для вашего собственного воображения на основе комментариев под вашим вопросом. Резюме этого заключается в том, что вы знаете, что у вас будут пробелы на DELETE
. Вам нужен какой-то упорядоченный способ повторного использования этих слотов, этих порядковых номеров. Итак, когда вы DELETE
ряд, используйте сохраненные процедуры соответственно, чтобы добавить это число. Естественно, есть хранимая процедура для получения следующего порядкового номера для повторного использования и прочего.
В целях тестирования ваши sectionType
= 'устройства'
А лучше всего проверено!
Схема:
create table myTable
( -- your main table, the one you cherish
`id` int auto_increment primary key, -- ignore this
`seqNum` int not null, -- FOCUS ON THIS
`others` varchar(100) not null
) ENGINE=InnoDB;
create table reuseMe
( -- table for sequence numbers to reuse
`seqNum` int not null primary key, -- FOCUS ON THIS
`reused` int not null -- 0 upon entry, 1 when used up (reused)
-- the primary key enforces uniqueness
) ENGINE=InnoDB;;
CREATE TABLE `sequences` (
-- table of sequence numbers system-wide
-- this is the table that allocates the incrementors to you
`id` int NOT NULL AUTO_INCREMENT,
`sectionType` varchar(200) NOT NULL,
`nextSequence` int NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `sectionType` (`sectionType`)
) ENGINE=InnoDB;
INSERT sequences(sectionType,nextSequence) values ('devices',1); -- this is the focus
INSERT sequences(sectionType,nextSequence) values ('plutoSerialNum',1); -- not this
INSERT sequences(sectionType,nextSequence) values ('nextOtherThing',1); -- not this
-- the other ones are conceptuals for multi-use of a sequence table
Сохраненная процедура: uspGetNextSequence
DROP PROCEDURE IF EXISTS uspGetNextSequence;
DELIMITER $$
CREATE PROCEDURE uspGetNextSequence(p_sectionType varchar(200))
BEGIN
-- a stored proc to manage next sequence numbers handed to you.
-- driven by the simple concept of a name. So we call it a section type.
-- uses SAFE INNODB Intention Locks to support concurrency
DECLARE valToUse INT;
START TRANSACTION;
SELECT nextSequence into valToUse from sequences where sectionType=p_sectionType FOR UPDATE;
IF valToUse is null THEN
SET valToUse=-1;
END IF;
UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType;
COMMIT; -- get it and release INTENTION LOCK ASAP
SELECT valToUse as yourSeqNum; -- return as a 1 column, 1 row resultset
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspGetNextSequence('devices'); -- your section is 'devices'
После того, как вы вызовете uspGetNextSequence(), ваша ОТВЕТСТВЕННОСТЬ гарантирует, что эта последовательность #
либо добавляется в myTable
(путем подтверждения), либо в случае неудачи вы вставляете его в
таблицу повторного использования вызовом uspAddToReuseList(). Не все вставки успешны. Сосредоточьтесь на этой части.
Потому что с этим кодом вы не можете "поместить" его обратно в таблицу sequences
из-за
параллелизм, другие пользователи и уже пройденный диапазон. Итак, просто, если вставка не удалась,
поместите число в reuseMe
через uspAddToReuseList()
.
.
.
Сохраненная процедура: uspAddToReuseList:
DROP PROCEDURE IF EXISTS uspAddToReuseList;
DELIMITER $$
CREATE PROCEDURE uspAddToReuseList(p_reuseNum INT)
BEGIN
-- a stored proc to insert a sequence num into the reuse list
-- marks it available for reuse (a status column called `reused`)
INSERT reuseMe(seqNum,reused) SELECT p_reuseNum,0; -- 0 means it is avail, 1 not
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspAddToReuseList(701); -- 701 needs to be reused
Сохраненная процедура: uspGetOneToReuse:
DROP PROCEDURE IF EXISTS uspGetOneToReuse;
DELIMITER $$
CREATE PROCEDURE uspGetOneToReuse()
BEGIN
-- a stored proc to get an available sequence num for reuse
-- a return of -1 means there aren't any
-- the slot will be marked as reused, the row will remain
DECLARE retNum int; -- the seq number to return, to reuse, -1 means there isn't one
START TRANSACTION;
-- it is important that 0 or 1 rows hit the following condition
-- also note that FOR UPDATE is the innodb Intention Lock
-- The lock is for concurrency (multiple users at once)
SELECT seqNum INTO retNum
FROM reuseMe WHERE reused=0 ORDER BY seqNum LIMIT 1 FOR UPDATE;
IF retNum is null THEN
SET retNum=-1;
ELSE
UPDATE reuseMe SET reused=1 WHERE seqNum=retNum; -- slot used
END IF;
COMMIT; -- release INTENTION LOCK ASAP
SELECT retNum as yoursToReuse; -- >0 or -1 means there is none
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspGetOneToReuse();
Сохраненная процедура: uspCleanReuseList:
DROP PROCEDURE IF EXISTS uspCleanReuseList;
DELIMITER $$
CREATE PROCEDURE uspCleanReuseList()
BEGIN
-- a stored proc to remove rows that have been successfully reused
DELETE FROM reuseMe where reused=1;
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspCleanReuseList();
Сохраненная процедура: uspOoopsResetToAvail:
DROP PROCEDURE IF EXISTS uspOoopsResetToAvail;
DELIMITER $$
CREATE PROCEDURE uspOoopsResetToAvail(p_reuseNum INT)
BEGIN
-- a stored proc to deal with a reuse attempt (sent back to you)
-- that you need to reset the number as still available,
-- perhaps because of a failed INSERT when trying to reuse it
UPDATE reuseMe SET reused=0 WHERE seqNum=p_reuseNum;
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspOoopsResetToAvail(701);
Идеи рабочего процесса:
Пусть GNS означает вызов uspGetNextSequence()
.
Пусть RS означает последовательность повторного использования через вызов uspGetOneToReuse()
Если требуется новый INSERT
, вызовите RS:
A. Если RS возвращает -1, то повторно использовать ничего нельзя, поэтому вызовите GNS, который вернет N. Если вы можете успешно выполнить INSERT
с помощью myTable.seqNum=N
с подтверждением, все готово. Если вы не можете успешно INSERT
сделать это, позвоните uspAddToReuseList(N)
.
Б. Если RS возвращает > 0, мысленно отметьте, что слот имеет reuseMe.reused=1
, это полезно запомнить. Таким образом, предполагается, что он находится в процессе успешного повторного использования. Давайте назовем этот порядковый номер N. Если вы можете успешно INSERT
с myTable.seqNum=N
с подтверждением, все готово. Если вы не можете успешно INSERT
сделать это, позвоните uspOoopsResetToAvail(N)
.
Когда вы сочтете безопасным позвонить uspCleanReuseList()
, сделайте это. Добавление DATETIME
в таблицу reuseMe
может быть хорошей идеей, обозначающей, когда строка из myTable
изначально удалялась и приводила к тому, что строка reuseMe
получала исходное INSERT
.
person
Drew
schedule
10.09.2016
sequence_value
) и обновлять записи с заданным интервалом. Почему такой подход? Вам нужен только один процесс, выполняющий эту задачу, и событие — идеальный кандидат. Создание такого запроса должно быть тривиальным. Вы можете делать это каждые несколько минут, чтобы сэкономить несколько ресурсов (поэтому вы не хотите делать это при каждом запросе, плюс это может привести к нежелательным эффектам, которые я не могу объяснить в одном комментарии). - person N.B.   schedule 10.09.2016