잠금고려사항

1) BLOCKING 시나리오

잠금에 대한 BLOCKING 문제의 대부분은 단일 프로세스가 오래도록 잠금을 유지하여 다른
모든 프로세스가 잠금 대기 상태로 되는 블로킹된 프로세스 체인을 초래하기 때문에 발생한다.
일반적인 BLOCKING 시나리오는 다음과 같다.

1] 실행 시간이 긴 쿼리 전송
실행 시간이 긴 쿼리는 다른 쿼리를 BLOCKING 할 가능성이 크다. 예를 들어, 많은 행에 영향을 주는
DELETE, UPDATE 작업에서는 테이블 전체에 대한 잠금과 상관없이, 다른 쿼리를 BLOCKING 하는
잠금을 많이 설정할 수 있다. 따라서, 일반적으로 실행 시간이 긴 OLAP 용 질의를 OLTP 와 동일한
데이터베이스나 서버에서 사용하는 것은 결코 권장하지 않는다. 물론 대용량 데이터베이스에서는
이런 일이 없을뿐더러, 중소규모 데이터베이스에서도 이런 일은 발생하지 않아야 한다.
이에 대한 해결 방법은 인덱스를 변경하거나, 크고 복잡한 쿼리를 간단한 쿼리로 분할하거나,
한가한 시간에 또는 별도의 컴퓨터에서 쿼리를 실행하여 쿼리를 최적화할 수 있는
방법을 찾는 것이 최고다.

쿼리가 오래도록 실행되어 BLOCKING 을 초래하게 되는 한 가지 이유는 커서를 잘못 사용하는 경우를
들 수 있다. 커서는 결과 집합을 편리하게 탐색할 수 있는 방법이지만, 커서를 사용하게 되면
레코드셋 지향의 쿼리보다 속도가 느려질 가능성이 크다.

2] COMMIT /ROLLBACK 되지 않은 쿼리를 취소하는 경우
응용 프로그램에서 쿼리를 취소하는 경우, 예를 들면 ROLLBACK과 COMMIT 문을 필요한 횟수만큼
실행하지 않고서 ODBC sqlcancel 함수를 사용하는 경우 발생할 수 있다.
쿼리를 취소하면 트랜잭션이 자동으로 롤백되거나 커밋되지 않는다. 따라서 트랜잭션 안에서 얻은
모든 잠금은 쿼리가 취소된 후에도 유지된다. 이런 상황이라면, 응용 프로그램에서는 취소된 트랜잭션을
커밋하거나 롤백함으로써, 트랜잭션 중첩 수준을 적절히 관리해야 할 것이다.

3] 모든 결과 처리를 완료하지 않는 응용 프로그램
응용프로그램에서 특정 쿼리를 DB 서버로 보낸 후, 모든 응용 프로그램에서는 모든 결과 행을 완료하기
위해 즉시 반입해야 한다. 응용 프로그램에서 모든 결과 행을 반입하지 않으면 테이블에 잠금이 남게
되어 다른 사용자를 BLOCKING 하게 될 수도 있다. Transact-SQL 문을 서버로 투명하게 전송하는
응용 프로그램을 사용하는 경우에는 응용 프로그램에서 모든 결과 행을 반입해야 한다. 그렇지 않은 경우
및 그렇게 구성할 수 없는 경우에는 BLOCKING 문제를 해결하지 못할 수도 있다.
이러한 응용 프로그램을 보고 또는 의사 결정 지원 데이터베이스로 제한하면 문제를 피할 수 있다.

4] 분산 클라이언트/서버 교착 상태
기본 교착 상태와 달리, 안타깝게도 SQL Server 2000은 분산 교착 상태를 자동으로 검색할 수 없다.
분산 클라이언트/서버 교착 상태는 응용 프로그램에서 SQL Server에 대해 여러 개의 연결을 열고
쿼리를 비동기식으로 전송하는 경우에 발생할 수 있다.

