다음을 통해 공유

SQL Server 차단 문제 이해 및 해결을 참조하세요

  • 아티클

적용 대상: SQL Server(지원되는 모든 버전), Azure SQL Managed Instance

원래 KB 번호: 224453

목표

이 문서에서는 SQL Server의 차단에 대해 설명하고 차단 문제를 해결하고 해결하는 방법을 보여 줍니다.

이 문서에서 연결이라는 용어는 데이터베이스의 단일 로그온된 세션을 나타냅니다. 각 연결은 많은 DMV에서 SPID(세션 ID) 또는 session_id로 표시됩니다. 이러한 각 SPID는 일반적인 의미에서 별도의 프로세스 컨텍스트는 아니지만 프로세스라고도 합니다. 대신 각 SPID는 지정된 클라이언트에서 단일 연결 요청을 지원하는 데 필요한 서버 리소스 및 데이터 구조로 구성됩니다. 단일 클라이언트 애플리케이션에 연결이 하나 이상 존재할 수 있습니다. SQL Server의 관점에서 볼 때 단일 클라이언트 컴퓨터의 단일 클라이언트 애플리케이션에서의 여러 연결과 여러 클라이언트 애플리케이션 또는 여러 클라이언트 컴퓨터의 여러 연결 간에는 차이가 없습니다. 원자성입니다. 원본 클라이언트에 관계없이 한 연결이 다른 연결을 차단할 수 있습니다.

참고 항목

이 문서는 Azure SQL Managed Instance를 비롯한 SQL Server 인스턴스에 중점을 줍니다. Azure SQL Database의 차단 문제 해결과 관련된 자세한 내용은 Azure SQL Database 차단 문제 이해 및 해결을 참조 하세요.

차단이란 무엇입니까

차단은 잠금 기반 동시성을 갖는 RDBMS(관계형 데이터베이스 관리 시스템)의 불가피하며 의도된 특성입니다. 앞서 언급한 바와 같이 SQL Server에서 차단은 한 세션이 특정 리소스에 대한 잠금을 보류하고 두 번째 SPID가 동일한 리소스에 대해 충돌하는 잠금 유형을 획득하려고 할 때 발생합니다. 일반적으로 첫 번째 SPID가 리소스를 잠그는 시간 프레임은 짧습니다. 소유 세션에서 잠금을 해제하면 두 번째 연결이 자유롭게 리소스에 대한 자체 잠금을 획득하고 처리를 계속할 수 있습니다. 여기에 설명된 대로 차단은 정상적인 동작이며 시스템 성능에 눈에 띄는 영향을 주지 않고 하루 동안 여러 번 발생할 수 있습니다.

쿼리의 지속 시간 및 트랜잭션 컨텍스트는 잠금이 유지되는 기간과 다른 쿼리에 미치는 영향을 결정합니다. 쿼리가 트랜잭션 내에서 실행되지 않고 잠금 힌트가 사용되지 않는 경우 SELECT 문에 대한 잠금은 쿼리 중에가 아니라 실제로 읽는 시간에만 리소스에 보관됩니다. INSERT, UPDATE 및 DELETE 문의 경우 데이터 일관성과 필요에 따라 쿼리를 롤백할 수 있도록 쿼리 중에 잠금이 유지됩니다.

트랜잭션 내에서 실행되는 쿼리의 경우 잠금이 유지되는 기간은 쿼리 유형, 트랜잭션 격리 수준 및 쿼리에서 잠금 힌트가 사용되는지 여부에 따라 결정됩니다. 잠금, 잠금 힌트 및 트랜잭션 격리 수준에 대한 설명은 다음 문서를 참조하세요.

잠금 및 차단이 시스템 성능을 저해하는 지점까지 지속되는 경우 다음 이유 중 하나로 인해 발생합니다.

  • SPID는 리소스 집합을 해제하기 전에 오랜 시간 동안 잠금을 유지합니다. 이 유형의 차단은 시간이 지남에 따라 자체적으로 해결되지만 성능이 저하될 수 있습니다.

  • SPID는 리소스 집합에 대한 잠금을 유지하고 해제하지 않습니다. 이러한 유형의 차단은 자체적으로 해결되지 않으며 영향을 받는 리소스에 대한 액세스를 무기한 차단합니다.

첫 번째 시나리오에서는 시간이 지남에 따라 다른 SPID가 서로 다른 리소스에서 차단을 유발하여 이동 대상을 생성하므로 상황이 매우 유동적일 수 있습니다. SQL Server Management Studio를 사용하여 문제를 개별 쿼리로 좁히려는 경우 이러한 상황에서 문제를 해결하기 어렵습니다. 반면, 두 번째 상황은 일관된 상태가 생성되므로 진단하기 쉬울 수 있습니다.

애플리케이션 및 차단

차단 문제가 발생할 때 서버 쪽 튜닝 및 플랫폼 문제에 집중하는 경향이 있을 수 있습니다. 그러나 데이터베이스에만 집중해도 문제가 해결되지 않을 수 있으며, 클라이언트 애플리케이션과 여기에서 제출하는 쿼리를 검사하는 데 필요한 시간과 에너지를 빼앗길 수 있습니다. 데이터베이스 호출과 관련하여 애플리케이션에서 노출하는 가시성 수준에 관계없이, 차단 문제는 애플리케이션에서 제출한 정확한 SQL 문을 검사하고 쿼리 취소, 연결 관리, 모든 결과 행 가져오기 등과 관련된 애플리케이션의 정확한 동작을 모두 검사해야 하는 경우가 많습니다. 개발 도구에서 연결 관리, 쿼리 취소, 쿼리 시간 제한, 결과 가져오기 등을 명시적으로 제어할 수 없는 경우 차단 문제를 해결할 수 없습니다. 특히 성능이 중요한 OLTP 환경의 경우 SQL Server용 애플리케이션 개발 도구를 선택하기 전에 이 가능성을 면밀히 검토해야 합니다.

