태그 보관물: transaction

transaction

데이터베이스는 어떤 시점에서 트랜잭션의 인덱스를 업데이트합니까? 명시되어 있습니다. 데이터를로드하기 전에 하나

인덱스와 트랜잭션이 모두 포함 된 삽입의 이벤트 시퀀스를 이해하려고합니다.

예를 들어, Oracle 설명서에는 다음이 명시되어 있습니다.

데이터를로드하기 전에 하나 이상의 인덱스를 작성하거나 갖는 경우 데이터베이스는 각 행이 삽입 될 때마다 모든 인덱스를 업데이트해야합니다.

그러나 트랜잭션을 만들고 5 개의 행을 삽입 한 다음 커밋하면 어떻게됩니까? 삽입 할 때마다 또는 커밋 지점에서 색인이 업데이트됩니까?

Logic은 레코드가 커밋 될 때까지 업데이트 된 인덱스를 사용할 수 없기 때문에 커밋 시점에서만 업데이트한다고 알려줍니다. 그러나 이것이 사실입니까?

그렇다면 1m 행을 삽입 할 때 최상의 성능을 얻으려면 100k 레코드의 10 트랜잭션이 아닌 모든 행에 대해 하나의 큰 커밋을 수행해야합니까? 물론 행 999,999가 실패하면 이것이 더 큰 롤백 위험이 있음을 알고 있습니다.

내 용어가 약간 밖에 없다면 사과드립니다. 나는 무역에 의한 DBA가 아닙니다. Oracle과 Postgres가 가장 많이 사용하지만 일반적으로 데이터베이스와 같이 특정 데이터베이스에별로 관심이 없습니다. 이 주제를 검색했지만 실제로 확실한 답을 찾을 수 없습니다.



답변

SQL Server 및 Oracle과 협력합니다. 일부 예외가있을 수 있지만 해당 플랫폼의 경우 일반적으로 데이터와 인덱스가 동시에 업데이트됩니다.

트랜잭션을 소유하는 세션과 다른 세션에 대해 인덱스가 업데이트되는 시점을 구분하는 것이 도움이 될 것이라고 생각합니다. 기본적으로 다른 세션에는 트랜잭션이 커밋 될 때까지 업데이트 된 인덱스가 표시되지 않습니다. 그러나 트랜잭션을 소유 한 세션은 즉시 업데이트 된 인덱스를 보게됩니다.

그것에 대해 생각하는 한 가지 방법은 기본 키가있는 테이블을 고려하십시오. SQL Server 및 Oracle에서는 인덱스로 구현됩니다. 대부분의 경우 INSERT기본 키를 위반하는 작업이 수행 되면 즉시 오류가 발생하기 를 원합니다. 이를 위해서는 데이터와 동시에 인덱스를 업데이트해야합니다. Postgres와 같은 다른 플랫폼에서는 트랜잭션이 커밋 될 때만 검사되는 지연 제약 조건을 허용합니다.

다음은 일반적인 사례를 보여주는 빠른 Oracle 데모입니다.

CREATE TABLE X_TABLE (PK INT NULL, PRIMARY KEY (PK));

INSERT INTO X_TABLE VALUES (1);
INSERT INTO X_TABLE VALUES (1); -- no commit

두 번째 INSERT문은 오류를 발생시킵니다.

SQL 오류 : ORA-00001 : 고유 제약 조건 (XXXXXX.SYS_C00384850) 위반

00001. 00000- “고유 제한 조건 (% s. % s)을 위반했습니다”

* 원인 : UPDATE 또는 INSERT 문이 중복 키를 삽입하려고했습니다. DBMS MAC 모드로 구성된 Trusted Oracle의 경우 중복 항목이 다른 레벨에 존재하면이 메시지가 표시 될 수 있습니다.

* 조치 : 고유 제한을 제거하거나 키를 삽입하지 마십시오.

아래의 인덱스 업데이트 작업을 보려면 SQL Server의 간단한 데모입니다. 먼저 행에 백만 개의 행과 비 클러스터형 인덱스가있는 두 개의 열 테이블을 만듭니다 VAL.

DROP TABLE IF EXISTS X_TABLE_IX;

CREATE TABLE X_TABLE_IX (
ID INT NOT NULL,
VAL VARCHAR(10) NOT NULL
PRIMARY KEY (ID)
);

CREATE INDEX X_INDEX ON X_TABLE_IX (VAL);

-- insert one million rows with N from 1 to 1000000
INSERT INTO X_TABLE_IX
SELECT N, N FROM dbo.Getnums(1000000);

인덱스는 해당 쿼리에 대한 포함 인덱스이므로 다음 쿼리는 비 클러스터형 인덱스를 사용할 수 있습니다. 여기에는 실행에 필요한 모든 데이터가 포함됩니다. 예상대로 리턴 값이 리턴되지 않습니다.

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

이제 트랜잭션을 시작 VAL하고 테이블의 거의 모든 행에 대해 업데이트하겠습니다 .

BEGIN TRANSACTION

UPDATE X_TABLE_IX
SET VAL = 'A'
WHERE ID <> 1;

이에 대한 쿼리 계획의 일부는 다음과 같습니다.

빨간색 원은 비 클러스터형 인덱스에 대한 업데이트입니다. 파란색 원은 클러스터 된 인덱스에 대한 업데이트이며 기본적으로 테이블의 데이터입니다. 트랜잭션이 커밋되지 않았더라도 쿼리 실행의 일부로 데이터와 인덱스가 업데이트되는 것을 볼 수 있습니다. 다른 요인과 함께 관련된 데이터의 크기에 따라 계획에서 항상 이것을 볼 수는 없습니다.

트랜잭션이 여전히 커밋되지 않은 상태 SELECT에서 위 의 쿼리를 다시 살펴 보겠습니다 .

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

쿼리 최적화 프로그램은 여전히 ​​인덱스를 사용할 수 있으며 이번에는 999999 개의 행이 반환 될 것으로 예상합니다. 쿼리를 실행하면 예상 결과가 반환됩니다.

그것은 간단한 데모 였지만 희망적으로 조금 정리했습니다.

제쳐두고, 나는 인덱스가 즉시 업데이트되지 않는다고 주장 할 수있는 몇 가지 경우를 알고 있습니다. 이는 성능상의 이유로 수행되며 최종 사용자는 일관되지 않은 데이터를 볼 수 없습니다. 예를 들어, 삭제가 SQL Server의 인덱스에 완전히 적용되지 않는 경우가 있습니다. 백그라운드 프로세스가 실행되어 결국 데이터를 정리합니다. 궁금하다면 고스트 레코드 에 대해 읽을 수 있습니다 .


답변

내 경험에 따르면 1,000,000 행 삽입에는 실제로 배치 삽입을 사용하는 것보다 더 많은 리소스가 필요하고 완료하는 데 시간이 더 걸립니다. 이것은 예를 들어 10,000 개의 행을 100 개의 삽입으로 구현할 수 있습니다.

이렇게하면 삽입되는 배치의 오버 헤드가 줄어들고 배치에 실패하면 더 작은 롤백입니다.

어쨌든 SQL Server의 경우 일괄 삽입을 수행하는 데 사용할 수 있는 bcp 유틸리티 또는 BULK INSERT 명령이 있습니다.

물론이 방법을 처리하기위한 자체 코드를 구현할 수도 있습니다.


답변