MSSQL에 LOCK 이 걸렸을 경우

1. sp_lock

 

sp_lock 결과 예시

Mode 가 X 혹은 IX인 경우 해당 세션은 LOCK 이 걸린 상태

spid = 세션id

 

2. dbcc inputbuffer(spid)

dbcc inputbuffer 결과 예시

EventInfo 컬럼으로 어떤 쿼리를 실행하고 있는지 확인할 수 있다.

개발 중인 쿼리였다면 Kill spid 로 해당 세션을 종료시키고 끝내면 되겠지만

멀쩡히 잘 돌아가던 쿼리였다면....??

 

3. sp_who 혹은 sp_who2

sp_who 결과 예시

해당 쿼리를 수행 중인 PC를 확인할 수 있다. loginname은 db 접속ID , hostname 이 PC 이름

hostname 이 없는 건 sql server 내부로직에 의한 작업들이다. 무시해도 된다.

자 누가 실행하고 있는지를 알았으니 , 죽이기 전에 미리 양해는 구해야겠지

 

4. 다시 sp_lock

Type 중에 TAB 인 행을 보자. 테이블에 LOCK이 걸렸음을 확인할 수 있다.

해당 행의 Objid 값을 넣어 확인해보자

 

SELECT * FROM sysobjects WHERE  id = ObjId

결과 예시. 이름은 지웠다

테이블명이 나온다. 아 이 테이블에서 부하가 걸리는구나.

만약에 인덱스가 있다면 리빌드 해주자

ALTER INDEX ALL ON DBO.테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90);

 

어.... 그런데도 문제가 해결되지 않는다.

 

5. 상호충돌하는 쿼리가 있는게 아닐까?

 

https://infrastructureland.wordpress.com/2015/01/24/how-to-release-or-remove-lock-on-a-table-sql-server/

HOW TO RELEASE OR REMOVE LOCK ON A TABLE SQL SERVER

구글님께서 검색결과를 윤허하셨다.

 

결론만 이야기하면 아래 쿼리를 실행했을때 상호 충돌하는 세션을 확인할 수 있다.

 

SELECT

blocking_session_id AS BlockingSessionID,

session_id AS VictimSessionID,

(SELECT [text] FROM sys.sysprocesses

CROSS APPLY sys.dm_exec_sql_text([sql_handle])

WHERE spid = blocking_session_id) AS BlockingQuery,

[text] AS VictimQuery,

wait_time/1000 AS WaitDurationSecond,

wait_type AS WaitType,

percent_complete AS BlockingQueryCompletePercent

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text([sql_handle])

WHERE blocking_session_id > 0

 

blocking_session_id : 문제가 발생한 세션

session_id : 피해자 세션

 

즉 문제가 발생한 세션을 죽이면 나머지는 정상작동한다.

그럼에도 문제가 발생한다면....?

 

6. 로그 파일 축소

 

USE [데이터베이스명];
GO
ALTER database [데이터베이스명]
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ([데이터베이스명_log], 1);
GO

-- Reset the vitzro recovery model.
ALTER database [데이터베이스명]
SET RECOVERY FULL;
GO

 

중요

1) Insert , Update , Delete 를 수행하면 로그가 쌓인다

2) 사용하는 프로그램이 트랜잭션을 수행한다면 쿼리가 끝나기 전엔 로그파일을 축소할 수 없다.

3) 눈 딱 감고 , 모든 세션을 죽여버리고 해당 쿼리를 수행하자.

 

아멘

 

번외

문제의 원인은 무엇인가

1) 동일테이블에서 트리거와 프로시저 혹은 IUD 쿼리가 동시에 수행되는 경우 발생하는 문제

    앞의 쿼리가 원활하게 수행되어야 하는데 이게 물리면서 발생한 문제....?

 

2) 이건 뇌피셜. 트랜잭션이 정상적으로 종료되지 않은 경우 해당 테이블이 락이 걸린 상태에서

    세션이 종료되는 경우가 있다. 세션은 없는데 왜 락만 남아있는거냐....

    아마 일반 로그 이외에 트랜잭션 로그가 있고 , 정상종료되지 않으면서 해당 테이블 락 상태가

    유지되는게 아닐까...? 로그를 날려버리면 되겠다 싶어서 축소해보니 문제해결됨.

    로그축소와 부하가 발생하는 테이블의 인덱스 생성 및 리빌드는 스케줄로 걸어놓자

    어차피 MSSQL 의 로그는 상용 솔루션을 쓰지 않는 이상 내용을 써먹기도 어려우니 문제될 건 없다

 

3) 부하가 발생하는 쿼리를 확인할 때 사용하는 것 중 유명한 건 sp_whoisactive 가 있다.

    문제라면 2018년 이후에 업데이트가 멈췄다는거 정도이려나... 

    만들어두는 것도 나쁘지 않을 것 같다.

 

https://whoisactive.com/downloads/

sp_whoisactive SQL Server Monitoring Stored Procedure Downloads