[MS-SQL] Lock(잠금)

안녕하세요. 

 

MS-SQL에서의 Lock(잠금) 확인방법과 처리 방법에 대하여 설명드리겠습니다.

Lock이란 트랜잭션 처리를 위해 순차성을 보장하기 위한 것 입니다.

먼저, Lock의 종류입니다.

1. 공유잠금(Shared Lock) : SELECT (Read Lock)

 - 공유 Lock은 데이터를 읽을때 사용되는 Lock

 - 공유 Lock 끼리는 동시 접근 가능

2. 배타적잠금(Exclusive lock) : INSERT, UPDATE, DELETE (Write Lock)

 - 배타 Lock은 데이터를 변경하고자 할때

 - 배타 Lock은 Lock이 해제될 때 까지 다른 트랜잭션이 해당 리소스에 접근 불가

 

반응형

1. SP_LOCK

아래의 명령어로 Lock을 조회한다.

EXEC SP_LOCK;

SP_LOCK 실행 결과

Mode 종류

① S : 공유. 보유 중인 세션이 리소스에 공유된 액세스를 할 수 있도록 권한을 부여합니다.

② U : 업데이트. 업데이트될 리소스에 대해 업데이트 잠금을 획득하도록 합니다. 이후에 업데이트할 가능성을 위해 여러 세션이 리소스를 잠그는 경우 발생하는 일반적인 형태의 교착 상태를 방지하기 위해 사용합니다.

③ U : 업데이트. 업데이트될 리소스에 대해 업데이트 잠금을 획득하도록 합니다. 이후에 업데이트할 가능성을 위해 여러 세션이 리소스를 잠그는 경우 발생하는 일반적인 형태의 교착 상태를 방지하기 위해 사용합니다.

④ IS : 내재된 공유. 잠금 계층 구조의 일부 하위 리소스에 S 잠금을 설정하려는 의도를 표시합니다.

⑤ IU : 의도 업데이트. 잠금 계층 구조의 일부 하위 리소스에 U 잠금을 설정하려는 의도를 표시합니다.

⑥ IX : 의도 배타. 잠금 계층 구조의 일부 하위 리소스에 X 잠금을 설정하려는 의도를 표시합니다.

 

 ※ 보통 위의 항목이 많이 활용됨 기타 항목은 https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-ver16 참조

 

2. SP_WHO

 

EXEC SP_WHO;

SP_WHO 실행 결과

또는

EXEC SP_WHO2;

실행

 

SP_WHO2 실행 결과

※ SP_WHO2에서는 BlkBy 칼럼에 값이 있으면 Lock 걸린 프로세스 이다. SP_WHO를 통하여 어느에서 Lock을 발생했는지 확인 할수 있다. 예를 들어 테스트 중이나 개발 중 Lock이 발생도 가능하다. 이러한 경우 프로세스가 문제가 아닌 개발 중 문제에 대하여 파악도 가능하다. 상황에 따라 활용을 하여야 하는 부분이다.

 

3.dbcc inputbuffer

Lock이 발생한 내용을 확인한다.

dbcc inputbuffer ( [spid] );

 

dbcc inputbuffer 실행 결과

 

※ 이후 Lock이 발생한 원인을 확인하여, 프로시저를 다시 튜닝하는것이 좋다.

 

4. KILL

③번에서 쿼리를 확인하여 프로세스에 문제가 없는 내용인지 확인을 하여 KILL로 프로세스를 죽인다.

EXEC KILL [spid];

※ 해당 프로세스를 KILL하여도 프로그램에서 문제가 없는지 확인하여 처리하는것이 좋다.