Как получить следующее число в последовательности

Ответ 1

Если вы не поддерживаете таблицу счетчиков, есть два варианта. Внутри транзакции сначала выберите MAX(seq_id) с одним из следующих табличных подсказок:

• WITH(TABLOCKX, HOLDLOCK)
• WITH(ROWLOCK, XLOCK, HOLDLOCK)

TABLOCKX + HOLDLOCK немного перебор. Он блокирует регулярные операции выбора, которые могут считаться тяжелыми, хотя транзакция небольшая.

A ROWLOCK, XLOCK, HOLDLOCK подсказка таблицы, вероятно, является лучшей идеей (но: читайте альтернативу с таблицей счетчиков далее). Преимущество заключается в том, что он не блокирует регулярные операции выбора, т.е. Когда операторы select не отображаются в транзакции SERIALIZABLE или когда операторы select не предоставляют одинаковые подсказки таблицы. Использование ROWLOCK, XLOCK, HOLDLOCK будет по-прежнему блокировать инструкции вставки.

Конечно, вы должны быть уверены, что никакие другие части вашей программы не будут выбирать MAX(seq_id) без этих подсказок таблицы (или вне транзакции SERIALIZABLE), а затем использовать это значение для вставки строк.

Обратите внимание, что в зависимости от количества строк, заблокированных таким образом, возможно, что SQL Server увеличит блокировку до блокировки таблицы. Подробнее о блокировке эскалации здесь.

Процедура вставки с использованием WITH(ROWLOCK, XLOCK, HOLDLOCK) будет выглядеть следующим образом:

DECLARE @target_model INT=3;
DECLARE @part VARCHAR(128)='Spine';
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @max_seq INT=(SELECT MAX(seq) FROM dbo.table_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE [email protected]_model);
    IF @max_seq IS NULL SET @max_seq=0;
    INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@max_seq+1,@target_model);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH

Альтернативой и, вероятно, лучшей идеей является наличие таблицы счетчиков и предоставление этих табличных подсказок в таблице счетчиков. Эта таблица будет выглядеть следующим образом:

CREATE TABLE dbo.counter_seq(model INT PRIMARY KEY, seq_id INT);

Затем вы измените процедуру вставки следующим образом:

DECLARE @target_model INT=3;
DECLARE @part VARCHAR(128)='Spine';
BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @new_seq INT=(SELECT seq FROM dbo.counter_seq WITH(ROWLOCK,XLOCK,HOLDLOCK) WHERE [email protected]_model);
    IF @new_seq IS NULL 
        BEGIN SET @new_seq=1; INSERT INTO dbo.counter_seq(model,seq)VALUES(@target_model,@new_seq); END
    ELSE
        BEGIN SET @new_seq+=1; UPDATE dbo.counter_seq SET [email protected]_seq WHERE [email protected]_model; END
    INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@new_seq,@target_model);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH

Преимущество заключается в том, что используется меньшее количество блокировок строк (т.е. по одной для модели в dbo.counter_seq), а блокировка эскалации не может заблокировать всю таблицу dbo.table_seq, тем самым блокируя операции выбора.

Вы можете протестировать все это и увидеть эффекты самостоятельно, разместив WAITFOR DELAY '00:01:00' после выбора последовательности из counter_seq и прокручивая таблицу (таблицы) на второй вкладке SSMS.

PS1: Использование ROW_NUMBER() OVER (PARTITION BY model ORDER BY ID) не является хорошим способом. Если строки удалены/добавлены или изменился идентификатор, последовательность изменится (рассмотрите идентификатор счета, который никогда не должен меняться). Также с точки зрения производительности, чтобы определить номера строк всех предыдущих строк при извлечении одной строки, это плохая идея.

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

Ответ 2

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

Однако у вас есть значение NULL для столбца seq, что кажется неправильным.

Проблема с запросом, например:

Insert into yourtable(id, Part, Seq, Model)
    Select 6, 'Groin', max(Seq) + 1, 3 
    From yourtable;

заключается в том, что два таких запроса, работающих одновременно, могут давать одинаковое значение. Рекомендация состоит в том, чтобы объявить seq как уникальный столбец идентичности и позволить базе данных выполнять всю работу.

Ответ 3

Сначала перечислим проблемы:

• Мы не можем использовать нормальное ограничение, поскольку существуют существующие значения NULL, и нам также нужно обслуживать дубликаты, а также пробелы - если мы посмотрим на существующие данные. Это хорошо, мы это выясним; → на шаге 3