데이터베이스 및 애플리케이션의 디자인과 생성 단계에서 데이터베이스 성능에 주의하세요. 특히 각 쿼리에 대해 리소스 사용, 격리 수준 및 트랜잭션 경로 길이를 평가해야 합니다. 각 쿼리 및 트랜잭션은 가능한 간단하게 만들어야 합니다. 좋은 연결 관리 원칙을 실행해야 합니다. 그렇지 않으면 사용자가 적을 때 애플리케이션이 허용 가능한 성능을 지원하지만, 사용자 수가 증가하면 성능이 크게 저하될 수 있습니다.

적절한 애플리케이션 및 쿼리 디자인을 통해 SQL Server는 차단이 거의 없는 단일 서버에서 수천 명의 동시 사용자를 지원할 수 있습니다.

차단 문제 해결

어떤 차단 상황에 있든 관계없이 잠금 문제를 해결하는 방법은 동일합니다. 이러한 논리적 구분은 이 문서의 나머지 컴퍼지션을 결정합니다. 개념은 헤드 블로커를 찾아 쿼리가 수행하는 작업과 해당 쿼리가 차단되는 원인을 식별하는 것입니다. 문제가 있는 쿼리가 식별되면(즉, 장기간 잠금을 보유하는 항목) 다음 단계는 차단이 발생하는 이유를 분석하고 확인하는 것입니다. 그 이유를 이해한 후에는 쿼리 및 트랜잭션을 다시 디자인하여 변경할 수 있습니다.

문제 해결 단계:

  1. 주 차단 세션(헤드 블로커) 식별

  2. 차단을 일으키는 쿼리 및 트랜잭션 찾기(장기간 잠금을 유지하는 항목)

  3. 장기간 차단이 발생하는 원인 분석/이해

  4. 쿼리 및 트랜잭션을 다시 디자인하여 차단 문제 해결

이제 적절한 데이터 캡처를 통해 주 차단 세션을 정확히 찾아내는 방법에 대해 알아보겠습니다.

차단 정보 수집

데이터베이스 관리자는 차단 문제 해결의 어려움을 해결하기 위해 SQL Server의 잠금 및 차단 상태를 지속적으로 모니터링하는 SQL 스크립트를 사용할 수 있습니다. 이 데이터를 수집하기 위해 두 가지 무료 방법이 있습니다.

첫 번째는 DMO(동적 관리 개체)를 쿼리하고 시간에 따른 비교를 위해 결과를 저장하는 것입니다. 이 문서에서 참조하는 일부 개체는 DMV(동적 관리 뷰)이며 일부는 DMF(동적 관리 함수)입니다.

두 번째는 확장 이벤트(XEvents) 또는 SQL 프로파일러 추적을 사용하여 실행 중인 항목을 캡처하는 것입니다. SQL 추적 및 SQL Server Profiler는 더 이상 사용되지 않으므로 이 문제 해결 가이드에서는 XEvents에 초점을 맞춥니다.

DMV에서 정보를 수집합니다.

차단 문제를 해결하기 위해 DMV를 참조하는 것은 차단 체인 및 SQL 문의 헤드에서 SPID(세션 ID)를 식별하는 것을 목표로 합니다. 차단된 문제가 된 SPID를 찾습니다. 다른 SPID에 의해 해당 SPID가 차단되는 경우 리소스를 소유한 SPID(차단 SPID)를 조사합니다. 소유자 SPID도 차단되고 있나요? 체인을 탐색하여 헤드 블로커를 찾은 다음 체인이 잠금을 유지하는 원인을 조사할 수 있습니다.

이렇게 하려면 다음 방법 중 하나를 사용할 수 있습니다.

  • SSMS(SQL Server Management Studio) 개체 탐색기 최상위 서버 개체를 마우스 오른쪽 단추로 클릭하고 보고서를 확장하고 표준 보고서를 확장한 다음 작업 - 모든 차단 트랜잭션을 선택합니다. 이 보고서는 차단 체인의 머리에서 현재 트랜잭션을 보여 줍니다. 트랜잭션을 확장하면 보고서에 헤드 트랜잭션에 의해 차단된 트랜잭션이 표시됩니다. 이 보고서에는 차단 SQL 문과 차단된 SQL 문도 표시됩니다.

  • SSMS에서 활동 모니터를 열고 차단된 기준 열을 참조합니다. 활동 모니터에 대한 자세한 내용은 여기를 참조하세요.

DMV를 사용하여 더 자세한 쿼리 기반 메서드도 사용할 수 있습니다.

  • sp_who 및 sp_who2 명령은 모든 현재 세션을 표시하는 이전 명령입니다. DMV sys.dm_exec_sessions는 결과 집합에서 더 많은 데이터를 반환하며, 결과 집합에서 쿼리 및 필터링을 보다 쉽게 수행할 수 있습니다. 다른 쿼리의 핵심에서 sys.dm_exec_sessions를 찾을 수 있습니다.

  • 특정 세션이 이미 식별된 경우 DBCC INPUTBUFFER(<session_id>)를 사용하여 세션에서 제출한 마지막 문을 찾을 수 있습니다. 쿼리 및 필터링하기 쉬운 결과 집합에서 sys.dm_exec_input_buffer DMF(동적 관리 함수)를 사용하여 유사한 결과를 반환하여 이때 session_id 및 request_id를 제공합니다. 예를 들어 session_id 66 및 request_id 0에서 제출한 가장 최근 쿼리를 반환하려면 다음을 수행합니다.

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • sys.dm_exec_requests 열을 참조하고 참조합니다blocking_session_id. blocking_session_id = 0이면 세션이 차단되지 않습니다. sys.dm_exec_requests에서 현재 실행 중인 요청만 나열하는 동안 sys.dm_exec_sessions에는 모든 연결(활성 여부에 상관없이)이 나열됩니다. 다음 쿼리에서는 sys.dm_exec_requests 및 sys.dm_exec_sessions 사이의 이 일반적인 조인을 기반으로 빌드합니다. 쿼리 sys.dm_exec_requests는 SQL Server를 사용하여 적극적으로 실행되어야 합니다.

  • 이 샘플 쿼리를 실행하여 sys.dm_exec_sql_text 또는 sys.dm_exec_input_buffer DMV를 사용해 현재 실행 중인 쿼리와 현재 SQL 일괄 처리 텍스트 또는 입력 버퍼 텍스트를 찾습니다. 열 sys.dm_exec_sql_text 에서 반환된 text 데이터가 NULL이면 쿼리가 현재 실행되고 있지 않습니다. 이 경우 열 sys.dm_exec_input_buffer 에는 event_info SQL 엔진에 전달된 마지막 명령 문자열이 포함됩니다. 이 쿼리를 사용하여 session_id당 차단된 session_ids 목록을 포함하여 다른 세션을 차단하는 세션을 식별할 수도 있습니다.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Microsoft 지원에서 제공하는 보다 정교한 샘플 쿼리를 실행해 차단 체인과 관련된 세션의 쿼리 텍스트를 포함하여 여러 세션 차단 체인의 헤드를 식별합니다.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;
  • 장기 실행 또는 커밋되지 않은 트랜잭션을 catch하려면 sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections 및 sys.dm_exec_sql_text를 포함하여 현재 열려 있는 트랜잭션을 보기 위해 다른 DMV 집합을 사용합니다. 트랜잭션 추적과 관련된 여러 DMV가 있습니다. 여기 트랜잭션의 DMV에서 더 많은 항목을 확인하세요.
SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • SQL Server의 스레드/작업 계층에 있는 참조 sys.dm_os_waiting_tasks . 그러면 요청이 현재 발생하는 SQL wait_type 대한 정보가 반환됩니다. sys.dm_exec_requests과 마찬가지로 sys.dm_os_waiting_tasks에 의해 활성 요청만 반환됩니다.

참고 항목

시간 경과에 따른 집계된 대기 통계를 포함한 대기 유형에 대한 자세한 내용은 DMV sys.dm_db_wait_stats를 참조하세요.

  • 쿼리에 의해 배치된 잠금에 대한 자세한 내용을 보려면 sys.dm_tran_locks DMV를 사용합니다. 이 DMV는 프로덕션 SQL Server 인스턴스에서 대량의 데이터를 반환할 수 있으며 현재 보유하고 있는 잠금을 진단하는 데 유용합니다.

sys.dm_os_waiting_tasks의 INNER JOIN으로 인해 다음 쿼리는 sys.dm_tran_locks의 출력을 현재 차단된 요청, 해당 대기 상태 및 잠금으로만 제한합니다.

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

DMV를 통해 시간에 따른 쿼리 결과를 저장하면 지정된 시간 간격 동안 차단을 검토하여 지속형 차단 또는 추세를 식별할 수 있는 데이터 요소를 제공합니다. 이러한 문제를 해결하기 위한 CSS용 이동 도구는 PSSDiag 데이터 수집기를 사용하는 것입니다. 이 도구는 "SQL Server 성능 통계"를 사용하여 시간이 지남에 따라 위에서 참조한 DMV에서 결과 집합을 수집합니다. 이 도구는 지속적으로 발전하고 있으므로 GitHub에서 최신 공개 버전의 DiagManager를 검토합니다.

확장 이벤트에서 정보 수집

위의 정보 외에도 SQL Server에서 차단 문제를 철저히 조사하기 위해 서버에서 작업의 추적을 캡처해야 하는 경우가 많습니다. 예를 들어, 세션이 트랜잭션 내에서 여러 문을 실행하는 경우 제출된 마지막 문만 표시됩니다. 그러나 이전 명령문 중 하나가 잠금이 여전히 유지되는 이유일 수 있습니다. 추적을 사용하면 현재 트랜잭션 내에서 세션에 의해 실행된 모든 명령을 볼 수 있습니다.

SQL Server에서 추적을 캡처하는 방법에는 두 가지가 있습니다. 확장 이벤트(XEvents) 및 프로파일러 추적 그러나 SQL Server 프로파일러를 사용하는 SQL 추적은 더 이상 사용되지 않습니다. XEvents는 관찰된 시스템에 더 많은 다양성과 적은 영향을 줄 수 있는 최신의 우수한 추적 플랫폼이며, 해당 인터페이스는 SSMS에 통합됩니다.

SSMS에서 시작할 준비가 된 미리 만들어진 확장 이벤트 세션이 있으며, XEvent Profiler 메뉴의 개체 탐색기 나열되어 있습니다. 자세한 내용은 XEvent Profiler를 참조 하세요. SSMS에서 사용자 지정 확장 이벤트 세션을 직접 만들 수도 있습니다. 확장 이벤트 새 세션 마법사를 참조하세요. 차단 문제를 해결하기 위해 일반적으로 다음을 캡처합니다.

  • 범주 오류:
    • 주의
    • Blocked_process_report**
    • Error_reported(채널 관리자)
    • Exchange_spill
    • Execution_warning

**차단된 프로세스 보고서가 생성되는 임계값 및 빈도를 구성하려면 sp_configure 명령을 사용하여 차단된 프로세스 임계값 옵션을 구성합니다. 이 옵션은 초 단위로 설정할 수 있습니다. 기본적으로 차단된 프로세스 보고서는 생성되지 않습니다.

  • 범주 경고:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • 범주 실행:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • 범주 잠금

    • Lock_deadlock

  • 범주 세션

    • Existing_connection
    • 로그인
    • 로그아웃

일반적인 차단 시나리오 식별 및 해결

위의 정보를 검사하여 대부분의 차단 문제의 원인을 확인할 수 있습니다. 이 문서의 나머지 부분에서는 이 정보를 사용하여 몇 가지 일반적인 차단 시나리오를 식별하고 해결하는 방법에 대해 설명합니다. 이 설명에서는 차단 스크립트(앞에서 참조)를 사용하여 차단 SPID에 대한 정보를 캡처하고 XEvent 세션을 사용하여 애플리케이션 작업을 캡처했다고 가정합니다.

