[MsSQL] SQL Server 2008 시스템 프로시저 sp_lock 알아보기

MS-SQL 의 시스템 프로시저에 대해 알아봅니다.

sp_lock 은 현재 잠금에 대한 정보를 보고합니다.

 sp_lock : 모든 잠근 나열에 대한 정보 출력

 sp_lock 53 : SPID 가 53에 대한 잠금을 포함한 정보를 출력

sp_lock 의 정보를 보기위해서 락을 발생시킵니다.

1. SSMS 창에서 BEGIN TRAN 을 사용하여 트랜잭션을 발생시킵니다.

2. 다른 SSMS 창(다른 세션)에서 트랜잭션이 걸린 테이블에 대한 데이터를 조회합니다.

WITH(NO LOCK) 을 사용하지 않았기 때문에 락이 발생합니다.

3. EXEC sp_lock 을 수행하여, 락에 대해서 조회합니다.

sp_lock	설명
spid	잠금을 요청하는 프로세스의 데이터베이스 엔진 세션 ID  SELECT @@SPID 로 확인 가능
dbid	잠금이 설정된 데이터베이스의 ID  SELECT DB_ID() 로 확인 가능
ObjId	잠금이 설정된 개체의 ID  SELECT OBJECT_NAME(ObjId) 로 확인 가능
IndId	잠금이 설정된 인덱스의 ID
Type	잠금 유형  * RID : 행 식별자로 식별되는 테이블의 단일 행에 대한 잠금  * KEY : 직렬화 할 수 있는 트랜잭션에서 키의 범위를 보호하는 인덱스 내의 잠금  * PAG : 데이터 또는 인덱스 페이지에 대한 잠금  * EXT : 익스텐트에 대한 잠금  * TAB : 모든 데이터와 인덱스가 포함된 전체 테이블에 대한 잠금  * DB : 데이터베이스에 대한 잠금  * FIL : 데이터베이스 파일에 대한 잠금  * APP : 응용 프로그램이 지정한 리소스에 대한 잠금  * MD : 메타데이터 혹은 카탈로그 정보에 대한 잠금  * HBT : 힙 또는 B-트리 인덱스에 대한 잠금  * AU : 할당 단위에 대한 잠금
Resource	잠금 리소스를 식별하는 값  각 Type 별로 아래 표에서 다시한번 설명합니다.
Mode	요청한 잠금모드  * NULL : 리소스에 대해 허가된 액세스가 없음  * Sch-S : 특정 세션이 스키마 요소에 대해 스키마 안전성 잠금을 보유하고 있는 동안 테이블 또는 인덱스 등의 스키마 요소가 삭제되지 않도록 함  * Sch-M : 스키마 수정. 지정한 리소스의 스키마를 변경하려는 세션이 보유해야 하는 잠금 모드. 다른 세션이 표시된 개체를 참조하지 않도록 해야함  * S : 공유. 보유 중인 세션이 리소스에 공유된 액세스를 할 수 있도록 권한을 부여  * U : 업데이트. 업데이트될 리소스에 대해 업데이트 잠금을 획득  * X : 배타. 보유 중인 세션이 리소스에 배타적으로 액세스 할 수 있도록 권한을 부여  * IS : 의도 공유. 잠금 계층 구조의 일부 하위 리소스에 S 잠금을 설정하려는 의도를 표시  * IU : 의도 업데이트. 잠금 계층 구조의 일부 하위 리소스에 X 잠금을 설정하려는 의도를 표시  * IX : 의도 배타. 잠금 계층 구조의 일부 하위 리소스에 X 잠금을 설정하려는 의도를 표시  * SIU : 공유 의도 업데이트. 잠금 계층 구조의 하위 리소스에 대한 업데이트 잠금을 획득하기 위해 리소스에 대한 공유된 액세스를 표시  * SIX : 공유 의도 배타. 잠금 계층 구조의 하위 리소스에 대한 배타적 잠금을 획득하기 위해 리소스에 대한 공유된 액세스를 표시  * UIX : 업데이트 의도 배타. 잠금 계층 구조의 하위 리소스에 대한 배타적 잠금을 획득하기 위해 리소스에 업데이트 잠금을 보유함을 표시  * BU : 대량 업데이트. 대량 작업에 사용  * RangeS_S : 공유 키 범위 및 공유 리소스 잠금. 직렬화 가능 범위 검색 표시  * RangeS_U : 공유 키 범위 및 업데이트 리소스 잠금. 직렬화 가능 업데이트 검색을 표시  * RangeI_N : 삽입 키 범위 Null 리소스 잠금. 새 키를 인덱스에 삽입하기 전에 범위를 테스트하는데 사용  * RangeI_S : 키 범위 변환 잠금. RangeI_N 및 S 잠금의 겹침으로 생성  * RangeI_U : RangeI_N 및 U 의 잠금의 겹침으로 생성된 키 범위 변환 잠금  * RangeI_X : RangeI_N 및 X 잠금의 겹침으로 생성된 키 범위 변환 잠금  * RangeI_X_S : RangeI_N 및 RANGES_S 잠금의 겹침으로 생성된 키 범위 변환 잠금  * RangeI_X_U : RangeI_N 및 RangeS_U 잠금의 겹침으로 생성된 키 범위 변환 잠금  * RangeX_X : 배타 키 범위 및 배타 리소스 잠금. 범위 내에서 키를 업데이트할 때 사용되는 변환 잠금
Status	잠금 요청 상태  * CNVRT : 다른 모드에서 잠금을 변환하는 중 충돌하는 모드의 잠금을 보유한 다른 프로세스로 인해 변환이 차단  * GRANT : 잠금을 획득  * WAIT : 충돌하는 모드의 잠금을 보유한 다른 프소세스로 인해 잠금이 차단

Resource	설명
RID	fileid: pagenumber 형식  fileid는 페이지가 포함된 파일을 식별  pagenumber는 행이 포함된 페이지를 식별하며 rid는 페이지에서 특정 행을 식별  fileid는 sys.database_files 카탈로그 뷰에 있는 file_id 와 일치
KEY	데이터베이스 엔진에서 내부적으로 사용되는 16진수
PAG	fileid: pagenumber 형식  fileid는 페이지가 포함된 파일을 식별  pagenumber는 행이 포함된 페이지를 식별
EXT	익스텐트의 첫 번째 페이지를 식별하는 번호  fileid: pagenumber 형식
TAB	ObjId에서 이미 식별되었으므로 정보가 제공되지 않음
DB	DBId에서 이미 식별되었으므로 정보가 제공되지 않음
FIL	sys.database_files 카탈로그 뷰에 있는 file_id 열과 일치하는 파일의 식별자
APP	잠긴 응용 프로그램 리소스의 고유 식별자  형식은 DbPrincipleId:<리소스 문자열의 처음 2자부터 16자까지><해시된 값>
MD	리소스 유형에 따라 달라지며 자세한 내용은  sys.dm_tran_locks의 resource_description 열을 참조
HBT	정보가 제공되지 않음  sys.dm_tran_locks 동적 관리 뷰를 대신 사용
AI	정보가 제공되지 않음  sys.dm_tran_locks 동적 관리 뷰를 대신 사용

락은 성능저하에 주요한 영향을 미치기 때문에, 락이 발생해서는 안됩니다.

락이 발생하는 경우에는, 해당 문제에 대한 원인을 꼭 조사한 뒤 해결해야 합니다.