[MS SQL] 열려 있는 트랜잭션과 잠금 상태를 확인하는 쿼리

SQL Server에서 트랜잭션은 데이터베이스에서 실행하는 작업의 논리적인 단위를 나타냅니다. 트랜잭션이 열려 있는 경우, 해당 트랜잭션이 어떤 데이터를 변경하고 있는지를 추적할 수 있습니다. 그러나 여러 세션이 동시에 같은 데이터에 접근하려 할 때 충돌이 발생할 수 있습니다. 이러한 충돌을 방지하기 위해 SQL Server는 잠금(locking) 메커니즘을 사용합니다.

아래 쿼리는 데이터베이스에서 현재 실행 중인 세션 중에서 열려 있는 트랜잭션을 갖고 있으며, 다른 세션에 의해 잠금이 발생한 경우에 해당하는 정보를 제공합니다. 즉, 어떤 세션은 데이터를 변경하고 있고, 다른 세션은 해당 데이터에 대한 접근을 시도하면서 잠금이 발생한 상황을 추적할 수 있습니다. 이는 데이터베이스 성능 이슈나 데드락 등을 해결하는 데 도움이 됩니다.

전체 쿼리

SELECT
	p.spid
	, p.cmd
	, p.status
	, p.loginame
	, p.hostname
	, p.open_tran
	, datediff(s, p.last_batch, getdate()) as [wait tims(s)]
	, q.text

FROM
	master.dbo.sysprocesses AS p WITH(NOLOCK)
	CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) AS q

WHERE
	p.open_tran > 0
	AND p.spid > 50
	AND EXISTS ( 
					SELECT 1 
					FROM master.dbo.syslockinfo  -- 잠금 정보를 제공하는 테이블
					WHERE req_spid = p.spid and rsc_type <> 2 
				)
				
ORDER BY datediff(s, p.last_batch, getdate()) DESC

세부 설명

FROM

• master.dbo.sysprocesses AS p WITH(NOLOCK) :
  현재 실행 중인 프로세스 정보를 가져오는 테이블입니다.
  NOLOCK 힌트는 다른 트랜잭션에 의한 차단을 피하기 위해 사용됩니다.

• CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS q :
  sys.dm_exec_sql_text 함수를 사용하여
  SQL 핸들에 대한 실행 중인 쿼리 텍스트를 가져오는데 사용됩니다.

CROSS APPLY

CROSS APPLY는 주로 함수나 테이블 값 함수 (Table-Valued Function)을 적용할 때 유용하게 사용됩니다. 여러 행에 대해 함수를 적용하고 싶을 때 유용합니다.

이 쿼리에서 CROSS APPLY는 sys.dm_exec_sql_text(p.sql_handle) 함수를 각 세션의 sql_handle에 적용하여 해당 세션의 SQL 텍스트를 가져오고 있습니다. 결과적으로 각 세션에 대한 SQL 텍스트가 함께 반환되어 원하는 정보를 효과적으로 얻을 수 있게 됩니다.

WHERE

• p.open_tran > 0 :
  열려 있는 트랜잭션이 있는 세션만을 선택합니다.

• p.spid > 50 :
  시스템 프로세스를 필터링하여 일반적인 사용자 세션을 선택합니다.

• EXISTS (SELECT 1 FROM master.dbo.syslockinfo WHERE req_spid = p.spid and rsc_type <> 2) :
  syslockinfo 테이블을 사용하여 특정 세션이 다른 세션의 잠금을 요청하고 있는지 확인합니다.

  master.dbo.syslockinfo 테이블은 잠금 정보를 제공합니다.
  여기서 req_spid는 잠금을 요청한 세션의 ID이고, rsc_type은 잠금의 유형을 나타냅니다.
  쿼리에서는 sc_type <> 2를 사용하여 테이블 잠금이 아닌 경우를 선택하고 있습니다.

SELECT

• p.spid : 세션 ID
• p.cmd : 현재 명령 실행
• p.status : 세션 상태
• p.loginame : 로그인 이름
• p.hostname : 호스트 이름
• p.open_tran : 열려 있는 트랜잭션의 수
• datediff(s, p.last_batch, getdate()) as [wait tims(s)] : 마지막 명령을 실행한 후 경과한 시간(초)
• q.text : 실행 중인 SQL 쿼리 텍스트

ORDER BY

datediff(s, p.last_batch, getdate()) DESC :
세션이 마지막으로 명령을 실행한 후 경과한 시간을 기준으로 내림차순으로 정렬합니다.

실행결과