차단 데이터 분석

  • sys.dm_exec_requests 및 sys.dm_exec_sessions의 출력을 검사하여 blocking_these 및 session_id를 사용하여 차단 체인의 헤드를 판단합니다. 이렇게 하면 차단된 요청과 차단 중인 요청이 가장 명확하게 식별됩니다. 차단된 세션과 차단 중인 세션을 자세히 살펴봅니다. 차단 체인에 공통 요소 또는 루트가 있나요? 공통 테이블을 공유할 가능성이 크며, 차단 체인에 포함된 하나 이상의 세션이 쓰기 작업을 수행합니다.

  • 차단 체인의 헤드에 있는 SPID에 대한 정보는 sys.dm_exec_requests 및 sys.dm_exec_sessions의 출력을 검사합니다. 다음 열을 찾습니다.

    • sys.dm_exec_requests.status

      이 열에서는 특정 요청의 상태를 표시합니다. 일반적으로 중지 중 상태는 SPID가 실행을 완료했으며 애플리케이션이 다른 쿼리나 배치를 제출하기를 기다리고 있음을 나타냅니다. 실행 가능 또는 실행 중 상태는 SPID가 현재 쿼리를 처리하고 있음을 나타냅니다. 다음 테이블에서는 다양한 상태 값을 간략하게 설명합니다.

      상태 의미
      배경 SPID는 교착 상태 검색, 로그 기록기 또는 체크포인트와 같은 백그라운드 작업을 실행합니다.
      중지 중 SPID가 현재 실행되고 있지 않습니다. 이는 일반적으로 SPID가 애플리케이션에서 명령을 기다리고 있음을 나타냅니다.
      실행 중 SPID는 현재 스케줄러에서 실행 중입니다.
      실행 가능 SPID는 스케줄러의 실행 가능한 큐에 있으며 스케줄러 시간을 가져오기를 기다리고 있습니다.
      일시 중단 SPID는 잠금 또는 래치와 같은 리소스를 기다리고 있습니다.

    • sys.dm_exec_sessions.open_transaction_count

      이 열은 이 세션의 열린 트랜잭션 수를 알려줍니다. 이 값이 0보다 큰 경우 SPID는 열린 트랜잭션 내에 있으며 트랜잭션 내의 모든 명령문에서 획득한 잠금을 보유할 수 있습니다. 열려 있는 트랜잭션은 현재 활성 문 또는 과거에 실행되었으며 더 이상 활성화되지 않은 문 요청에 의해 생성되었을 수 있습니다.

    • sys.dm_exec_requests.open_transaction_count

      마찬가지로 이 열은 이 요청의 열린 트랜잭션 수를 알려줍니다. 이 값이 0보다 큰 경우 SPID는 열려 있는 트랜잭션 내에 있으며 트랜잭션 내의 활성 문에서 획득한 잠금을 보유할 수 있습니다. 활성 요청이 없는 경우와 달리 sys.dm_exec_sessions.open_transaction_count이 열에는 0이 표시됩니다.

    • sys.dm_exec_requests.wait_type, wait_time 및 last_wait_type

      sys.dm_exec_requests.wait_type이 NULL이면 요청은 현재 아무 것도 기다리지 않고 last_wait_type 값은 요청이 발생한 마지막 wait_type을 나타냅니다. sys.dm_os_wait_stats에 대한 자세한 내용 및 가장 일반적인 대기 유형에 대한 설명은 sys.dm_os_wait_stats를 참조하세요. wait_time 값을 사용하여 요청을 진행 중인지 여부를 확인할 수 있습니다. sys.dm_exec_requests 테이블에 대한 쿼리가 wait_time 열에 이전 쿼리인 sys.dm_exec_requests의 wait_time 값보다 작은 값을 반환하는 경우, 이전 잠금을 획득하여 해제한 다음, 현재 새 잠금에서 대기하고 있음을 나타냅니다(0이 아닌 wait_time인 것으로 가정). 이는 요청이 대기 중인 리소스를 표시하는sys.dm_exec_requests 출력 간의 wait_resource를 비교하여 확인할 수 있습니다.

    • sys.dm_exec_requests.wait_resource

      이 열은 차단된 요청이 대기 중인 리소스를 나타냅니다. 다음 테이블은 일반 wait_resource 형식 및 해당 의미를 나열합니다.

      리소스 형식 예시 설명
      테이블 DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 이 경우 데이터베이스 ID 5는 pubs 샘플 데이터베이스이고 object_id 261575970 타이틀 테이블이고 1은 클러스터형 인덱스입니다.
      페이지 DatabaseID:FileID:PageID PAGE: 5:1:104 이 경우 데이터베이스 ID 5는 pubs, 파일 ID 1은 기본 데이터 파일, 페이지 104는 제목 테이블에 속하는 페이지입니다. 페이지가 속한 object_id 식별하려면 동적 관리 함수 sys.dm_db_page_info를 사용하여 wait_resource에서 DatabaseID, FileId, PageId를 전달합니다.
      키 DatabaseID:Hobt_id(인덱스 키의 해시 값) 키: 5:72057594044284928(3300a4f361aa) 이 경우 데이터베이스 ID 5는 Pubs, Hobt_ID 72057594044284928은 object_id 261575970(제목 표)의 index_id 2에 해당합니다. 카탈로그 뷰를 sys.partitions 사용하여 특정 index_id object_id및 에 hobt_id 연결합니다. 인덱스 키 해시를 특정 키 값으로 해시 해제하는 방법은 없습니다.
      Row DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 이 경우 데이터베이스 ID 5는 pubs, 파일 ID 1은 기본 데이터 파일, 페이지 104는 제목 테이블에 속하는 페이지, 슬롯 3은 페이지에서 행의 위치를 나타냅니다.
      Compile DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 이 경우 데이터베이스 ID 5는 pubs, 파일 ID 1은 기본 데이터 파일, 페이지 104는 제목 테이블에 속하는 페이지, 슬롯 3은 페이지에서 행의 위치를 나타냅니다.

    • sys.dm_tran_active_transactionssys.dm_tran_active_transactions DMV에는 커밋 또는 롤백을 기다리는 트랜잭션의 전체 상황을 파악하기 위해 다른 DMV에 조인할 수 있는 열린 트랜잭션에 대한 데이터가 포함되어 있습니다. 다음 쿼리를 사용하여 sys.dm_tran_session_transactions를 포함하는 다른 DMV에 조인된 열린 트랜잭션에 대한 정보를 반환합니다. 트랜잭션의 현재 상태, transaction_begin_time 및 기타 상황적 데이터를 고려하여 차단의 출처인지 여부를 평가합니다.

      SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • 기타 열

      sys.dm_exec_sessions 및 sys.dm_exec_request에 나머지 열은 문제의 근원에 대한 인사이트도 제공할 수 있습니다. 해당 유용성은 문제의 상황에 따라 달라집니다. 예를 들어 특정 클라이언트(), 특정 네트워크 라이브러리(hostnameclient_interface_name)에서만 문제가 발생하는지, SPID에서 제출한 마지막 일괄 처리가 sys.dm_exec_sessionslast_request_start_time 언제인지, 요청이 실행 start_time sys.dm_exec_requests된 기간 등을 확인할 수 있습니다.

