커서 잠금

MicrosoftSQL Server에서 커서 정의의 SELECT 문은 다른 SELECT 문에 적용되는 것과 동일한 트랜잭션 잠금 규칙을 따릅니다. 그러나 커서에서는 지정된 커서 동시성 수준에 따라 스크롤 잠금을 추가로 획득할 수 있습니다.

커서 정의의 SELECT 문을 비롯한 SELECT 문에서 획득하는 트랜잭션 잠금은 다음에 의해 제어됩니다.

• 연결에 대한 트랜잭션 격리 수준 설정

• FROM 절에 지정된 모든 잠금 힌트

이러한 잠금은 커서와 독립 SELECT 문 모두에 대해 현재 트랜잭션이 끝날 때까지 보유됩니다. SQL Server가 자동 커밋 모드에서 실행될 때 각 개별 SQL 문이 트랜잭션이며 문이 끝날 때 잠금이 해제됩니다. SQL Server가 명시적 또는 암시적 트랜잭션 모드에서 실행될 때는 트랜잭션이 커밋되거나 롤백될 때까지 잠금이 보유됩니다.

예를 들어 다음 두 Transact-SQL 예제에 대해 수행되는 잠금은 본질적으로 동일합니다.

/* Example 1 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
GO
SELECT * FROM AdventureWorks.Sales.Store;
GO

/* Example 2 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
GO
DECLARE abc CURSOR STATIC FOR
SELECT * FROM AdventureWorks.Sales.Store;
GO
OPEN abc
GO

트랜잭션 격리 수준을 반복 읽기로 설정하면 예제 1의 독립 SELECT 문과 예제 2의 DECLARE CURSOR에 포함된 SELECT 문 모두 읽은 행에 대한 공유 잠금을 생성하며 이 공유 잠금은 트랜잭션이 커밋되거나 롤백될 때까지 보유됩니다.

잠금 획득

커서가 독립 SELECT 문과 동일한 규칙을 따르더라도 획득하는 트랜잭션 잠금 유형에 관계없이 잠금이 획득되는 시기는 다릅니다. 독립 SELECT나 커서에 의해 생성되는 잠금은 항상 행 검색 시 획득됩니다. 독립 SELECT의 경우 문 실행 시 모든 행이 검색됩니다. 그러나 커서의 경우 커서 유형에 따라 행 검색 시기가 다릅니다.

• 정적 커서는 커서가 열릴 때 전체 결과 집합을 검색합니다. 따라서 결과 집합의 각 행은 열릴 때 잠깁니다.

• 키 집합 커서는 커서가 열릴 때 결과 집합에 있는 각 행의 키를 검색합니다. 따라서 결과 집합의 각 행은 열릴 때 잠깁니다.

• 일반 정방향 전용 커서를 비롯한 동적 커서는 인출될 때까지 행을 검색하지 않습니다. 따라서 행이 인출될 때까지 행에 대한 잠금도 획득되지 않습니다.

• 빠른 정방향 전용 커서의 경우 쿼리 최적화 프로그램에서 선택하는 실행 계획에 따라 잠금 획득 시기가 다릅니다. 동적 계획이 선택되는 경우 행이 인출될 때까지 잠금이 획득되지 않습니다. 작업 테이블이 생성되면 행이 작업 테이블로 읽혀지고 열릴 때 잠깁니다.

커서는 자체 동시성 사양도 지원하며 그 중 일부는 각 인출 시 행에 대한 추가 잠금을 생성합니다. 이러한 스크롤 잠금은 다음 인출 작업이 실행되거나 커서가 잠길 때까지 보유됩니다. 커밋 시 커서를 열어두는 연결 옵션을 설정하면 이러한 잠금이 커밋 또는 롤백 작업 중에도 보유됩니다.