- PVSM.RU - https://www.pvsm.ru -

Что делать, если в PK Identity закончились значения?

Иногда, при дизайне БД разработчики недооценивают масштабы проекта. А потом, проект выстреливает и становится высоконагруженным. Затем, в какой-то момент, кто-то замечает, что в качестве первичного ключа большой таблицы выбран identity типа INT, с ограничением 2,147,483,647.

Изначально кажется, что 2 миллиарда записей (records) – это много. Но если, у вас ежедневно добавляется 10 млн. новых записей? И уже израсходовано более 1 млрд. значений? У вас приложение, работающее в режиме 24/7? То у вас осталось всего 114 дней, чтобы это исправить тип первичного ключа. Это не так уж и много, если у вас используется значение ключа как в веб-приложении, так и в клиентском.
Что делать, если в PK Identity закончились значения? - 1

Если описанная ситуация вам знакома, и Вы заметили эту прискорбную деталь – у Вас заканчиваются значения первичного ключа – слишком поздно, то данная статья – для Вас. В нашей статье Вы найдете скрипты, которые приведены для таблицы TableWithPKViolation, в которой поле TableWithPKViolationId вызывает проблему.

В худшем случае, Вы столкнулись с ошибкой “Arithmetic overflow error converting IDENTITY to data type int”. Это означает, что значения первичного ключа уже закончились, и Ваше приложение перестало работать. В данном случае Вы можете использовать следующие решения:

  1. Поменять тип первичного ключа на BIGINT. Всем и каждому понятно, что лучший вариант — это сесть в машину времени и изменить INTна BIGINT там, в прошлом. Но Вы можете сделать это и сейчас, если поле TableWithPKViolationId не используется в серверном и клиентском приложении, то у вас есть возможность оперативно и безболезненно поменять тип. Сделайте это, и не тратьте время на остальную статью. Обратите внимание, что если в Вашей таблице больше 1 млрд. записей, то изменение будет применяться, т.е. может занять больше 3 часов, в зависимости от мощности вашего сервера, и потребует дополнительного места в логе транзакций (если можете, переключитесь на модель Recovery Mode в Simple). Скрипт для изменения следующий: 
    ALTER TABLE TableWithPKViolation ALTER COLUMN TableWithPKViolationId BIGINT;

    Если данный способ Вам недоступен, рекомендуем запланировать переход ключа на BIGINT как можно быстрее.

  2. Использовать отрицательные значения. Обычно, при использовании identity используется по умолчанию IDENTITY(1,1), когда значение подходит к 2 миллиардам записей, Вы можете сделать сброс начального значения, используя следующую команду: 
    DBCC CHECKIDENT (TableWithPKViolation, - 2147483647, reseed)

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

  3. III. Сформировать таблицу с неиспользованными значениями. Посчитайте значения, которые пропущены, и сформируйте таблицу со списком неиспользованных значений. Это даст Вам дополнительное время для перехода на BIGINT.

    Данный способ Вам подойдет, если Вы не опираетесь на порядок записей в таблице, то есть не используете ORDERY BY Id. Либо, таких мест не много, и Вы можете изменить порядок сортировки, основываясь на другом поле, например, на дате добавления записи.

    Сформировать таблицу с неиспользованными значениями можно двумя способами:

    Способ А. Пропущенные значения.

    Когда вы используете Identity, у вас всегда есть пропущенные значения, так как, значения резервируются при начале транзакции, и, в случае ее отката, следующей транзакции присваивается новое, следующее за зарезервированным, значение первичного ключа. Зарезервированное значение, которое было сформировано для отмененной транзакции, так и останется неиспользованным. Данные неиспользованные значения можно сформировать в отдельную таблицу и применить, используя код, который будет приведен ниже.

    Способ В. Удаленные значения.

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

    Исходная таблица TableWithPKViolation.

    CREATE TABLE [dbo].[TableWithPKViolation](
	[TableWithPKViolationId] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

    1. Создаем таблицу для хранение свободных ID
    10-CreateNewId.sql

    
CREATE TABLE [dbo].[NewIds](
	[NewId] [int] NOT NULL,
	[DateUsedUtc] [datetime] NULL
) ON [PRIMARY]

    Далее, в зависимости от способа:

    Для генерации последовательности способом А. Пропущенные значения:

    2. Генерируем последовательность из пропущенных идентификаторов
    20-GenerateGaps.sql
    «Option1 FindGaps20-GenerateGaps.sql»

    
CREATE PROCEDURE [dbo].[spNewIDPopulateInsertFromGaps]
	@batchsize INT = 10000,
	@startFrom INT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	IF @startFrom IS NULL
	BEGIN
		SELECT @startFrom = MAX([NewId])
		FROM dbo.NewIds; 
	END;

	DECLARE @startId INT = ISNULL(@startFrom,0);
	DECLARE @rowscount INT = @batchsize;
	DECLARE @maxId INT;

	SELECT @maxId = MAX(TableWithPKViolationId)
	FROM dbo.TableWithPKViolation;

	WHILE @startId < @maxId
	BEGIN
		INSERT INTO dbo.NewIds
		([NewId])
		SELECT id
		FROM (
			SELECT TOP (@batchsize)
				@startId + ROW_NUMBER() 
                                OVER(ORDER BY TableWithPKViolationId) AS id
			FROM dbo.TableWithPKViolation --any table where you have @batchsize rows
			) AS genids
		WHERE id < @maxId
			AND NOT EXISTS 
			  (
			   SELECT 1 
			   FROM [dbo].[TableWithPKViolation] as Tb WITH (NOLOCK)
			   WHERE Tb.TableWithPKViolationId = genids.id
			  ); 

		SET @rowscount = @@ROWCOUNT;
				
		SET @startId = @startId + @batchsize;

		PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50));
	END