일반적인 차단 시나리오

아래 테이블에서는 일반적인 증상을 가능한 원인에 매핑합니다.

wait_type, open_transaction_count 및 status 열은 sys.dm_exec_request에서 반환된 정보를 참조하며, 다른 열은 sys.dm_exec_sessions에서 반환될 수 있습니다. "Resolves?" 열은 차단이 자체적으로 해결되는지 또는 세션을 KILL 명령을 통해 종료해야 하는지 여부를 나타냅니다. 자세한 내용은 KILL(Transact SQL)을 참조하세요.

시나리오 Wait_type Open_Tran 상태 해결 방법 기타 증상
1 NOT NULL > = 0 실행 가능 예(쿼리가 완료되는 경우). sys.dm_exec_sessions에서 reads, cpu_time 및/또는 memory_usage 열은 시간이 지남에 따라 증가합니다. 작업을 완료하면 쿼리의 기간이 증가합니다.
2 NULL >0 중지 중 아니요. 하지만 SPID는 종료할 수 있습니다. 이 SPID에 대한 확장 이벤트 세션에서 쿼리 시간 제한 또는 취소가 발생했음을 나타내는 주의 신호가 표시될 수 있습니다.
3 NULL > = 0 실행 가능 아니요. 클라이언트가 모든 행을 가져오거나 연결을 닫을 때까지 해결되지 않습니다. SPID는 종료할 수 있지만 최대 30초까지 걸릴 수 있습니다. open_transaction_count = 0이고 트랜잭션 격리 수준이 기본값인 동안 SPID가 잠금을 보유하는 경우(READ COMMITTED) 원인일 수 있습니다.
4 상황에 따라 다름 > = 0 실행 가능 아니요. 클라이언트가 쿼리를 취소하거나 연결을 닫을 때까지 해결되지 않습니다. SPID는 종료할 수 있지만 최대 30초까지 걸릴 수 있습니다. 차단 체인의 헤드에 있는 SPID에 대한 sys.dm_exec_sessions의 hostname 열은 차단 중인 SPID 중 하나와 동일합니다.
5 NULL >0 rollback 예. 이 SPID에 대한 확장 이벤트 세션에서 주의 신호가 표시될 수 있습니다. 이는 쿼리 시간 제한 또는 취소가 발생했거나 단순히 롤백 문이 실행되었음을 나타냅니다.
6 NULL >0 중지 중 최종. Windows NT에서 세션이 더 이상 활성 상태가 아닐 경우 연결이 끊어집니다. sys.dm_exec_sessions의 last_request_start_time 값이 현재 시간보다 훨씬 이전입니다.

자세한 차단 시나리오

시나리오 1: 실행 시간이 긴 일반적으로 실행되는 쿼리로 인한 차단

이 시나리오에서는 적극적으로 실행 중인 쿼리가 잠금을 획득했으며 잠금이 해제되지 않습니다(트랜잭션 격리 수준의 영향을 받습니다). 따라서 다른 세션은 잠금이 해제될 때까지 대기합니다.

해결 방법:

이 차단 문제의 해결 방법은 쿼리를 최적화하는 방법을 찾는 것입니다. 이 차단 문제 클래스는 성능 문제일 수 있으며 이를 처리해야 합니다. 느리게 실행되는 특정 쿼리 문제를 해결하는 방법에 대한 자세한 내용은 SQL Server에서 느리게 실행되는 쿼리 문제 해결을 참조하세요. 자세한 내용은 성능 모니터링 및 조정을 참조하세요.

쿼리 저장소 SSMS에 기본 제공된 보고서(SQL Server 2016에서 도입됨)는 가장 비용이 많이 드는 쿼리 및 최적이 아니면 실행 계획을 식별하기 위한 매우 권장되고 유용한 도구이기도 합니다.

다른 사용자를 차단하는 장기 실행 쿼리가 있고 최적화할 수 없는 경우 OLTP 환경에서 전용 보고 시스템으로 이동하는 것이 좋습니다. Always On 가용성 그룹을 사용하여 데이터베이스의 읽기 전용 복제본을 동기화할 수도 있습니다.

참고 항목

