MS SQL - LOCK 장애 대응

실행 쿼리

-- 1. LOCK 찾기 : BlkBy 컬럼에 값이 있으면 Lock 걸린 프로세스
EXEC SP_WHO2;

-- 2. LOCK 찾기
SELECT * FROM SYS.sysprocesses WHERE SPID > 50 AND BLOCKED > 0

-- 상세보기
dbcc inputbuffer ( [spid] );

-- Lock 걸린 프로세스 Kill
EXEC KILL [spid]

 

SP_WHO2 활용한 LOCK 쿼리 찾는 SP

• 실행 시, findlock_sp_who2 테이블에 당시 LOCK 로그를 남긴다.
• findlock_sp_who2 테이블의 마지막 EventInfo 필드에는 dbcc inputbuffer([SPID])의 결과 (LOCK 유발 쿼리)를 남긴다.

drop SEQUENCE findlock_seq

CREATE SEQUENCE findlock_seq
    AS [int]
    START WITH 1
    INCREMENT BY 1
GO


drop table findlock_sp_who2

create table findlock_sp_who2 (
	EXEC_DATE VARCHAR(8) NOT NULL,
	SEQ INT  NOT NULL,
	SPID INT  NOT NULL,
	Status VARCHAR(255),
	Login  VARCHAR(255),
	HostName  VARCHAR(255), 
	BlkBy  VARCHAR(255),
	DBName  VARCHAR(255), 
	Command VARCHAR(255),
	CPUTime INT,
	DiskIO INT,
	LastBatch VARCHAR(255), 
	ProgramName VARCHAR(255),
	SPID2 INT, 
	REQUESTID INT,
	SAVE_TIME DATETIME,
	EVENTType  varchar(100) ,
	Parameters varchar(100) ,
	EventInfo varchar(8000) ,
	--PRIMARY KEY(SEQ, SPID)
)
ALTER TABLE findlock_sp_who2 ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY (SEQ, SPID)
------------------------------------------------------------



DROP PROCEDURE findLock;
DELETE FROM findlock_sp_who2;
--------------------------------------------------------------------
CREATE PROCEDURE findLock
AS
BEGIN
-- 켜서변수

DECLARE @EVENTType		VARCHAR(100)
DECLARE @Parameters		VARCHAR(100)
DECLARE @EventInfo		VARCHAR(8000)

DECLARE @SPID				INT

-- 테이블 변수 : inputbuffer 결과값 저장
 DECLARE @findlock_inputbuffer TABLE ( 
	EVENTType  varchar(100) ,
	Parameters varchar(100) ,
	EventInfo varchar(8000)
)

-- 시퀀스를 담기 위한 변수
DECLARE @findlock_seq INT;
SET @findlock_seq = NEXT VALUE FOR findlock_seq;

-- 테이블 변수
DECLARE @findlock_sp_who2 TABLE (
	SPID INT,
	Status VARCHAR(255),
	Login  VARCHAR(255),
	HostName  VARCHAR(255), 
	BlkBy  VARCHAR(255),
	DBName  VARCHAR(255), 
	Command VARCHAR(255),
	CPUTime INT,
	DiskIO INT,
	LastBatch VARCHAR(255), 
	ProgramName VARCHAR(255),
	SPID2 INT, 
	REQUESTID INT
)

-- 임시 테이블에 담아
INSERT INTO @findlock_sp_who2  EXEC sp_who2

-- 영속 테이블에 저장 + 시퀀스필드 + 날짜
INSERT INTO findlock_sp_who2(
	EXEC_DATE 	
  , SEQ
  , SPID
  , Status
  , Login
  , HostName
  , BlkBy
  , DBName
  , Command
  , CPUTime
  , DiskIO
  , LastBatch
  , ProgramName
  , SPID2
  , REQUESTID
  , SAVE_TIME
) SELECT 
	CONVERT(CHAR(8), GETDATE(), 112)
  , @findlock_seq
  , SPID
  , Status
  , Login
  , HostName
  , BlkBy
  , DBName
  , Command
  , CPUTime
  , DiskIO
  , LastBatch
  , ProgramName
  , SPID2
  , REQUESTID
  , GETDATE()
FROM @findlock_sp_who2
--WHERE
--	Status = 'SUSPENDED'

------------	
-- 반복처리

