ведение настраиваемого столбца автоматического увеличения

#sql #sql-server #sql-server-2012 #sql-server-2014

#sql #sql-сервер #sql-server-2012 #sql-server-2014

Вопрос:

У меня есть таблица с ID (identity) и XID (int), которая является моим настраиваемым столбцом автоматического увеличения. Я использую триггер instead вместо insert для поддержания XID, но я получаю дубликаты.

таблица

xtable (идентификатор идентификатора, XID int)

триггер — вместо вставки

 insert into [xtable] (XID)
select [x].[NextavailableID]
from inserted [i]
cross apply
(
  select coalesce(max([t].[XID]), 0)   1 [NextavailableID]
  from [xtable] [t]
) [x];

Предполагается, что вставлено = 1 строка.

Этот триггер не предотвращает дублирование в столбце XID. Есть идеи о том, как это изменить?

1. Зачем вам нужен второй столбец автоматического увеличения? Это упрощенный пример? Может быть альтернативное решение, которое проще поддерживать.

2. @GarethD — Это упрощенный пример. У каждой компании должен быть уникальный идентификатор. Таким образом, в xtable также будет CompanyID fk

3. Какой SQL Server? 2008 [R2], 2012, 2014?

4. @BogdanSahlean — мы используем обновленную версию, но подойдет любая версия решения

Ответ №1:

Проблема в том, что при вставке нескольких строк вы используете один и тот же следующий доступный идентификатор для всех строк, вам нужно будет добавить ROW_NUMBER() , чтобы убедиться, что xid был уникальным во вставке:

 insert into [xtable] (XID)
select [x].[NextavailableID]   ROW_NUMBER() OVER (ORDER BY i.ID)
from inserted [i]
cross apply
(
  select coalesce(max([t].[XID]), 0) [NextavailableID]
  from [xtable] [t] WITH (TABLOCK, HOLDLOCK)
) [x];

Что касается предотвращения дублирования, вы можете использовать подсказки таблицы для блокировки xtable при получении максимума xid .

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

1. Будет ли это работать, если несколько инструкций insert выполняются параллельно?

2. Извините, я не понял, я предполагаю, что вставлено только 1 строка. Я получаю дубликаты, когда одновременно выполняется несколько инструкций insert

3. @Aducci И именно поэтому ответ заключался в том, чтобы использовать IDENTITY столбец вместо созданного на заказ

4. Используя блокировку, я получаю сообщение об ошибке transaction was deadlocked on lock resources with another process and has been chosen as the victim

5. Идея подсказок по таблице была отличной. Мне просто нужно было использовать их в отдельной таблице. Спасибо за вашу помощь

Ответ №2:

В итоге я создал еще одну таблицу для хранения последнего приращения. В триггере внутри транзакции я выбираю из новой таблицы с помощью подсказок ( UPDLOCK , ROWLOCK ).

таблица

 Info (LastId int)

триггер — вместо вставки

 declare @nextId int;

begin tran t1

  set @nextId = (select top 1 LastId from Info with (UPDLOCK, ROWLOCK))   1;

  update Info set LastId = nextId;

commit tran t1

insert into [xtable] (XID)
select @nextId
from inserted [i]