END

    Для генерации последовательности способом B Удаленные значения:
    2. Создаем таблицу для генерации последовательности и заполняем ее данными от 1 до 2,147,483,647
    15-CreateInt.sql

    
CREATE TABLE [dbo].[IntRange](
	[Id] [int] NOT NULL
) ON [PRIMARY]

    20-GenerateInt.sql

    
CREATE PROCEDURE [dbo].[spNewIDPopulateInsert]
	@batchsize INT = 10000,
	@startFrom INT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	IF @startFrom IS NULL
	BEGIN
		SELECT @startFrom = MAX(id)
		FROM dbo.IntRange; 
	END;

	DECLARE @startId INT = ISNULL(@startFrom,0);
	DECLARE @rowscount INT = @batchsize;
	DECLARE @maxId INT = 2147483647;

	WHILE @rowscount = @batchsize
	BEGIN
		INSERT INTO dbo.IntRange
		(id)
		SELECT id
		FROM (
			SELECT TOP (@batchsize)
				@startId + ROW_NUMBER() 
                                OVER(ORDER BY TableWithPKViolationId) AS id
			FROM dbo.TableWithPKViolation --any table where you have @batchsize rows
			) AS genids
		WHERE id < @maxId; 

		SET @rowscount = @@ROWCOUNT;
				
		SET @startId = @startId + @rowscount;

		PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50));
	END
END

    25-PopulateRange.sql

    
exec dbo.spNewIDPopulateInsert
	@batchsize = 10000000

    В скрипте используется таблица TableWithPKViolation для генерации последовательности, на самом деле, вы можете использовать любой, наиболее любимый способ для этого, в том числе, последовательность встроенную в MS SQL (Sequence), данный способ был выбран, потому что работал быстрее по сравнению с другими.

    30-CreateIndexOnInt.sql

    
ALTER TABLE [dbo].[IntRange] ADD PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

    и заполняем ее
    50-GenerateNewId.sql

    