• Мы нуждаемся в безопасности для параллельных операций (таким образом, какая-то форма или сочетание транзакций, уровней изоляции и, возможно, "своего рода SQL-мьютекс".) Чувствуйте, что здесь хранится процесс по нескольким причинам:

  2.1 Он легче защищает от внедрения sql

  2.2 Мы можем более легко контролировать уровни изоляции (блокировку таблиц) и восстанавливать некоторые проблемы, связанные с этим требованием

  2.3 Мы можем использовать блокировки db уровня приложения для управления concurrency

• Мы должны хранить или находить следующее значение для каждой вставки. Слово concurrency говорит нам уже о том, что будут конфликты и, вероятно, высокая пропускная способность (иначе, пожалуйста, придерживайтесь отдельных потоков). Поэтому мы уже должны думать: не читайте из той же таблицы, в которую хотите писать, в уже сложном мире.

Итак, с этим коротким приквелом, попробуйте решение:

В начале мы создаем вашу исходную таблицу, а затем также таблицу для хранения последовательности (BodyPartsCounter), которую мы устанавливаем для последней используемой последовательности + 1:

    CREATE TABLE BodyParts
        ([id] int identity, [Part] varchar(9), [Seq] varchar(4), [Model] int)
    ;

    INSERT INTO BodyParts
        ([Part], [Seq], [Model])
    VALUES
        ('Head', NULL, 3),
        ('Neck', '1', 3),
        ('Shoulders', '2', 29),
        ('Shoulders', '2', 3),
        ('Stomach', '5', 3)
    ;

    CREATE TABLE BodyPartsCounter
        ([id] int
        , [counter] int)
    ;

    INSERT INTO BodyPartsCounter
        ([id], [counter])
    SELECT 1, MAX(id) + 1 AS id FROM BodyParts
    ;

Затем нам нужно создать хранимую процедуру, которая будет делать магию. Короче говоря, он действует как мьютекс, в основном гарантируя вам concurrency (если вы не вставляете или не обновляете в те же таблицы в другом месте). Затем он получает следующий seq, обновляет его и вставляет новую строку. После того, как все это произошло, он совершит транзакцию и освободит сохраненный proc для следующего ожидающего потока вызовов.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Charlla
-- Create date: 2016-02-15
-- Description: Inserts a new row in a concurrently safe way
-- =============================================
CREATE PROCEDURE InsertNewBodyPart 
@bodypart varchar(50), 
@Model int = 3
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    BEGIN TRANSACTION;

    -- Get an application lock in your threaded calls
    -- Note: this is blocking for the duration of the transaction
    DECLARE @lockResult int;
    EXEC @lockResult = sp_getapplock @Resource = 'BodyPartMutex', 
                   @LockMode = 'Exclusive';
    IF @lockResult = -3 --deadlock victim
    BEGIN
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        DECLARE @newId int;
        --Get the next sequence and update - part of the transaction, so if the insert fails this will roll back
        SELECT @newId = [counter] FROM BodyPartsCounter WHERE [id] = 1;
        UPDATE BodyPartsCounter SET [counter] = @newId + 1 WHERE id = 1;

        -- INSERT THE NEW ROW
        INSERT INTO dbo.BodyParts(
            Part
            , Seq
            , Model
            )
            VALUES(
                @bodypart
                , @newId
                , @Model
            )
        -- END INSERT THE NEW ROW
        EXEC @lockResult = sp_releaseapplock @Resource = 'BodyPartMutex';
        COMMIT TRANSACTION;
    END;

END
GO

Теперь запустите тест следующим образом:

EXEC    @return_value = [dbo].[InsertNewBodyPart]
    @bodypart = N'Stomach',
    @Model = 4

SELECT  'Return Value' = @return_value

SELECT * FROM BodyParts;
SELECT * FROM BodyPartsCounter

Все это работает, но будьте осторожны - там много можно рассмотреть с любым многопоточным приложением.

Надеюсь, что это поможет!

Ответ 4

Я считаю, что лучший способ справиться с таким сценарием генерации последовательности - это таблица счетчиков как TT. Я просто хотел показать вам немного упрощенную версию TT.

Таблицы:

CREATE TABLE dbo.counter_seq(model INT PRIMARY KEY, seq INT);
CREATE TABLE dbo.table_seq(part varchar(128), seq int, model int);

Упрощенная версия (инструкция SELECT для получения текущего seq):

DECLARE @target_model INT=3;
DECLARE @part VARCHAR(128)='Otra MAS';

