여러 테이블에서 bigint ID 열을 사용하고 있는데 낮은 숫자에서 높은 숫자로 계속 이동하는 것을 확인했습니다. 이 경우에는 17부터 1004까지이지만 다른 테이블에서는 10000까지입니다. 이런 행동이 계속된다면 나는 수천 건의 기록 내에서 bigint 정도가 될 것입니다! 왜 이런 일이 발생하며 어떻게 해결할 수 있나요?
여기 스크린샷:
답변1
Bigint의 범위는 -9,223,372,036,854,775,808부터 9,223,372,036,854,775,807까지입니다. 상한선에 도달하기까지는 시간이 좀 걸릴 것 같습니다. 한 번에 10만 개의 레코드를 뛰어오르더라도 수십억 개의 점프를 하고도 여전히 상한선을 긁지 못할 수 있습니다. 그래도 걱정이 된다면 ID를 다시 설정하여 부정적인 범위에서 시작하세요. 그러면 걱정하기 전에 우리 모두가 죽게 될 것입니다.
ID 값에 간격이 있는 이유는 무엇입니까?
애플리케이션이 테이블에 레코드를 삽입하려고 시도하고 숫자가 풀에서 제거됩니다. 참조 무결성 이유로 인해 삽입이 실패하거나 호출 프로그램이 트랜잭션된 삽입을 실행 취소(롤백)합니다. 어느 쪽이든 ID 값은 더 이상 후보 풀에 없습니다. 네, 트랜잭션이 롤백되더라도 해당 개수가 소진되어 롤백되지 않습니다.
직접 테스트해보세요
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Demo') IS NOT NULL
BEGIN
DROP TABLE #Demo;
END
CREATE TABLE #Demo
(
demoid int IDENTITY(1,1) NOT NULL
, CantBeNull varchar(50) NOT NULL
);
-- add a record and look at our identity
INSERT INTO #Demo
(CantBeNull)
VALUES
('First Entry');
SELECT SCOPE_IDENTITY() AS GeneratedId;
-- GO forces the batch to commit (assuming default implicit transaction model)
GO
-- Violate RI
INSERT INTO #Demo
(CantBeNull)
VALUES
(NULL);
SELECT SCOPE_IDENTITY() AS RIViolationGeneratedId;
GO
-- add a record and look at our identity
INSERT INTO #Demo
(CantBeNull)
VALUES
('Third Entry');
SELECT SCOPE_IDENTITY() AS ThirdGeneratedId;
GO
-- Transaction consumes
BEGIN TRAN
INSERT INTO #Demo
(CantBeNull)
VALUES
('Fourth Entry');
SELECT SCOPE_IDENTITY() AS FourthGeneratedId;
ROLLBACK;
INSERT INTO #Demo
(CantBeNull)
VALUES
('Fifth Entry');
SELECT SCOPE_IDENTITY() AS FifthGeneratedId;
GO
SELECT D.* FROM #Demo AS D;
결과 탭에는 사용된 숫자인 1, 1, 3, 4, 5가 표시되지만 4를 롤백하므로 #Demo 테이블의 커밋된 결과가 표시됩니다.
demoid CantBeNull
1 First Entry
3 Third Entry
5 Fifth Entry
나는 이런 행동을 좋아하지 않는다
이것이 ID 속성이 작동하는 방식입니다. 변경할 수 있는 방법이 없습니다. Sequence 개체(2012년에 새로 추가됨)로 전환하는 경우에도 마찬가지입니다.
격차가 우려된다면 어떤 프로세스가 이를 소비하고 있는지 확인하겠습니다. RI(참조 무결성) 위반인 경우 저장을 시도하기 전에 유효성 검사를 강화하세요. 근본 원인이 트랜잭션이 롤백되는 것이라면 이를 2단계 커밋 또는 이와 유사한 것으로 만드십시오(Stage.MyTable에 저장). ID 고갈이 문제인 경우 SEQUENCE 개체로 전환하고 순환을 켜십시오. 주기적으로 커밋된 트랜잭션에 대해 이 테이블을 폴링한 다음 거기에서 dbo.MyTable에 삽입합니다. 그러면 대리 키에 공백이 발생할 가능성이 줄어듭니다.
켄드라 리틀멋진 격리 수준 포스터는 실제로 커밋된 내용을 확인하는 데 필요한 격리 수준을 기억하는 데 도움이 됩니다.
