My Story

SQL 기본: SQL Server 2005에서 TRY/CATCH를 사용하여 교착 상태 해결

Ron Talmage

오늘날의 RDBMS 아키텍처에서 교착 상태는 피할 수 없으며 고용량의 OLTP 환경에서 흔히 나타납니다. 하지만 .NET 공용 언어 런타임 덕분에 SQL Server 2005는 개발자에게 새로운 오류 처리 방법을 제공하고 있습니다. 이번 달 칼럼에서 Ron Talmage는 TRY/CATCH를 사용하여 교착 상태를 해결하는 방법을 보여줍니다.

T-SQL은 메시지 전달에는 뛰어나지만 오류 차단 도구를 제공하는 데는 그다지 뛰어나지 않습니다. 제 생각에 거의 모든 DBA는 다음과 같은 1205 "deadlock victim" 오류 메시지에 익숙할 것입니다.

Transaction (Process ID 52) was deadlocked on lock 
resources with another process and has been chosen as 
the deadlock victim. Rerun the transaction.

코드에서 교착 상태 상태 발생한 경우, 저장 프로시저에서 응용 프로그램이 얼마나 깊이 중첩되어 있는지는 중요하지 않습니다. 교착 상태에서 처리되지 않은 spid의 자체 일괄 처리를 중단하고 1205 오류가 클라이언트에 반환됩니다. 트랜잭션을 다시 시도하라는 오류 메시지가 나타나지만 T-SQL 코드 내에서는 그럴 수 없습니다. 이 다시 시도 동작은 호출 응용 프로그램에서 수행되어야 합니다. 이 오류는 쉽게 포착되지 않으며 @@ERROR가 소용 없습니다. 그다지 좋은 상황은 아닙니다.

교착 상태 예제
SQL Server 2000 및 2005에서 교착 상태을 유발하는 예제에 대해 먼저 살펴보겠습니다. 이 기사에서 저는 SQL Server 2005의 최신 CTP(Community Technology Preview)를 사용했지만 SQL Server 2005 베타 2(지난 7월에 출시)를 사용해도 괜찮습니다. 베타 2 또는 최신 CTP 중 하나에 액세스하지 못하는 경우 최신 버전의 SQL Server 2005 Express를 다운로드하여 시험할 수 있습니다.

발생할 수 있는 교착 상태의 종류는 다양하지만 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp (영문) 기사 및 교착 상태 기사 트리의 이어지는 기사 참조] 가장 흥미롭고도 어려운 교착 상태 중 하나는 판독기(reader)와 작성기(writer)가 서로를 차단하는 경우입니다. 다음 코드는 Pubs 데이터베이스에서의 이러한 교착 상태를 보여줍니다. (SQL Server 2000의 두 쿼리 분석기 창이나 SQL Server 2005의 두 Management Studio 쿼리에서 이 코드를 나란히 실행할 수 있습니다.) 첫 번째 창에서 코드 본문 앞에 다음 코드를 넣습니다.

-- Window 1 header
DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = ''111-11-1111'', @au_lname = ''test1''

두 번째 연결이 있는 두 번째 창에서 다음 코드를 사용합니다.

-- Window 2 header
DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = ''111-11-1112'', @au_lname = ''test2''

그런 다음 두 창에서 다음 코드를 코드 본문으로 사용합니다.