쿼리 실행 중 차단은 행 또는 페이지 잠금이 테이블 잠금으로 에스컬레이션되는 시나리오인 쿼리 에스컬레이션으로 인해 발생할 수 있습니다. Microsoft SQL Server는 잠금 에스컬레이션을 수행할 시기를 동적으로 결정합니다. 잠금 에스컬레이션을 방지하는 가장 간단하고 안전한 방법은 트랜잭션을 짧게 유지하고 잠금 에스컬레이션 임계값을 초과하지 않도록 비용이 많이 드는 쿼리의 잠금 공간을 줄이는 것입니다. 과도한 잠금 에스컬레이션을 감지하고 방지하는 방법에 대한 자세한 내용은 잠금 에스컬레이션으로 인한 차단 문제 해결을 참조하세요.

시나리오 2: 커밋되지 않은 트랜잭션이 있는 절전 모드 SPID로 인한 차단

이 유형의 차단은 대개 0보다 큰 트랜잭션 중첩 수준(@@TRANCOUNTopen_transaction_count부터)이 있는 명령을 대기 중이거나 대기 중인 SPID로 sys.dm_exec_requests식별할 수 있습니다. 이 상황은 애플리케이션에서 필요한 수의 ROLLBACK 및/또는 COMMIT 문을 실행하지 않고 쿼리 시간 제한을 경험하거나 취소를 실행하는 경우에 발생할 수 있습니다. SPID는 쿼리 시간 제한 또는 취소를 받으면 현재 쿼리 및 일괄 처리를 종료하지만 트랜잭션을 자동으로 롤백하거나 커밋하지는 않습니다. SQL Server는 단일 쿼리가 취소되어 전체 트랜잭션을 롤백해야 한다고 가정할 수 없으므로 애플리케이션이 이를 담당합니다. 쿼리 시간 제한 또는 취소는 확장 이벤트 세션에서 SPID에 대한 주의 신호 이벤트로 표시됩니다.

커밋되지 않은 명시적 트랜잭션을 시연하려면 다음 쿼리를 실행합니다.

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

그런 다음 동일한 창에서 이 쿼리를 실행합니다.

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

두 번째 쿼리의 출력은 트랜잭션 수가 1임을 나타냅니다. 트랜잭션에서 획득한 모든 잠금은 트랜잭션이 커밋되거나 롤백될 때까지 계속 유지됩니다. 애플리케이션이 트랜잭션을 명시적으로 열고 커밋하는 경우 통신 또는 기타 오류로 인해 세션과 해당 트랜잭션이 열린 상태로 남을 수 있습니다.

sys.dm_tran_active_transactions에 따라 이 문서의 앞부분에 나오는 스크립트를 사용하여 인스턴스 간에 현재 커밋되지 않은 트랜잭션을 식별합니다.

해결 방법:

  • 이 차단 문제 클래스도 성능 문제일 수 있으며 이를 처리해야 합니다. 쿼리 실행 시간을 줄일 수 있는 경우 쿼리 시간 제한 또는 취소가 발생하지 않을 수 있습니다. 애플리케이션이 발생하는 경우 시간 제한을 처리하거나 시나리오를 취소할 수 있지만 쿼리 성능을 검사하면 도움이 될 수도 있습니다.

  • 애플리케이션은 트랜잭션 중첩 수준을 적절하게 관리해야 합니다. 그렇지 않으면 이러한 방식으로 쿼리를 취소한 후 차단 문제가 발생할 수 있습니다. 다음을 고려하십시오.

    • 클라이언트 응용 프로그램의 오류 처리기에서 클라이언트 응용 프로그램에서 트랜잭션이 열려 있다고 생각하지 않더라도 오류 발생 후 IF @@TRANCOUNT > 0 ROLLBACK TRAN을 실행합니다. 일괄 처리 중에 호출된 저장 프로시저가 클라이언트 애플리케이션의 지식 없이 트랜잭션을 시작할 수 있으므로 열려 있는 트랜잭션을 확인해야 합니다. 쿼리 취소와 같은 특정 조건은 현재 문을 지나 프로시저가 실행되지 않도록 하기 때문에 절차에 IF @@ERROR <> 0를 확인하고 트랜잭션을 중단하는 논리가 있더라도 이러한 경우에는 이 롤백 코드가 실행되지 않습니다.

    • 연결을 열고 웹 기반 애플리케이션과 같이 연결을 풀에 다시 릴리스하기 전에 몇 가지 쿼리를 실행하는 애플리케이션에서 연결 풀링을 사용하는 경우 클라이언트 애플리케이션이 오류를 적절하게 처리하도록 수정될 때까지 연결 풀링을 일시적으로 사용하지 않도록 설정하면 문제가 완화될 수 있습니다. 연결 풀링을 사용하지 않도록 설정하면 연결을 해제하면 SQL Server 연결이 물리적으로 끊어지게 되므로 서버가 열려 있는 트랜잭션을 롤백합니다.

    • 연결 또는 트랜잭션을 시작하고 오류가 발생한 후 정리되지 않는 저장 프로시저에 사용합니다 SET XACT_ABORT ON . 런타임 오류가 발생할 경우 이 설정은 열려 있는 트랜잭션을 중단하고 클라이언트로 컨트롤을 반환합니다. 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 검토 하세요.

참고 항목

연결 풀에서 다시 사용할 때까지 연결이 다시 설정되지 않으므로 사용자가 트랜잭션을 연 다음 연결 풀에 대한 연결을 해제할 수 있지만 몇 초 동안 다시 사용되지 않을 수 있으며 이 기간 동안 트랜잭션이 열린 상태로 유지됩니다. 연결을 다시 사용하지 않으면 연결 시간이 초과되고 연결 풀에서 제거되면 트랜잭션이 중단됩니다. 따라서 클라이언트 애플리케이션이 오류 처리기에서 트랜잭션을 중단하거나 이 잠재적인 지연을 방지하는 데 사용하는 SET XACT_ABORT ON 것이 가장 좋습니다.

주의

다음으로 SET XACT_ABORT ON, 오류를 발생시키는 문을 따르는 T-SQL 문은 실행되지 않습니다. 이는 기존 코드의 의도된 흐름에 영향을 줄 수 있습니다.

시나리오 3: 해당 클라이언트 애플리케이션이 모든 결과 행을 완료로 가져오지 않은 SPID로 인한 차단

