[MSSQL]Transaction Kill

Transact-SQL KILL 명령은 SQL Server 프로세스를 갑자기 끝낼 때 사용됩니다. 각 프로세스를 시스템 프로세스 ID(spid)

Transact-SQL KILL 명령은 SQL Server 프로세스를 갑자기 끝낼 때 사용됩니다. 각 프로세스를 시스템 프로세스 ID(spid)라고도 합니다. SQL Enterprise Manager(엔터프라이즈 관리자)의 Current Activity 아래에 있는 Kill Process 단추는 단지 Transact-SQL KILL 명령을 서버로 보내는것으로(낼뿐므로) 이 경우 서버쪽 KILL 메커니즘과() 동일하게 작동합니다.

spid는 KILL 명령에 즉시 응답(하거나), 지연 후 응답(하거나), 또는 전혀 응답하지 않는 경우가 있습니다.(않습니다). 어떤 조건에서는 KILL 명령에대해 지연 후 응답하거나 또는 전혀 응답하지 않는 것이 정상일 수 있습니다. 본 문서에서는 이러한 KILL 명령의 작동 방법과 KILL명령수행이 지연되는 및 ()수행되지()않는 조건( 그러한 조건)을 식별하는 방법에 대해(을) 설명합니다.

 

각 데이터베이스 연결은 spid 또는 시스템 프로세스 ID를 가지는 행을 (라고도 하며) sysprocesses에 삽입하므로써() 형성됩니다.

각 데이터베이스 연결은 spid 또는 시스템 프로세스 ID를 가지는 행을 (라고도 하며) sysprocesses에 삽입하므로써() 형성됩니다.(합니다). 이러한(각) 연결을 SQL Server 용어로 "프로세스"라고도 하지만 이는 일반적인 관점에서 말하는 개별적인 프로세스 는것은() 아닙니다(않습니다). SQL Server 6.0과 6.5에서 각 프로세스는 개별적인 운영 체제 스레드(Thread)와 거의 유사하며 그러한 개별 스레드가 각 프로세스의 작업을 담당합니다. 또한, 각 데이터베이스 연결은 프로세스 상태, 트랜잭션 상태, 보유한 잠금(Lock) 등을 추적하는 서버 데이터 구조들로 구성됩니다. 이러한 구조 중 하나가 프로세스 상태 구조(PSS)로서, PSS는 각 연결마다 하나씩 있습니다. 서버는 PSS의 목록을 검색하여 sysprocesses 가상 테이블을 구체화합니다. 따라서, sysprocesses의 CPU 및 physical_io 열(Column)은 각 PSS에 있는 동등한 값으로부터 파생된것입니다.(됩니다.)

Transact-SQL KILL 명령은 spid의 프로세스 슬롯 구조(Process Slot Structure)에 "스스로 중지(Kill Yourself)"하라는 메시지를 보냅니다. 이 메시지는 spid가 주기적으로 검사하는 상태 비트로 표현됩니다.(나타납니다.) spid가 PSS 상태 필드를 검사하지 않는 코드 경로를 실행 중이면 KILL 명령은(이) 이행되지 않습니다. 이러한 상황이 발생할 수 있는 것으로 알려진 몇 가지 조건을 아래에서 설명합니다. 따라서, 이들 조건 대부분은 버그가 아니라 예상된 동작으로 간주됩니다.

 

Spid가 네트워크 입출력(Network I/O)을 기다리는 경우

클라이언트가 모든 결과 값(행)을 서버로부터 가져오지 못하면 결국 서버는 클라이언트가 결과값을 가져와서(에) 쓰는 동안습니다. 이 상황은 sysprocesses.waittype 0x0800으로 나타납니다. 네트워크를 기다리는 동안은 어떤 SQL Server 코드도 PSS를 검사하고 KILL 명령을 검색할 수 없습니다.(있는 SQL Server 코드가 실행되지 않습니다.) 네트워크 입출력을 기다리기 전에 spid가(에) 잠금(Lock)에(이) 걸려 있으면 이는 다른 프로세스를(가) 차단시킬수(될 수도) 있습니다.

