인덱스 잠금 사용자 지정
SQL Server 데이터베이스 엔진은 대부분의 경우 쿼리에 가장 적합한 잠금 세분성을 자동으로 선택하는 동적 잠금 전략을 사용합니다. 페이지 잠금 및 행 잠금이 설정되어 있는 기본 잠금 수준은 잘 알려져 있으며 일관적인 테이블 또는 인덱스 액세스 패턴이 아닌 경우 및 리소스 충돌 문제를 해결해야 하는 경우 재정의하지 않는 것이 좋습니다. 잠금 수준을 재정의하면 테이블 또는 인덱스에 대한 동시 액세스에 심각한 방해가 될 수 있습니다. 예를 들어 사용자의 액세스가 빈번한 대규모 테이블에 테이블 수준 잠금만을 지정하는 경우, 사용자가 테이블에 액세스하려면 테이블 수준 잠금이 해제될 때까지 기다려야 하므로 병목 현상이 발생할 수 있습니다.
잘 알려져 있으며 일관된 액세스 패턴의 경우 페이지 잠금 또는 행 잠금을 허용하지 않는 것이 좋은 경우가 있습니다. 예를 들어 데이터베이스 응용 프로그램에서 일괄 처리를 통해 매주 업데이트되는 조회 테이블을 사용하는 경우를 가정합니다. 동시 판독기는 공유(S) 잠금으로 테이블에 액세스하고, 주간 일괄 업데이트는 배타적(X) 잠금으로 테이블에 액세스합니다. 테이블에서 페이지 및 행 잠금을 해제하면 판독기가 공유 테이블 잠금을 통해 동시에 테이블에 액세스할 수 있어 주중 잠금 오버헤드가 줄어듭니다. 일괄 작업을 실행하면 배타적 테이블 잠금을 얻어 업데이트를 효율적으로 완료할 수 있습니다.
주간 일괄 업데이트는 업데이트 실행 동안 동시 판독기의 테이블 액세스를 차단하므로 페이지 및 행 잠금 해제가 허용될 수도, 그렇지 않을 수도 있습니다. 행 또는 페이지만 변경하는 일괄 작업의 경우, 행 또는 페이지 수준 잠금을 허용하도록 잠금 수준을 변경하여 다른 세션에서 차단 없이 테이블에서 읽기를 수행하도록 허용할 수 있습니다. 업데이트가 다수 포함된 일괄 작업의 경우, 테이블에 대한 배타적 잠금이 효율적인 일괄 작업 완수를 위한 최상의 방법일 수 있습니다.
2건의 동시 작업에서 모두 페이지 잠금이 필요하여 동일 테이블에서 행 잠금을 구한 다음 차단함에 따라 교착 상태가 발생하는 경우가 있습니다. 행 잠금을 허용하지 않으면 두 작업 중 하나를 대기시켜 교착 상태를 방지합니다.
인덱스에 사용되는 잠금의 세분성은 CREATE INDEX 및 ALTER INDEX 문을 사용하여 설정할 수 있습니다. 잠금 설정은 인덱스 페이지와 테이블 페이지에 모두 적용됩니다. 또한 CREATE TABLE 및 ALTER TABLE 문을 사용하여 PRIMARY KEY 및 UNIQUE 제약 조건에 대한 잠금 세분성을 설정할 수도 있습니다. 이전 버전과의 호환성을 위해 sp_indexoption 시스템 저장 프로시저에서도 세분성을 설정할 수 있습니다. 특정 인덱스에 대한 현재 잠금 옵션을 표시하려면 INDEXPROPERTY 함수를 사용합니다. 페이지 수준 잠금, 행 수준 잠금 또는 페이지 수준 및 행 수준 잠금의 혼합 방법은 인덱스에 따라 허용되지 않을 수 있습니다.
허용되지 않는 잠금 |
허용되는 잠금 |
|---|---|
페이지 수준 |
행 수준 및 테이블 수준 잠금 |
행 수준 |
페이지 수준 및 테이블 수준 잠금 |
페이지 수준 및 행 수준 |
테이블 수준 잠금 |