서버에 쿼리를 보낸 후 모든 애플리케이션에서 즉시 모든 결과 행을 가져와 완료해야 합니다. 애플리케이션에서 모든 결과 행을 가져오지 않으면 테이블을 잠가 다른 사용자를 차단할 수 있습니다. SQL 문을 서버에 투명하게 제출하는 애플리케이션을 사용하는 경우 애플리케이션은 모든 결과 행을 가져와야 합니다. 구성되지 않은 경우(그리고 구성할 수 없는 경우) 차단 문제를 해결할 수 없습니다. 이 문제를 방지하려면 기본 OLTP 데이터베이스와 별도로 제대로 동작하지 않는 애플리케이션을 보고 또는 의사 결정 지원 데이터베이스로 제한할 수 있습니다.

해결 방법:

완료할 결과의 모든 행을 가져오려면 애플리케이션을 다시 작성해야 합니다. 이는 서버측 페이징을 수행하기 위해 쿼리의 ORDER BY 절의 OFFSET 및 FETCH 사용을 배제하지 않습니다.

시나리오 4: 분산 클라이언트/서버 교착 상태로 인한 차단

기존의 교착 상태와 달리 RDBMS 잠금 관리자를 사용하여 분산 교착 상태를 검색할 수 없습니다. 교착 상태에 관련된 리소스 중 하나만 SQL Server 잠금이기 때문입니다. 교착 상태의 다른 쪽은 SQL Server가 제어할 수 없는 클라이언트 애플리케이션 수준에 있습니다. 다음 두 섹션에서는 이런 일이 발생할 수 있는 방법과 애플리케이션에서 방지할 수 있는 가능한 방법의 예를 보여 줍니다.

예제 A: 단일 클라이언트 스레드를 사용하는 클라이언트/서버 분산 교착 상태

클라이언트에 열려 있는 연결이 여러 개 있고 단일 실행 스레드가 있는 경우 다음과 같은 분산 교착 상태가 발생할 수 있습니다. 여기서 사용되는 용어 dbproc 는 클라이언트 연결 구조를 나타냅니다.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

위에 표시된 경우 단일 클라이언트 애플리케이션 스레드에는 두 개의 열린 연결이 있습니다. dbproc1에서 SQL 작업을 비동기적으로 제출합니다. 즉, 계속하기 전에 호출이 반환되기를 기다리지 않습니다. 그런 다음, 애플리케이션은 dbproc2에서 다른 SQL 작업을 제출하고 반환된 데이터 처리를 시작하기 위해 결과를 기다립니다. 데이터가 다시 돌아오기 시작하면(dbproc가 먼저 응답하는 경우- dbproc1이라고 가정) 해당 dbproc에서 반환된 모든 데이터를 완료하도록 처리합니다. SPID2가 보유한 잠금에서 SPID1이 차단될 때까지 dbproc1의 결과를 가져옵니다(두 쿼리가 서버에서 비동기적으로 실행되기 때문). 이 시점에서 dbproc1은 더 많은 데이터를 무기한 대기합니다. SPID2는 잠금에서 차단되지 않지만 클라이언트 dbproc2로 데이터를 보내려고 시도합니다. 그러나 애플리케이션에 대한 단일 실행 스레드가 dbproc1에서 사용 중이므로 dbproc2는 애플리케이션 계층의 dbproc1에서 효과적으로 차단됩니다. 이로 인해 관련된 리소스 중 하나만 SQL Server 리소스이므로 SQL Server에서 검색하거나 확인할 수 없는 교착 상태가 발생합니다.

예제 B: 연결당 스레드가 있는 클라이언트/서버 분산 교착 상태

클라이언트의 각 연결에 대해 별도의 스레드가 있는 경우에도 다음과 같이 분산된 교착 상태의 변형이 계속 발생할 수 있습니다.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

이 사례는 예제 A와 유사합니다. 단, dbproc2 및 SPID2는 한 번에 행 처리를 수행하고 버퍼를 통해 각 행을 동일한 테이블의 dbproc1 UPDATEINSERT또는 DELETE 문에 전달하려는 의도로 문을 실행 SELECT 합니다. 결국 SPID1(또는 수행)은 SPID2(수행INSERTUPDATEDELETESELECT)가 보유한 잠금에서 차단됩니다. SPID2는 결과 행을 클라이언트 dbproc2에 씁니다. 그런 다음 Dbproc2는 버퍼의 행을 dbproc1에 전달하려고 하지만 dbproc1이 사용 중임을 발견합니다(SPID2에서 차단된 현재 INSERT를 완료하기 위해 SPID1에서 대기하는 것이 차단됨). 이 시점에서 dbproc2는 SPID2에 의해 데이터베이스 수준에서 SPID(SPID1)가 차단되는 dbproc1에 의해 애플리케이션 계층에서 차단됩니다. 그러면 관련된 리소스 중 하나만 SQL Server 리소스이므로 SQL Server에서 검색하거나 해결할 수 없는 교착 상태가 발생합니다.

예제 A와 B는 모두 애플리케이션 개발자가 알아야 하는 기본적인 문제입니다. 이러한 경우를 적절하게 처리하려면 애플리케이션을 코딩해야 합니다.

해결 방법:

쿼리 시간 제한이 제공되면 분산 교착 상태가 발생하면 시간 초과가 발생할 때 끊어집니다. 쿼리 시간 제한 사용에 대한 자세한 내용은 연결 공급자 설명서를 참조하세요.

시나리오 5: 롤백 상태의 세션으로 인한 차단

사용자 정의 트랜잭션 외부에서 종료되거나 취소된 데이터 수정 쿼리가 롤백됩니다. 이는 클라이언트 네트워크 세션 연결 끊김의 부작용으로 발생하거나 요청이 교착 상태의 희생자로 선택된 경우에 발생할 수 있습니다. 이는 ROLLBACKcommand을 나타낼 수 있는 출력sys.dm_exec_requests을 관찰하여 식별할 수 있으며 열에 percent_complete 진행률이 표시될 수 있습니다.

