[MSSQL] Lock 상황 유발해보고, Lock을 유발하는 세션 찾아서 종료시키기

Lock 유발시키기에 앞서, 아래 쿼리는 Lock을 인위적으로 유발하기 위해 사용하는 쿼리로, 인위적으로 트랜잭션의 수행시간을 1시간으로 지정한다.

현업의 운영 DB에서는 해당 쿼리를 절대 사용하면 안된다. 반드시 테스트 용도로만 사용해야 한다.

 

1. 테이블 Lock 걸리도록 하는 쿼리 수행

-- 테스트 DB로 SQL Server 에서 공식적으로 제공하는 'AdventureWorks2019' 샘플 DB를 사용한다.
USE AdventureWorks2019

-- 테스트 해볼 row를 조회하는 쿼리
SELECT ProductID, Name
FROM Production.Product
WHERE ProductID=999
-- 수행 결과 : ProductID: 999 / Name: Road-750 Black, 52

BEGIN TRAN
	UPDATE Production.Product SET Name='test'
    WHERE ProductID=999
    
    WAITFOR DELAY '01:00:00' 	-- 트랜잭션은 임의로 1시간 delay
   
COMMIT TRAN

 

위 쿼리를 수행한 후, 동일한 DB의 새 쿼리 창을 연 다음 아래 쿼리를 수행해보자.

SELECT ProductID, Name
FROM AdventureWorks2019.Production.Product
WHERE ProductID=999

 

그럼 결과는 아래와 같이, 당연하게도 1시간동안 트랜잭션으로 해당 테이블을 Lock 걸게 만들었으니, SELECT 쿼리가 수행되지 않을 것이다.

Lock 걸린 테이블을 조회해서 쿼리가 지연되는 상황

---

2. 현재 Lock 을 유발하는 쿼리가 사용하고 있는 데이터베이스의 ID 확인

해당 데이터베이스가 Lock 걸린 상황인 것을 확인했다면, 이제 그 데이터베이스의 개체 ID를 조회해보자!

'sys.dm_tran_locks' T-SQL 을 사용하기 위해 DB_ID를 확인하는 것이다. 

-- name='Lock 걸린 데이터베이스 이름'
SELECT database_id, name FROM sys.databases
WHERE name = 'AdventureWorks2019'

Lock 걸린 데이터베이스인 'AdventureWorks2019' 의 DB_ID 확인

---

3. Lock 정보를 확인하기

-- Lock 정보를 조회하는 쿼리
-- resource_database_id = <dbid> 값에 위에서 조회한 데이터베이스의 ID를 넣어야 함.
SELECT resource_type, resource_associated_entity_id,  
    request_status, request_mode,request_session_id,  
    resource_description   
FROM sys.dm_tran_locks  
WHERE resource_database_id = 5

'sp_lock' 을 사용해도 되나, 이는 SQL Server 2016 버전 이후부터는 Deprecated 되어 더이상 권장하지 않는 방법이므로, 'sys.dm_tran_locks' 를 사용하자!

sys.dm_tran_locks 결과

위 결과를 보면, mode가 IX로 배타적 잠금에 걸린 걸 확인할 수 있다. Lock 걸린 개체(테이블)의 entity ID는 '72057594061783040' 인 것을 확인할 수 있고, Lock 을 잡고 있는 세션의 ID는 '52' 인 것을 기억해두자!

 

아래 쿼리는 entity ID 로 object ID를 알아보기 위한 쿼리이다. 참고로 알아두자.

SELECT object_name(object_id), *  
FROM sys.partitions  
WHERE hobt_id=72057594061783040

sys.partitions 쿼리 수행 결과

---

4. Lock 을 유발시키는 세션 ID를 통해 해당 세션이 수행한 스크립트를 확인해보자

-- Lock 을 유발시키는 Session_ID를 통해 해당 세션이 수행한 스크립트를 확인하는 T-SQL
-- DBCC INPUTBUFFER ('session_id')
DBCC INPUTBUFFER (52)

Lock 유발한 범인을 확인할 수 있다.

 

---

5. Lock을 유발한 세션 종료시키기

mass update 성격의 쿼리가 수행되는 세션을 함부로 종료했다가는 데이터의 정합성이 다 깨져버리니, 세션을 강제 종료시킬 때에는 정말 신중해야 한다.

 

※ 해당 세션을 종료시키게 되면, 쿼리가 중간에 수행되다 중단되는 것이기 때문에, 반드시 해당 애플리케이션 오너와 상의가 필요하다. 예를 들어, 회사에서 급여를 올리는 batch 를 돌렸는데 그 세션을 중간에 종료시켜버리면, 어떤 사람은 급여가 올라갔는데, 어떤 사람은 올라가지 않은 불상사가 발생할 수 있다.

 

-- 종료를 해도 되는 세션이라면 강제로 종료시키는 방법
-- 여기서는 일부러 딜레이 시키는 쿼리를 수행하는 세션이므로 종료시켜도 된다.
-- KILL 'session_id'
KILL 52

---

6. 원래 수행하려던 쿼리 수행하여 Lock 해제된 것 확인하기.

SELECT ProductID, Name
FROM AdventureWorks2019.Production.Product
WHERE ProductID=999

AdventureWorks2019 DB 의 Lock 이 해제되어 정상적으로 데이터가 조회된다.

---

참고

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver16 

sys.dm_tran_locks(Transact-SQL) - SQL Server