DECLARE CUR_LOOP CURSOR FOR 
 SELECT 
	SPID
FROM @findlock_sp_who2
WHERE
	BlkBy != '  .'
	--Status = 'SUSPENDED'

OPEN CUR_LOOP
FETCH NEXT FROM CUR_LOOP INTO @SPID	

WHILE @@FETCH_STATUS = 0
BEGIN

-- 반복 작업 시작

	-- 임시 테이블에 담아
	INSERT INTO @findlock_inputbuffer EXEC ('dbcc inputbuffer (' + @SPID + ')')
	
	-- 값 추출 후
	SELECT
		TOP 1
		@EVENTType = EVENTType
	  , @Parameters = Parameters	 
	  , @EventInfo = EventInfo
	FROM 
		@findlock_inputbuffer

	UPDATE 
		findlock_sp_who2
	SET
		EVENTType = @EVENTType
	  , Parameters = @Parameters
	  , EventInfo = @EventInfo
	WHERE
		SEQ = @findlock_seq
		AND SPID = @SPID

	-- 임시 테이블 삭제
	DELETE FROM @findlock_inputbuffer


-- 반복 작업 끝

FETCH NEXT FROM CUR_LOOP INTO @SPID	-- 다음 ROW로 이동
END
-- 켜서 닫고 초기화
CLOSE CUR_LOOP
DEALLOCATE CUR_LOOP

---------------

SELECT
	TOP 1000 *
FROM
	findlock_sp_who2
WHERE
	Status = 'SUSPENDED'
ORDER BY
	SEQ DESC, SPID ASC

PRINT '로그 데이터는 findlock_sp_who2 테이블을 확인하세요'
END
GO

EXEC findLock

 

LOCK 찾는 계층 쿼리

• sp_who2의 계층 구조를 분석하여, 부모 LOCK을 찾는다.

실행 결과

CREATE PROCEDURE findLock2
AS
BEGIN

-- 테이블 변수
DECLARE @findlock_sp_who2 TABLE (
	SPID INT,
	Status VARCHAR(255),
	Login  VARCHAR(255),
	HostName  VARCHAR(255), 
	BlkBy  VARCHAR(255),
	DBName  VARCHAR(255), 
	Command VARCHAR(255),
	CPUTime INT,
	DiskIO INT,
	LastBatch VARCHAR(255), 
	ProgramName VARCHAR(255),
	SPID2 INT, 
	REQUESTID INT
)

-- 임시 테이블에 담아
INSERT INTO @findlock_sp_who2  EXEC sp_who2;

-- 계층 쿼리 실행
WITH tree_table AS (
	SELECT
		SPID id
	  , IIF(BlkBy = '  .', 0, BlkBy) parent_id
	  , SPID name
	FROM
		@findlock_sp_who2 
), tree_query AS (

	SELECT
		id
	  , parent_id
	  , name
	  , convert(varchar(255), id) sort
	  , convert(varchar(255), name) depth_fullname
	FROM
		tree_table A
	WHERE
		parent_id = 0

	UNION ALL

	SELECT
		B.id
	  , B.parent_id
	  , B.name
	  , convert(varchar(255), convert(nvarchar,C.sort) + ' > ' +  convert(varchar(255), B.id)) sort
	  , convert(varchar(255), convert(nvarchar,C.depth_fullname) + ' > ' +  convert(varchar(255), B.name)) depth_fullname
	FROM
		tree_table B, tree_query C
	WHERE
		B.parent_id = C.id

)

SELECT id, parent_id, name, depth_fullname FROM tree_query 
where
	parent_id != 0 
order by SORT

END

 

참고 : https://roqkffhwk.tistory.com/140

---

추가

dbcc inputbuffer (X)  테이블에 값 넣기

create table #test
( EVENTType  varchar(100) ,
  Parameters varchar(100) ,
  EventInfo varchar(8000))

insert into #test
exec ( 'dbcc inputbuffer (76)')

select * from #test

 

SSMS의 LOCK 추적 기능

LOCK 추적 시작 명령어 > DBCC TRACEON (1222,-1)
LOCK 추적 중지 명령어 > DBCC TRACEOFF (1222,-1)

http://tech.ubcare.co.kr/post/2019/04/30/sqlserver-deadlock-diagnosis.html