BEGIN TRY
    BEGIN TRANSACTION;
    DECLARE @seq int = 1
    UPDATE dbo.counter_seq WITH(ROWLOCK,HOLDLOCK) SET @seq = seq = seq + 1 WHERE [email protected]_model;
    IF @@ROWCOUNT = 0 INSERT INTO dbo.counter_seq VALUES (@target_model, 1);
    INSERT INTO dbo.table_seq(part,seq,model)VALUES(@part,@seq,@target_model);
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH

Ответ 5

Поскольку вы хотите, чтобы последовательность была основана на конкретной модели, просто добавьте ее в предложение where при выборе. Это гарантирует, что Max (SEQ) относится только к этой серии моделей. Кроме того, поскольку SEQ может быть null обнулить его в ISNULL, поэтому, если он равен нулю, он будет равен 0, поэтому 0 + 1 установит рядом с 1. Основной способ сделать это:

Insert into yourtable(id, Part, Seq, Model)
    Select 6, 'Groin', ISNULL(max(Seq),0) + 1, 3 
    From yourtable
    where MODEL = 3;

Ответ 6

Я бы не попытался сохранить значение Seq в таблице в первую очередь.

Как вы сказали в комментариях, ваш ID - IDENTITY, который автоматически увеличивается с помощью эффективного и совместимого со стороны сервера. Используйте его для определения порядка, в который были вставлены строки, и порядок, в котором должны генерироваться значения Seq.

Затем используйте ROW_NUMBER для генерации значений Seq, разделенных на Model (последовательность перезапускается от 1 для каждого значения Model) по мере необходимости в запросе.

SELECT
    ID
    ,Part
    ,Model
    ,ROW_NUMBER() OVER(PARTITION BY Model ORDER BY ID) AS Seq
FROM YourTable

Ответ 7

insert into tableA (id,part,seq,model)
values
(6,'Groin',(select MAX(seq)+1 from tableA where model=3),3)

Ответ 8

create function dbo.fncalnxt(@model int)
returns int 
begin
declare @seq int
select @seq= case when @model=3 then max(id) --else
end from tblBodyParts
return @seq+1
end
--query idea To insert values, ideal if using SP to insert
insert into tblBodyParts values('groin',dbo.fncalnxt(@model),@model)

Вы можете попробовать это, я думаю. Новичок, исправьте меня, если я ошибаюсь. я бы предложил использовать функцию, чтобы получить значение в столбце seq на основе модели; вам нужно будет проверить else, хотя для возврата другого значения вы хотите, когда model!= 3, теперь он вернет null.

Ответ 9

Предполагая, что у вас есть следующая таблица:

CREATE TABLE tab (
    id int IDENTITY(1,1) PRIMARY KEY,
    Part VARCHAR(32) not null,
    Seq int not null,
    Model int not null
);

INSERT INTO
    tab(Part,Seq,Model)
VALUES
    ('Head', 0, 3),
    ('Neck', 1, 3),
    ('Shoulders', 2, 29),
    ('Shoulders', 2, 3),
    ('Stomach', 5, 3);

Следующий запрос позволит вам импортировать несколько записей, не разрушая model_seq

INSERT INTO
    tab (model, part, model_seq)
SELECT
    n.model,
    n.part,
    -- ensure new records will get receive the proper model_seq
    IFNULL(max_seq + model_seq, model_seq) AS model_seq
FROM
    (
        SELECT
            -- row number for each model new record
            ROW_NUMBER() OVER(PARTITION BY model ORDER BY part) AS model_seq,
            n.model,
            n.part,
            MAX(t.seq) AS max_seq
        FROM
            -- Table-values constructor allows you to prepare the
            -- temporary data (with multi rows),
            -- where you could join the existing one
            -- to retrieve the max(model_seq) if any
            (VALUES
                ('Stomach',3),
                ('Legs',3),
                ('Legs',29),
                ('Arms',1)
            ) AS n(part, model)
        LEFT JOIN
            tab
        ON
            tab.model = n.model
        GROUP BY
            n.model n.part
    ) AS t

Нам нужен row_number(), чтобы гарантировать, что мы импортируем более одного значения, который будет сохранен. Подробнее о ROW_NUMBER() OVER() (Transact-SQL)

Конструктор табличных значений используется для создания таблицы с новыми значениями и присоединения к модели MAX model_seq для модели. Здесь вы можете найти больше о конструкторе табличных значений: Конструктор значений таблиц (Transact-SQL)