-- Body for both connections:
BEGIN TRANSACTION
INSERT Authors VALUES 
  (@au_id, @au_lname, '''', '''', '''', '''', '''', ''11111'', 0)
WAITFOR DELAY ''00:00:05''
SELECT *
  FROM authors
  WHERE au_lname LIKE ''Test%''
COMMIT

이 ID의 데이터가 Authors 테이블에 없는지 확인하기 위해 세 번째 창에서 다음 코드를 실행합니다.

DELETE FROM authors WHERE au_id = ''111-11-1111''
DELETE FROM authors WHERE au_id = ''111-11-1112''

이제 서로 5초 이내에 Window 1과 Window 2를 실행합니다. 각 연결이 INSERT를 종료합니다. 그리고 각각 5초를 기다린 후에 SELECT 문을 생성하므로 SELECT가 생성되기 전에 두 INSERT를 완료할 수 있습니다. 각 SELECT 문은 LIKE ''Test%''인 au_lname 값을 가지는 모든 데이터를 Authors 테이블에서 읽으려고 시도합니다. 따라서 각 SELECT 문은 자체 연결의 삽입된 데이터뿐만 아니라 다른 연결의 삽입된 데이터를 읽으려고 시도합니다.

READ COMMITTED 격리 수준은 SELECT 문이 커밋되지 않은 데이터를 읽지 않도록 하기 위해 공유 잠금을 생성합니다. 공유 잠금은 동일한 리소스상의 단독 잠금과 호환되지 않습니다. 그리고 요청자가 공유 잠금을 생성할 수 있으려면 단독 잠금이 해제될 때까지 기다려야 합니다. 각 연결의 삽입된 데이터에는 이미 단독 잠금이 있으므로 두 연결 간의 삽입된 데이터를 읽으려고 시도하는 SELECT 문이 공유 잠금을 획득하려고 시도하고 차단됩니다. 두 연결이 서로를 상호 차단하여 교착 상태를 유발합니다. SQL Server의 잠금 관리자는 교착 상태를 감지하고 배치 중 하나를 중단하고 트랜잭션을 롤백하여 차단 잠금을 해제하므로 다른 트랜잭션이 완료될 수 있습니다. 교착 상태 피해자의 트랜잭션이 롤백되고 다른 트랜잭션이 계속됩니다.

TRY/CATCH 사용 방법
이제 코드 본문을 수정하고 TRY/CATCH를 사용해 보겠습니다. (이 예제에서는 SQL Server 2005 버전에서 코드를 실행해야 합니다.) TRY/CATCH를 사용할 경우 작업 코드와 오류 처리 코드를 분리해야 합니다. 작업 코드는 TRY 블록에 넣고 오류 처리 코드는 CATCH 블록에 넣습니다. TRY 블록의 코드가 실패하면 CATCH 블록으로 점프하여 코드가 실행됩니다. 이 동작은 전체 배치가 실행되는 것을 막는 오류(예: 개체 없음)를 제외한 모든 오류에서 유효합니다.

다음은 위와 동일한 코드를 사용하는 TRY/CATCH의 예제입니다. 동일한 헤더를 사용하고 코드 본문은 변경합니다.

BEGIN TRANSACTION
BEGIN TRY
  INSERT Authors VALUES 
  (@au_id, @au_lname, '''', '''', '''', '''', '''', ''11111'', 0)
  WAITFOR DELAY ''00:00:05''
  SELECT COUNT(*)  FROM Authors 
  COMMIT
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber
  ROLLBACK
END CATCH;
SELECT @@TRANCOUNT AS ''@@Trancount''

이제 SQL Server 2005에 나란히 연결된 창에서 이 코드를 실행하면 삽입을 막는 특정 데이터가 Authors 테이블에서 삭제되었는지 확인할 수 있습니다. 선행 DELETE 문을 사용할 수 있습니다.

두 창이 @@TRANCOUNT 수준 0을 반환합니다. 교착 상태는 여전히 발생하지만 이제 TRY/CATCH가 이 교착 상태를 격리합니다. 더 이상 피해자의 일괄 처리가 중단되지 않으며 교착 상태 피해자의 출력에 다음과 같은 오류를 볼 수 있습니다.

ErrorNumber
-----------
1205

@@Trancount
-----------
0

이제 TRY/CATCH가 제공하는 성능을 알 수 있을 것입니다. CATCH 블록에서 교착 상태 오류까지도 격리할 수 있기 때문에 더 이상 일괄 처리가 중단되지 않으며 T-SQL 코드가 계속해서 실행될 수 있습니다. 교착 상태 피해자를 위해 교착 상태 오류 1205는 T-SQL 코드를 CATCH 블록에 넣습니다. 이 블록에서 새로운 오류 처리 기능을 사용하여 코드를 탐색할 수 있습니다. 선행 코드에서는 @@ERROR 대신 ERROR_NUMBER()만을 사용하지만 ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_SEVERITY() 및 ERROR_STATE()를 사용할 수도 있습니다. 이 기능은 쉬우면서도 이전의 기능보다 훨씬 뛰어난 성능을 제공합니다.

참고로 선행 CATCH 블록 코드에는 ROLLBACK이 포함되는데 그 이유는 교착 상태 오류가 격리되더라도 트랜잭션이 롤백되지 않기 때문입니다. 트랜잭션은 여전히 불안하지만 이제 TRY/CATCH 내에서 이 트랜잭션을 롤백하는 것이 여러분의 책임입니다. 그렇다면 무슨 차이가 있습니까? 비록 트랜잭션을 계속할 수는 없더라도 다시 시도할 수는 있다는 것입니다.

TRY/CATCH에서 다시 시도
SQL Server 2000의 T-SQL에서 1205 오류는 "트랜잭션을 다시 실행"하라는 암시입니다. 문제는 트랜잭션을 다시 실행할 수 없다는 것입니다. 적어도 SQL Server 2000의 T-SQL에서는 그렇습니다. 하지만 SQL Server 2005의 TRY/CATCH를 사용하여 교착 상태 오류를 격리할 수 있으므로 이제 트랜잭션을 다시 시도할 수 있습니다.

다음 코드 본문은 다시 시도를 실행하기 위한 한 방법을 보여줍니다. 위에 나타난 동일한 헤더와 함께 이 코드를 다시 사용하십시오.

DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
    INSERT Authors VALUES 
      (@au_id, @au_lname, '''', '''', '''', '''', '''', 
''11111'', 0)
    WAITFOR DELAY ''00:00:05''
    SELECT * FROM authors WHERE au_lname LIKE ''Test%''
    COMMIT
    BREAK
  END TRY
  BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
    ROLLBACK
    SET @Tries = @Tries + 1
    CONTINUE
  END CATCH;
END

이 코드가 수행하는 동작은 WHILE 루프를 사용하여 다시 시도를 추가하는 것입니다. 저는 다시 시도 횟수를 세 번으로 설정했지만 변경이 가능합니다. 요컨대 이전에는 불가능했지만 이제는 T-SQL 내에서 교착 상태 피해자의 코드를 다시 시도할 수 있습니다.

하지만 전체 트랜잭션은 WHILE 루프 외부가 아니라 내부에 있다는 점에 주의하십시오. 따라서 각 루프 내에서 트랜잭션이 시작할 뿐만 아니라 COMMIT(TRY 블록이 실행되는 경우) 또는 ROLLBACK(CATCH 블록이 실행되는 경우)으로 끝납니다. TRY 블록은 BREAK 명령으로 끝나며 이 TRY 블록이 성공한 경우 WHILE 루프가 종료됩니다. CATCH 블록은 다시 시도 카운터를 증가시키며, WHILE 루프를 다시 실행하는 CONTINUE 명령으로 끝납니다. 1205 오류와 마찬가지로 실제로 트랜잭션을 다시 시도하는 코드가 있습니다. 하지만 이제 T-SQL 내에서 다시 시도가 완벽하게 수행됩니다.

SQL Server 2005에는 교착 상태를 해결하기 위한 다른 방법이 있습니다. 예를 들어, SNAPSHOT ISOLATION 수준과 READ COMMITTED용의 새로운 옵션(READ COMMITTED SNAPSHOT)이 있습니다. 하지만 이제 SQL Server 2005에서 트랜잭션을 코딩하고 교착 상태 오류를 격리한 후 트랜잭션을 다시 시도할 수 있기 때문에 훨씬 더 강력해진 도구를 마음대로 사용할 수 있습니다.

다운로드 코드(505RON.SQL)를 보려면 다운로드 단추를 클릭하십시오.

TSQL Server Professsional과 Pinnacle Publishing에 대한 자세한 내용은 회사 웹 사이트( http://www.pinpub.com/ (영문))를 방문하십시오.

참고: 이 사이트는 Microsoft Corporation 웹 사이트가 아니며 Microsoft는 사이트의 내용에 책임이 없습니다.

이 기사는 SQL Server Professsional 2005년 5월 호에서 복제된 것입니다. Copyright 2005 Pinnacle Publishing, Inc.(별도의 언급이 없는 경우). All rights reserved. SQL Server Professsional은 Pinnacle Publishing, Inc.이 독립적으로 제작하는 간행물입니다. Pinnacle Publishing의 사전 동의 없이는 이 기사의 어떠한 부분도 임의로 사용하거나 복제할 수 없습니다(중요한 기사와 비평에 사용되는 간단한 인용 제외). Pinnacle Publishing, Inc.에 연락하려면 1-800-788-1900으로 전화를 주십시오.