네트워크 연결이 시간을 초과하거나 수동으로 취소되면 네트워크 입출력을 기다리는 SQL 스레드(Thread)는 오류 반환을 수신하고 그러므로써(그럼으로써) 대기 상태에서 해제되어 PSS를 검사하고 KILL 명령에 응답할 수 있게 됩니다. NET SESSION, NET FILES 명령이나 동일한 기능의 서버 관리자 명령을 사용하여 (수동으로) 명명된 파이프(Named Pipe) 연결을 수동으로 닫을 수 있습니다. TCP/IP와 SPX/IPX 같은 다른 IPC 세션은 수동으로 닫을 수 없으므로, 이 경우에 사용할 수 있는 유일한 방법은 특정 IPC에 대한 세션 시간 제한을 더 작은 값으로 조정하는 것 뿐입니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

 

137983  (http://support.microsoft.com/kb/137983/EN-US/ ) : How to Troubleshoot Orphaned Connections in SQL Server

 

Spid가 롤백(Roll Back) 또는 취소(Backout) 중인 경우

어떤 이유로든 트랜잭션이 중지되면 그 트랜잭션은 롤백(Roll Back)되어야 합니다. 오랫 동안 실행하는 트랜잭션이 중지된 경우에는 트랜잭션이 적용된 만큼(을 적용하는 것만큼) 롤백(Roll Back)하는데 시간이 오래 걸릴 수 있습니다. 이러한(그러한) 트랜잭션으로는 단일 SELECT INTO, DELETE 또는 UPDATE 문과 같이(은) 오랫 동안 실행한(하는) 암시적 트랜잭션이 있습니다. 롤백(Roll Back)하는 동안은 트랜잭션을 중지할 수 없습니다. 그렇지 않으면 트랜잭션 가능한 변경 내용을 일관성 있게 취소(Backout)할 수 없기 때문입니다.

(sp_who 출력에 ROLLBACK 명령이 나타날 수도 있으므로) 종종 sp_who 출력을 관찰함으로써 중지 불가능한 롤백(Roll Back) 상황을 확인할수 있는데 이는 sp_who 수행결과에서 ROLLBACK명령으로 나타날수 있습니다.(있습니다.) SQL Server 버전 6.5 서비스 팩 2 이상에는 sysprocesses.status에 ROLLBACK 상태가 추가되었습니다. 이 상태는 sp_who 출력이나 SQL 엔터프라이즈 관리자의 "현재 동작(Current Activity)" 화면에도 나타납니다. 그러나, 이 정보를 얻는 가장 신뢰할 만한(수 있는) 방법은 문제가 있는 차단 SPID의 DBCC PSS를 조사하고 pstat 값을 관찰하는 것입니다. 아래에 예가 있습니다.

 

dbcc traceon(3604) /* Return subsequent DBCC output to the client rather    
than to the errorlog. */
go
SELECT SUID FROM SYSPROCESSES WHERE SPID=<unkillable SPID number>
go
DBCC PSS (suid, spid, 0) /* Where suid is from above, and spid is the
                            unkillable SPID number. */
go

 

반환된 정보에서 첫째 줄은 pstat 값을 포함합니다.

예를 들어, pstat 값은 아래와 같습니다.

 

pstat=0x4000, 0x800, 0x100, 0x1

pstat 비트의 의미는 아래와 같습니다.

0x4000 -- 중요 섹션 안에 있으면 KILL 및 ATTENTION 신호를 지연시킵니다.
0x2000 -- 프로세스가 중지되고 있습니다(됩니다.)
0x800  -- 프로세스가 취소(Backout) 중이기 때문에 교착 상태 프로세스로 선택될 수 없습니다.
0x400  -- 프로세스가 ATTENTION 신호를 수신했고 내부 예외를
          발생하여 응답했습니다.
0x100  -- 프로세스가 단일 문 트랜잭션의 중간에 있습니다.
0x80   -- 프로세스가 분산(다중) 데이터베이스 트랜잭션과 관계됩니다.
0x8    -- 프로세스가 현재 트리거(Trigger)를 수행 중입니다.
0x2    -- 프로세스가 KILL 명령을 수신했습니다.
0x1    -- 프로세스가 ATTENTION 신호를 수신했습니다.

 

GUI 응용 프로그램에서 쿼리 취소 단추를 누르는 등의 취소 동작에 의해 오랫 동안 실행하는 데이터 수정 프로세스가 취소되고(된 다음) 해당 spid가 잠시 동안 사용자를 차단하는(여전히 중지할 수 없는)것으로 ( SPID가) 발견되었을 경우, 일반적으로 위와 같은 pstat 값이 나타납니다. 이 상황은 정상이며 트랜잭션은 취소(Backout)되어야 합니다. 위에 나와 있듯이 비트에서 이를 확인할 수 있습니다.

 

Spid 1이 상태 0000(복구 실행 중)을 가지는 경우

SQL Server를 시작하거나 재시작할 때 각 데이터베이스는(를) 사용되(하)기 전에 시작 복구가(를) 완료되어져야(해야) 합니다. 이러한 복구 진행상황은(이것은) sp_who에 있는(서) 첫번째 spid의 상태값이 0000을 가지는것으로 알수 있습니다.(나타납니다). 이 spid는 중지될 수 없으며 복구 프로세스는 서버를 다시 시작하지 않고도 완료할 때까지 실행될 수 있어야 합니다. lazywriter, 검사점(Checkpoint), RA Manager 같은 시스템 spid는 중지할 수 없고 사용자 spid만 중지할 수 있습니다. 또한 자신이 소유한 spid도 중지할 수 없습니다. SELECT @@SPID를 사용하면 자신의 spid를 알 수 있습니다.

 

서버가 KILL 이행을 고의로 지연시킨 경우

드문 경우지만 서버가 고의로 KILL 명령이나 ATTENTION 신호(쿼리 취소 요청)에 대한 조치를 지연시킬 수도 있습니다. 이러한 상황의 예는 중요 섹션에 있는 동안입니다. 이 간격은 대개 짧습니다. 이 상황은 pstat 값 0x4000으로 알 수 있습니다.

 

코드 경로가 KILL을 확인하지 않는 경우

위의 각 시나리오 중 어느 것에도 해당하지 않는다면 단순히 현재 코드 경로가 KILL을 확인하지 않는 경우일 수 있습니다. 예를 들어, SQL Server 버전 6.5 서비스 팩 3 이전에는 특정 코드 경로가 KILL을 확인하지 않았기 때문에 DBCC CHECKDB는(가) KILL 명령에 대해 신뢰성 있게 응답하지 않았습니다. 위의 모든 경우(즉, 사용자 프로세스가 입출력을 기다리거나 또는 롤백(Roll Back) 중이거나, 데이터베이스가 복구 중이거나 SQL Server가 고의로 KILL을 지연시키는 경우)에 해당하지 않지만 여전히 KILL 명령이 이행되지 않으면, KILL이 작동하도록 서버를 향상하는 것이 좋습니다. 이를 확인하려면 주 지원 공급자에게 각 경우를 개별적으로 검사하게 해야 합니다.

 

기타 정보

"호스트 이름 JOE가 프로세스 id 10을 중지했습니다."라는 메시지가 오류 로그에 기록되었다는 사실만으로 KILL이 실제로 이행되었다고 확신할 수는 없습니다. 이 메시지는 중지 요청을 보낸 직후 기록되므로 KILL이 실제로 이행되었음을 나타내는 것은 아닙니다.