CREATE PROCEDURE [dbo].[spNewIDPopulateInsertFiltered]
	@batchsize INT = 10000,
	@startFrom INT = NULL,
	@endTill INT = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	IF @startFrom IS NULL
	BEGIN
		SELECT @startFrom = MAX([NewId])
		FROM dbo.NewIds; 
	END;

	DECLARE @startId INT = ISNULL(@startFrom,0);
	DECLARE @rowscount INT = @batchsize;
	DECLARE @maxId INT = ISNULL(@endTill,2147483647);
	DECLARE @endId INT = @startId + @batchsize;

	WHILE @startId < @maxId
	BEGIN
		INSERT INTO [NewIds] 
		([NewId])
		SELECT IR.id
		FROM [dbo].[IntRange] AS IR
		WHERE IR.id >= @startId
			AND IR.id < @endId
			AND NOT EXISTS 
			  (
			   SELECT 1 
			   FROM [dbo].[TableWithPKViolation] as Tb WITH (NOLOCK)
			   WHERE Tb.TableWithPKViolationId = IR.id
			  );

		SET @rowscount = @@ROWCOUNT;
				
		SET @startId = @endId;
		SET @endId = @endId + @batchsize;

		IF @endId > @maxId 
			SET @endId = @maxId;

		PRINT CONVERT(VARCHAR(50),GETDATE(),121)+' '+ CAST(@startId AS VARCHAR(50));
	END
END

    55-ExecGeneration.sql

    
-----Run each part in separate window in parallel
-----

--part 1
exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, 
@startFrom = 1, @endTill= 500000000
--end of part 1

--part 2
exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, 
@startFrom = 500000000, @endTill= 1000000000
--end of part 2

--part 3
exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, 
@startFrom = 1000000000, @endTill= 1500000000
--end of part 3

--part 4
DECLARE @maxId INT

SELECT @maxId = MAX(TableWithPKViolationId)
FROM dbo.TableWithPKViolation

exec dbo.spNewIDPopulateInsertFiltered @batchsize = 10000000, 
@startFrom = 1500000000, @endTill= @maxId
--end of part 4

    3. После того, как таблица свободных идентификаторов, сгенерированная способом A или B готова, создаем индексы на таблице со свободными ключами
    60-CreateIndex.sql

    