예를 들어, 단일 클라이언트 응용 프로그램 스레드에 두 개의 연결이 열려 있고, 비동기식으로 트랜잭션을
시작하며 첫 번째 연결에 대해 쿼리를 실행한다. 그런 다음, 응용 프로그램에서 다른 트랜잭션을 시작하고,
또 하나의 연결에 대해 쿼리를 실행하고, 결과를 기다린다. SQL Server에서 한 개의 연결에 대한 결과를
반환하면 응용 프로그램이 결과를 처리하기 시작한다. 결과를 생성하는 쿼리는 다른 연결에 대해 실행되는
쿼리에 의해 BLOCKING 되므로 응용 프로그램은 더 이상 사용 가능한 결과가 없을 때까지 결과를 처리한다.
이 시점에서 첫 번째 연결이 BLOCKING 되어 처리할 결과를 무한정 기다리게 된다. 두 번째 연결은
잠금 상태로 BLOCKING 되지는 않지만, 응용 프로그램에게 결과 반환을 시도한다.
그러나 응용 프로그램이 BLOCKING 되어 첫 번째 연결에 대한 결과를 기다리므로, 두 번째 연결에 대한
결과는 처리되지 않는다.
다음 중 하나를 사용하여 이 문제를 방지할 수 있다.

- 각 쿼리에 대한 쿼리 제한 시간
- 각 쿼리에 대한 잠금 제한 시간.
- 바운드 연결.

SQL Server는 기본적으로 클라이언트 응용 프로그램의 명령에 따라 움직인다. 서버에서 얻는 잠금에
대해서는 클라이언트 응용 프로그램이 거의 모든 제어권(및 책임)을 가진다. SQL Server 잠금 관리자는
잠금을 사용하여 자동으로 트랜잭션을 보호하지만, 이것은 클라이언트 응용 프로그램이 보낸 쿼리
유형과 결과가 처리되는 방식에 의해 직접 이루어진다. 따라서, BLOCKING 문제를 해결할 때는
대부분의 경우 클라이언트 응용 프로그램 검사가 수반된다.

BLOCKING 문제를 해결하기 위해서는 응용 프로그램이 전송하는 SQL 문과 연결 관리,
모든 결과 행 처리 등에 관련된 응용 프로그램의 동작이 모두 정확한지 검사해야 하는 경우가 많다.
개발 도구에서 연결 관리, 쿼리 제한 시간, 결과 처리 등에 대한 명시적인 제어가 허용되지 않으면
BLOCKING 문제는 해결되지 않을 수도 있다.


2) 블로킹을 방지하기 위한 응용 프로그램 디자인 지침

1] 실행 시간이 긴 쿼리가 생성될 수 있는 응용 프로그램은 사용하거나 디자인하지 않는 것이 좋다.
예를 들어, 응용프로그램에서 특정 필드를 공백으로 두거나, 와일드카드를 입력할 수 있게 허용하지 않고
사용자에게 입력을 요구하는 응용 프로그램은, 사용하거나 디자인하지 말자. 이렇게 하면 응용 프로그램에서
실행 시간이 너무 긴 쿼리를 전송하게 되어 BLOCKING 문제를 초래할 수 있기 때문이다.

2] 트랜잭션 내에서 사용자 입력을 허용하는 응용 프로그램을 사용하거나 디자인하지 않는다.

3] 쿼리 취소를 허용하게 한다.

4] 쿼리 또는 잠금 제한 시간을 사용하여 쿼리가 무한정 실행되지 않게 하며, 분산 교착 상태를 방지한다.

5] 모든 결과 집합을 즉시 반입하여 완료한다.

6] 트랜잭션을 되도록 간략하게 유지한다.

7] 연결 관리를 명시적으로 제어한다.

8] 예상되는 전체 동시 사용자 로드로 응용 프로그램의 문제 상황을 테스트한다.