MSSQL LOCK 해결하는 방법

 

MSSQL LOCK 해결하는 방법

 

우리가 데이터베이스에 엑세스하다 보면 조회 속도가 현저하게 느려지거나 아예 엑세스 자체도 불가능할 때도 있다.
이런 경우 LOCK를 의심해 봐야 하는데 오늘은 이런 LOCK를 확인하고 해결하는 방법을 알아보자.

 

LOCK은?

LOCK은 잠금을 의미하는데 어떤 데이터베이스 자원에 대해 자신이 사용하고 있음을 표시는 것이다.
즉 자신이 사용하고 있으므로 다른 사용자 연결에 대하여 엑세스를 제안하게 되는 것이다.
결국 변경 중인 데이터에 대하여는 읽을 수 없게 하거나 수정할 수 없게 하여 데이터의 일관성을 유지하기 위함이다.

 

 

LOCK 즉 잠금은 결국 동시성에 대한 문제로 누군가 자원을 먼저 사용하고 있다면 어떤 형태로든 잠금을 설정하여 여러 사용자가 동시에 같은 데이터를 변경할 수 없게 한다.
만약 잠금을 사용하지 않으면 데이터베이스의 데이터는 논리적으로 잘못될 가능성이 매우 크고, 이것은 데이터베이스 자체에 대한 심각한 오류의 요소가 된다.

 

SQL Server는 이런 LOCK 잠금기법을 사용하여 데이터베이스 무결성 및 일관성을 유지한다. 

 

LOCK의 종류

MSSQL에서 잠금은 여러 종류가 있지만, 대표적으로 공유 잠금(SHARED LOCK)과 배타적 잠금(EXCLUSIVE LOCK)으로 나눌 수 있다.

공유 잠금은 SELECT 문에 의해 설정되고, 배타적 잠금은 DML(INSERT,UPDATE,DELETE) 에 의해서 설정된다.

 

 

공유 잠금(SHARED LOCK) - SELECT

공유 잠금은 데이터를 수정하거나 삭제, 업데이트 하지 않는, 읽는 동안에만 설정 된다.
공유 잠금이 설정되어도 여러 트랜잭션이 동시에 하나의 개체를 읽을 수 있다.
즉 공유 잠금은 다른 공유 잠금과 호환되며, 공유 잠금끼리는 서로 충돌되지 않는다.
그러나 다른 배타적 잠금은 허용하지 않는다.

 

배타적 잠금(EXCLUSIVE LOCK) - INSERT, UPDATE, DELETE

배타적 잠금 설정이 되면 어떤한 연결도 해당 리소스(테이블 혹은 데이터)에 엑세스하지 못하도록 한다.
배타적 잠금을 설정하고 있으면 다른 엑세스하는 연결 시도는 BLOCKING 당하게 된다.
그리고 이 상태는 잠금이 풀리때까지 지속된다.
만약 이 상태를 해제를 하려면 해당 트랜잭션을 완료하거나 프로세스를 강제로 종료(KILL) 해야 한다.
이것이 바로 배타적 잠금이다.

 

MSSQL LOCK 해결하는 방법

1. LOCK 확인하는 방법

테이블이 잠겼는지 확인하는 방법은 다음과 같은 방법으로 확인할 수 있다.

 

EXEC sp_lock 

Mode 컬럼 값이 X 이면 LOCKDL 발생한 프로세스이다.

 

EXEC sp_who2

BlkBy 컬럼이 . 이 아니면 LOCK이 발생한 프로세스이다.

 

SELECT * FROM SYS.sysprocesses WHERE blocked > 0

block 컬럼이 0 이상이면 LOCK이 발생한 프로세스이다.

 

2. 마지막으로 실행한 쿼리 조회하는 방법

 

DBCC inputbuffer ([SPID])

 

3. LOCK이 발생한 SPID를 강제 KILL(종료) 하는 방법 

 

KILL [SPID]