[sql-server] 프로세스가 교착 상태의 희생양이되는 원인

Program / 글쓴이

완료하는 데 5 ~ 10 분 정도 걸리는 Select 프로세스가 있습니다.
현재 MS SQL 데이터베이스 엔진에 대한 힌트로 NOLOCK을 사용하지 않습니다.
동시에 동일한 데이터베이스 및 동일한 테이블에 업데이트 및 삽입을 수행하는 다른 프로세스가 있습니다.
첫 번째 프로세스가 시작되었으며 최근에 메시지와 함께 조기 종료됩니다.

SQLEXCEPTION : 트랜잭션이 다른 프로세스와 함께 잠금 리소스에서 교착 상태가되었고 교착 상태 희생자로 선택되었습니다.

이 첫 번째 프로세스는 동일한 조건으로 다른 사이트에서 실행되지만 데이터베이스가 더 작아서 문제의 select 문이 훨씬 더 짧은 시간 (약 30 초 정도)이 걸립니다. 이러한 다른 사이트에서는 이러한 다른 사이트에서 교착 상태 메시지가 표시되지 않습니다. 또한 처음에 문제가 발생한 사이트에서이 메시지를받지 못했지만 데이터베이스가 커짐에 따라 어느 정도 임계 값을 넘었을 것입니다. 내 질문은 다음과 같습니다.

  1. 트랜잭션을 실행하는 데 걸리는 시간으로 인해 관련 프로세스가 교착 상태로 표시 될 가능성이 높아질 수 있습니다.
  2. NOLOCK 힌트로 선택을 실행하면 문제가 제거됩니까?
  3. select 문에서 WHERE 절의 일부로 확인 된 datetime 필드로 인해 조회 시간이 느려진 것 같습니다. 이 필드를 기반으로 색인을 만들 수 있습니까? 권장됩니까?

답변

Q1 : 트랜잭션이 실행되는 데 걸리는 시간으로 인해 관련 프로세스가 교착 상태로 표시 될 가능성이 높아질 수 있습니다.

아니요. SELECT는 데이터를 읽기만 했으므로 희생자이므로 트랜잭션 과 관련된 비용이 더 낮 으므로 희생자로 선택됩니다.

기본적으로 데이터베이스 엔진은 롤백하는 데 가장 비용 이 적게 드는 트랜잭션을 실행하는 세션을 교착 상태로 선택합니다 . 또는 사용자가 SET DEADLOCK_PRIORITY명령문을 사용하여 교착 상태 상황에서 세션의 우선 순위를 지정할 수 있습니다 . DEADLOCK_PRIORITY는 LOW, NORMAL 또는 HIGH로 설정하거나 또는 범위 (-10 ~ 10)의 정수 값으로 설정할 수 있습니다.

Q2. NOLOCK 힌트로 선택을 실행하면 문제가 제거됩니까?

아니요. 몇 가지 이유가 있습니다.

Q3. select 문에서 WHERE 절의 일부로 확인 된 datetime 필드로 인해 조회 시간이 느려진 것 같습니다. 이 필드를 기반으로 색인을 만들 수 있습니까? 권장됩니까?

아마. 교착 상태의 원인은 거의 색인이 제대로되지 않은 데이터베이스 일 가능성이 매우 높습니다. 10 분 쿼리는 이러한 좁은 조건에서 허용되며, 귀하의 경우 100 % 확신 할 수 없습니다 .

99 %의 신뢰를 바탕으로 업데이트와 충돌하는 대형 테이블 스캔으로 인해 교착 상태가 발생한다고 선언합니다. 교착 상태 그래프 를 캡처 하여 원인을 분석하십시오. 데이터베이스의 스키마를 최적화해야 할 가능성이 높습니다. 수정하기 전에 색인 및 하위 문서 디자인 주제를 읽으십시오 .

답변

이 특정 교착 상태 문제가 실제로 발생하는 방법과 실제로 해결 된 방법은 다음과 같습니다. 이것은 매일 130K 트랜잭션이 발생하는 상당히 활동적인 데이터베이스입니다. 이 데이터베이스의 테이블에있는 인덱스는 원래 클러스터링되었습니다. 클라이언트는 인덱스를 클러스터링하지 않도록 요청했습니다. 그렇게하자마자 교착 상태가 시작되었습니다. 인덱스를 클러스터 된 상태로 다시 설정하면 교착 상태가 중지되었습니다.

답변

여기에있는 답변은 시도해 볼 가치가 있지만 코드도 검토해야합니다. 특히 Polyfun의 답변을 읽으십시오.
SQL Server 2005 및 C # 응용 프로그램에서 교착 상태를 제거하는 방법은 무엇입니까?

동시성 문제와 쿼리에서 “with (updlock)”를 사용하면 실제로 코드가 수행하는 작업에 따라 교착 상태 상황을 수정할 수있는 방법에 대해 설명합니다. 코드가이 패턴을 따르는 경우 더티 읽기 등에 의존하기 전에 이것이 더 나은 수정일 가능성이 높습니다.

답변

@ Remus Rusanu ‘s가 이미 탁월한 대답이지만 SQL Server의 교착 상태 원인 및 추적 전략 에 대한 더 나은 통찰력을 기대하는 경우 Brad McGehee 의 SQL Server 2005 프로파일 러를 사용하여 교착 상태를 추적하는 방법을 읽어 보는 것이 좋습니다.

답변