ALTER TABLE [dbo].[NewIds] ADD  CONSTRAINT [PK_NewIds] PRIMARY KEY CLUSTERED 
(
	[NewId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_NewIds_DateUsedUtc] ON [dbo].[NewIds]
(
	[DateUsedUtc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF)

GO

ALTER TABLE [dbo].[NewIds] SET ( LOCK_ESCALATION = DISABLE )
GO

    Проверяем, что все было правильно сгенерировано, у вас не должно быть ID в таблице NewId, которые есть в основной таблице TableWithPKViolation.
    70-CheckData.sql

    
declare @maxId INT


select @maxId = max(TableWithPKViolationId) 
from [dbo].[TableWithPKViolation]

IF EXISTS (select 1 from [dbo].[NewIds] WHERE [NewId] > @maxId)
BEGIN
	PRINT 'PROBLEM. Wait for cleanup';
	
	declare @batchsize INT = 10000
	DECLARE @rowcount int = @batchsize;

	while @rowcount = @batchsize 
	begin 

		delete top (@batchsize)
		from [dbo].[NewIds]
		where DFVId > @maxId;

		SET @rowcount = @@rowcount;
	end;
END
ELSE
	PRINT 'OK';

    Если вы генерируете последовательно на другом сервере (например на сервере с восстановленной резервной копией БД), то выгрузить данные в файл, можно с помощью скрипта:
    80-BulkOut.sql

    
declare @command VARCHAR(4096),
		@dbname VARCHAR(255),
		@path VARCHAR(1024),
		@filename VARCHAR(255),
		@batchsize INT


SELECT @dbname = DB_NAME();
SET @path = 'D:NewIds';
SET @filename = 'NewIds-'+@dbname+'.txt';
SET @batchsize = 10000000;
SET @command = 'bcp "['+@dbname+'].dbo.NewIds" out  "'+@path+@filename+'" -c -t, -S localhost -T -b '+CAST(@batchsize AS VARCHAR(255));

PRINT @command

exec master..xp_cmdshell @command

    4. Создаем процедуру, которая помечает нужное количество доступный ID и возвращает их в результате
    90-GetNewId.sql

    
create PROCEDURE [dbo].[spGetTableWithPKViolationIds]
	@batchsize INT = 1
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @rowcount INT,
		@now DATETIME = GETUTCDATE();

	BEGIN TRAN

    UPDATE TOP (@batchsize) dbo.NewIds
	SET DateUsedUtc = @now
	OUTPUT inserted.[NewId]
	WHERE DateUsedUtc IS NULL;

	SET @rowcount = @@ROWCOUNT; 

	IF @rowcount != @batchsize
	BEGIN 
		DECLARE @msg NVARCHAR(2048);

		SET @msg = 'TableWithPKViolationId out of ids. sp spGetTableWithPKViolationIds, table NewIds. '
			+'Ids requested '
			+ CAST(@batchsize AS NVARCHAR(255))
			+ ', IDs available '
			+ CAST(@rowcount AS NVARCHAR(255));

		RAISERROR(@msg, 16,1);
		ROLLBACK;
	END
	ELSE
	BEGIN
		COMMIT TRAN
	END;
END

    5. Добавляем во все процедуры, в которых была вставка данных, в таблицу и возвращался SCOPE_IDENTITY(), вызов новой процедуры.

    Если позволяет производительность или вам очень дорого время, а процедур нужно поменять много, можно сделать триггер instead of insert.

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

    
CREATE TABLE #tmp_Id (Id INT);

INSERT INTO #tmp_Id
EXEC spGetTableWithPKViolationIds @batchsize=@IDNumber;

SELECT @newVersionId = Id
FROM #tmp_Id;

SET IDENTITY_INSERT [dbo].[TableWithPKViolation] ON;

    Обратите внимание, что для опции SET IDENTITY_INSERT ON нужно, чтобы пользователь вызывающий процедуру имел разрешение на ALTER для таблицы TableWithPKViolation.

    6. Затем можно настроить JOB, который будет очищать таблицу с используемыми идентификаторами
    95-SPsCleanup.sql

    
create PROCEDURE dbo.spCleanupNewIds @batchSize INT = 4999
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @minId INT
	DECLARE @maxId INT

	SELECT @minId = Min([NewId]), @maxId = MAX([NewId])
	FROM dbo.NewIds WITH (NOLOCK)
	WHERE DateUsedUtc IS NOT NULL;

	DECLARE @totRowCount INT = 0
	DECLARE @rowCount INT = @batchSize

	WHILE @rowcount = @batchsize
	BEGIN
		DELETE TOP (@batchsize)
		FROM dbo.NewIds
		WHERE DateUsedUtc IS NOT NULL AND [NewId] >= @minId AND [NewId] <= @maxId

		SET @rowcount = @@ROWCOUNT

		SET @totRowCount = @totRowCount + @rowcount
	END

	PRINT 'Total records cleaned up - ' + CAST(@totRowCount AS VARCHAR(100))
END

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

    Я бы все равно порекомендовала при этом запланировать переход на BIGINT.

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

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

Наилучшим решением является мониторинг пограничных значений и переход на соответствующие типы данных заранее.

Архив с кодом [1]

Автор: KristinaMyLife

Источник [2]

Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/sql/259170

Ссылки в тексте:

[1] Архив с кодом: https://drive.google.com/file/d/0B2xL7TcM6ZSpcjRfSU51N25Dd0E/view?usp=sharing

[2] Источник: https://habrahabr.ru/post/329506/