사용자 정의 트랜잭션 외부에서 종료되거나 취소된 데이터 수정 쿼리가 롤백됩니다. 이는 클라이언트 컴퓨터가 다시 시작되고 네트워크 세션 연결이 끊어짐에 따른 부작용으로도 발생할 수 있습니다. 마찬가지로 교착 상태의 희생자로 선택한 쿼리는 롤백됩니다. 데이터 수정 쿼리는 처음에 적용된 변경 내용보다 더 빠르게 롤백할 수 없는 경우가 많습니다. 예를 들어 , INSERT또는 UPDATE 문이 한 시간 동안 실행된 경우 DELETE롤백하는 데 1시간 이상이 걸릴 수 있습니다. 변경 내용을 롤백해야 하거나 데이터베이스의 트랜잭션 및 물리적 무결성이 손상되기 때문에 이 동작이 예상됩니다. 이 경우 SQL Server는 SPID를 골든 또는 롤백 상태로 표시합니다(즉, 교착 상태의 희생자로 사망하거나 선택할 수 없습니다). 이는 ROLLBACK 명령을 나타낼 수 있는 출력 sp_who을 관찰하여 식별할 수 있는 경우가 많습니다. 열 sys.dm_exec_sessions 은 status ROLLBACK 상태를 나타냅니다.

참고 항목

가속 데이터베이스 복구 기능을 사용하는 경우 긴 롤백은 드물다. 이 기능은 SQL Server 2019에서 도입되었습니다.

해결 방법:

세션이 변경 내용 롤백을 완료할 때까지 기다려야 합니다.

이 작업 중간에 인스턴스가 종료되면 데이터베이스는 다시 시작할 때 복구 모드에 있으며 열려 있는 모든 트랜잭션이 처리될 때까지 액세스할 수 없습니다. 시작 복구는 기본적으로 트랜잭션당 런타임 복구와 동일한 시간이 소요되며 이 기간 동안 데이터베이스에 액세스할 수 없습니다. 따라서 롤백 상태에서 SPID를 수정하기 위해 서버를 강제로 중단하는 것은 종종 비생산적입니다. 가속 데이터베이스 복구를 사용하도록 설정된 SQL Server 2019에서는 이 문제가 발생하지 않습니다.

이러한 상황을 방지하려면 OLTP 시스템에서 바쁜 시간 동안 대규모 일괄 처리 쓰기 작업이나 인덱스 생성 또는 유지 관리 작업을 수행하지 않습니다. 가능하면 활동이 적은 기간 동안 이러한 작업을 수행합니다.

시나리오 6: 분리된 트랜잭션으로 인한 차단

이는 일반적인 문제 시나리오이며 시나리오 2와 부분적으로 겹칩니다. 클라이언트 애플리케이션이 중지되거나 클라이언트 워크스테이션이 다시 시작되거나 일괄 처리 중단 오류가 발생하면 이러한 모든 트랜잭션이 열려 있을 수 있습니다. 이 상황은 애플리케이션이 애플리케이션 또는 FINALLY 블록에서 CATCH 트랜잭션을 롤백하지 않거나 그렇지 않으면 이 상황을 처리하지 않는 경우에 발생할 수 있습니다.

이 시나리오에서는 SQL 일괄 처리 실행이 취소되었지만 애플리케이션은 SQL 트랜잭션을 열어 둡니다. SQL Server 인스턴스의 관점에서 볼 때 클라이언트는 여전히 존재하는 것처럼 보이며 획득한 모든 잠금은 유지됩니다.

분리된 트랜잭션을 보여 주려면 존재하지 않는 테이블에 데이터를 삽입하여 일괄 처리 중단 오류를 시뮬레이션하는 다음 쿼리를 실행합니다.

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

그런 다음 동일한 창에서 이 쿼리를 실행합니다.

SELECT @@TRANCOUNT;

두 번째 쿼리의 출력은 트랜잭션 수가 1임을 나타냅니다. 트랜잭션에서 획득한 모든 잠금은 트랜잭션이 커밋되거나 롤백될 때까지 계속 유지됩니다. 일괄 처리가 쿼리에 의해 이미 중단되었으므로 이 쿼리를 실행하는 애플리케이션은 여전히 열려 있는 트랜잭션을 정리하지 않고 동일한 세션에서 다른 쿼리를 계속 실행할 수 있습니다. 세션이 종료되거나 SQL Server 인스턴스가 다시 시작될 때까지 잠금이 유지됩니다.

해결 방법:

  • 이 조건을 방지하는 가장 좋은 방법은 특히 예기치 않은 종료의 경우 애플리케이션 오류/예외 처리를 개선하는 것입니다. 애플리케이션 코드에서 블록을 사용하고 Try-Catch-Finally 예외 발생 시 트랜잭션을 롤백해야 합니다.
  • 세션 또는 트랜잭션을 시작하고 오류 발생 후 정리하지 않는 저장 프로시저에 사용하는 SET XACT_ABORT ON 것이 좋습니다. 일괄 처리를 중단하는 런타임 오류가 발생하는 경우 이 설정은 열려 있는 트랜잭션을 자동으로 롤백하고 클라이언트에 컨트롤을 반환합니다. 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 검토 하세요.
  • 리소스를 적절하게 정리하지 않고 연결이 끊어진 클라이언트 애플리케이션의 분리된 연결을 해결하려면 명령을 사용하여 SPID를 KILL 종료할 수 있습니다. 참조는 KILL(Transact-SQL)을 참조 하세요.

KILL 명령은 SPID 값을 입력으로 사용합니다. 예를 들어 SPID 9를 종료하려면 다음 명령을 실행합니다.

KILL 99

참고 항목

KILL 명령 확인 사이의 간격으로 인해 명령이 완료되기까지 최대 30초가 KILL 걸릴 수 있습니다.

참고 항목