복붙노트

[SQL] 시퀀스에서 다음 번호를 얻는 방법

SQL

시퀀스에서 다음 번호를 얻는 방법

나는이 같은 테이블이 있습니다 :

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 2    | 29    |  |
| 4  | Shoulders | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
+----+-----------+------+-------+--+

어떻게 모델 그래서 여기에 3에 대한 위 후 다음 서열과 다른 레코드를 삽입 할 수 있습니다하면 새 테이블의 모양을 가정 것입니다 :

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 2    | 29    |  |
| 4  | Shoulders | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
| 6  | Groin     | 6    | 3     |  |
+----+-----------+------+-------+--+

모델 3 만에 가장 높은 서열 후 다음 숫자를 줄 것이다 삽입 쿼리를 정교하게 할 수있는 방법이있다. 또한, 뭔가를 찾고 동시성 안전입니다.

해결법

  1. ==============================

    1.당신이 카운터 테이블을 유지하지 않는 경우, 두 가지 옵션이 있습니다. 트랜잭션 내에서 먼저 다음 테이블 힌트 중 하나를 사용하여 MAX (seq_id)를 선택합니다 :

    당신이 카운터 테이블을 유지하지 않는 경우, 두 가지 옵션이 있습니다. 트랜잭션 내에서 먼저 다음 테이블 힌트 중 하나를 사용하여 MAX (seq_id)를 선택합니다 :

    TABLOCKX + HOLDLOCK은 약간의 과잉이다. 트랜잭션이 작은 경우에도 무거운 간주 될 수 있습니다 그것은 블록 일반 select 문.

    ROWLOCK는 XLOCK은 HOLDLOCK 테이블 힌트 아마 좋은 생각이다 (그러나 : 더 카운터 테이블에 함께 대안을 읽기). 장점은 선택 문이 같은 테이블 힌트를 제공하지 않는 경우 선택 문이 SERIALIZABLE 트랜잭션에 표시하거나하지 않는 경우는 일반 select 문, 즉 차단하지 않습니다. ROWLOCK을 사용 XLOCK은 HOLDLOCK은 여전히 ​​문 삽입 차단합니다.

    물론 당신은 반드시 프로그램의 다른 부분 (또는 SERIALIZABLE 트랜잭션 외부)이 테이블 힌트없이 MAX (seq_id)을 선택하고 행을 삽입하기 위해이 값을 사용하는 것이 있어야합니다.

    이런 식으로 고정되는 행의 수에 따라, SQL Server가 테이블 잠금으로 잠금을 확대 할 가능성이 있습니다. 여기 잠금 에스컬레이션에 대해 자세히 알아보십시오.

    다음과 같이 보일 것이다 (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 model=@target_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 model=@target_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 seq=@new_seq WHERE model=@target_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 '두 번째 SSMS 탭에 (들)을 counter_seq에서 순서를 선택하고 테이블과 조롱 후.

    PS1은 : (ID BY 모델 ORDER BY 파티션) ROW_NUMBER () OVER를 사용하는 것은 좋은 방법이 아닙니다. 행이 삭제 된 경우 / 추가, 또는 ID의 변경 순서 변경 (즉, 변경 안 송장 아이디의 생각) 것입니다. 또한 하나의 행을 검색 할 때 이전의 모든 행의 행 번호를 결정하는 데 성능면에서 나쁜 생각이다.

    PS2 : 나는 SQL Server가 이미 격리 수준 또는 세분화 된 테이블 힌트를 통해 잠금 제공하는 경우, 잠금을 제공하기 위해 외부 자원을 사용하지 않을 것입니다.

  2. ==============================

    2.당신은, 시퀀스와 열의 기본값을 선호하는 경우 등의 삽입을 처리하는 올바른 방법은 ID 열을 사용하는 것입니다 나.

    당신은, 시퀀스와 열의 기본값을 선호하는 경우 등의 삽입을 처리하는 올바른 방법은 ID 열을 사용하는 것입니다 나.

    그러나, 당신은 올바른하지 않는 것 서열 컬럼의 NULL 값을 가지고있다.

    쿼리 등의 문제 :

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

    동시에 실행이 이러한 쿼리는, 같은 값을 생성 할 수 있다는 것입니다. 권장 사항은 고유 한 ID 열로 서열을 선언하고 데이터베이스로 하여금 모든 작업을 행하도록하는 것입니다.

  3. ==============================

    3.하자 첫 번째 목록 도전 :

    하자 첫 번째 목록 도전 :

    그 짧은 전편을 가진 자, 솔루션을 시도 할 수 있습니다 :

    시작, 우리는 원래의 테이블을 작성하고 또한 표는 우리가 마지막으로 사용 된 순서 + 1로 설정하는 순서 (BodyPartsCounter)를 잡아 :

        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
    ;

    그럼 우리가 마법을 할 것입니다 저장 프로 시저를 작성해야합니다. (다른 곳에서 같은 테이블에 삽입 또는 업데이트를하지 않으면) 즉, 그것은 기본적으로 당신에게 동시성을 보장 뮤텍스 역할을합니다. 그것은 다음의에게 다음 서열을 얻을, 그것은 및 삽입 새 행을 업데이트합니다. 이 모든 일이 후에는 트랜잭션을 커밋 및 스레드를 호출 다음 대기를 위해 저장된 프로 시저를 발표 할 예정이다.

    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. ==============================

    4.나는 TT가 제안 시퀀스를 생성 이런 종류의 시나리오를 처리 할 수있는 가장 좋은 방법은 카운터 테이블 믿습니다. 난 그냥 여기 TT 구현의 약간 단순화 된 버전을 보여주고 싶었어요.

    나는 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 문은 현재 서열를 검색 할 수 있습니다)

    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 model=@target_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. ==============================

    5.당신은 순서가 특정 모델을 기반으로하는 원하기 때문에 선택을 할 때, 단지 where 절에 그것을 추가 할 수 있습니다. 이것은 최대 (서열) 해당 모델 시리즈에 해당을 보장합니다. SEQ 그것이 NULL 인 그래서 경우에 ISNULL 감싸 널이 될 수 있으며 또한 이후 0 + 1을 1로 설정한다 다음 그래서, 0 일 것이다. 이 작업을 수행하는 기본적인 방법은 다음과 같습니다

    당신은 순서가 특정 모델을 기반으로하는 원하기 때문에 선택을 할 때, 단지 where 절에 그것을 추가 할 수 있습니다. 이것은 최대 (서열) 해당 모델 시리즈에 해당을 보장합니다. SEQ 그것이 NULL 인 그래서 경우에 ISNULL 감싸 널이 될 수 있으며 또한 이후 0 + 1을 1로 설정한다 다음 그래서, 0 일 것이다. 이 작업을 수행하는 기본적인 방법은 다음과 같습니다

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

    6.나는 처음에 테이블의 서열 값을 저장하려고하지 않을 것입니다.

    나는 처음에 테이블의 서열 값을 저장하려고하지 않을 것입니다.

    당신이 코멘트에 말했듯이, 당신의 ID가 서버에 의해 매우 효율적이고 동시 안전한 방법으로 자동 증가 IDENTITY이다. 행이 삽입 된 순서와 서열 값을 생성하는 순서를 결정하기 위해 사용.

    그런 서열이 (모델의 각 값에 대해 1에서 상기 시퀀스가 ​​다시 시작) 모델에 의해 구획의 쿼리에 필요한 값을 생성하기 ROW_NUMBER를 사용한다.

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

    7.

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

    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)

    이 내가 추측을 시도 할 수 있습니다. 메신저 잘못된 경우 초보자 샷, 정정 해줘. 나는 모델을 기반으로 서열 열의 값을 얻기 위해 기능을 사용하는 것이 좋습니다 것; 당신은 모델! = 3, 지금 널 (null)을 반환 할 수 있습니다 때, 당신이 원하는 다른 값을 반환 불구하고 다른 경우를 확인해야합니다.

  9. ==============================

    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);

    쿼리는 아래 ruine 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)를 참조하십시오

    10. from https://stackoverflow.com/questions/35261411/how-to-get-the-next-number-in-a-sequence by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 실종 날짜를 표시하는 SQL 서버  (0) 2020.06.18
[SQL] 열을 지정하지 않고 SQL INSERT. 무슨 일이야?  (0) 2020.06.18
[SQL] 주말을 제외한 SQL에서 두 날짜 사이의 계산 차이,  (0) 2020.06.18
[SQL] 어떻게 2008 SQL 서버에서 XML 열을 읽습니까?  (0) 2020.06.18
[SQL] 함수 (SQL 서버) 내에서 동적 SQL을 실행할 때 오류를 얻기?  (0) 2020.06.18
,

티스토리툴바