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

в 12:10, , рубрики: Arithmetic overflow, identity, Microsoft SQL Server, sql, sql server, workaround, ужас, метки:

Иногда, при дизайне БД разработчики недооценивают масштабы проекта. А потом, проект выстреливает и становится высоконагруженным. Затем, в какой-то момент, кто-то замечает, что в качестве первичного ключа большой таблицы выбран 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.

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

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

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

Архив с кодом

Автор: KristinaMyLife

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js