수안이의 컴퓨터 연구실

Tony Patton ( TechRepublic ) 2006/06/09


.NET 애플리케이션 코드에서 예외를 처리하는 것은 매우 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 살펴본다.

개발자들은 예외(exception)를 처리하는데 친절하게도 많은 노력을 기울이기 때문에 사용자들은 알 수 없는 시스템 에러 메시지를 걱정할 필요가 없다. 이런 이유 때문에, 예외 처리는 모든 .NET 애플리케이션의 표준 항목이다. try/catch 블록은 개발자가 예외를 잡아내고 그 시점에서의 애플리케이션 실행을 컨트롤할 수 있도록 해준다. 많은 에러들은 데이터베이스 처리 중에 발생하지만 많은 개발자들은 데이터베이스 부분에서 생기는 에러를 처리하는 것을 알지 못한다. 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 알아보자.

T-SQL에서 발생한 에러 처리하기
SQL 서버가 제공하는 T-SQL 언어는 저장 프로시저, 함수 등에서 발생할 수 있는 치명적이지 않은 에러를 쉽게 처리할 수 있게 해주지만, 모든 에러가 쉽게 처리할 수 있게 되는 것은 아니다. 사실, 에러에는 치명적인 에러와 치명적이지 않은 에러가 있는데, 치명적이지 않은 에러와는 달리 치명적인 에러는 실행이 중단된다.

트랜잭션
변경사항이 모두 완료돼 모든 것이 정상인 것을 확실히 하기 위해서는 데이터베이스 코드에 트랜잭션을 사용해야만 한다. SQL 서버 온라인 도움말은 selects, inserts, updates 혹은 deletes와 같은 명령행의 연속으로 이루어진 논리적 작업 단위라고 설명한다. 만약 트랜잭션동안 에러가 없다면 트랜잭션의 모든 변경 사항은 데이터베이스에 적용될 것이며, 만약 에러가 발생하면, 어떤 변경사항도 데이터베이스에 적용되지 않는다.

트랜잭션은 BEGIN TRANSACTION과 END TRANSACTION 명령 사이에 포함된다. ROLLBACK TRANSACTION 명령은 모든 변경사항을 취소하도록 하여, 어떤 변경사항도 이루어지지 않게 한다. COMMIT TRANSACTION 명령은 변경사항을 데이터베이스에 반영한다. 이제, T-SQL에서 에러를 처리하는 방법을 알아보자.

@@Error
@@Error 함수는 T-SQL을 만들 때 에러를 처리하도록 해준다. 이 함수는 시스템의 에러 코드를 돌려준다. 만약 에러가 없으면 0을 리턴 한다. @@Error 함수는 각 T-SQL 명령이 실행되면 초기화되기 때문에, 명령을 호출한 직후 바로 불러야한다.

RAISERROR
RAISERROR 명령은 커스텀 에러 메시지를 만들거나 sysmessages 테이블에 이미 있는 메시지를 사용할 수 있게 해준다. 이 구문의 문법은 온라인으로 볼 수 있지만, 가장 기본적인 형태는 에러의 심각도, 상태와 함께 메시지나 메시지 ID를 포함한다. 상태는 SQL 서버에서 사용하지 않기 때문에 임의의 숫자를 이용해 처리한다. 심각도는 에러의 심각성을 나타내는데 0~18은 사용자가 사용할 수 있으며 19~25는 관리자를 위해 예약돼 있다.

예제 1. 이 예제 저장 프로시저는 Northwind 데이터베이스의 개별 레코드를 업데이트하는데 이 기능들을 사용한다. 에러가 없을 경우 전화 번호 칼럼의 값을 프로시저를 통해 수정한다. 만약 에러가 발생하면 음수를, 에러가 없으면 양수를 돌려주는 리턴 값을 사용한다.

저장 프로시저의 리턴 값 사용하기
.NET 코드에 저장 프로시저의 리턴 값을 사용할 수 있다. SqlCommand 객체는 저장된 리턴 값뿐만 아니라 쉽게 프로시저에 파라미터를 넘길 수 있도록 해준다. 파라미터의 Direction 속성은 저장 프로시저 호출을 통한 리턴 값을 얻는데 사용되는데, 이 속성은 InputOutput과 Output이 될 수 있다. 다음 예제에서는 상태 값을 받기 위해 Output을 사용하였다.

다음 예제는 Northwind 데이터베이스의 customers 테이블의 특정 레코드에 새로운 값을 저장하는 간단한 ASP.NET 페이지이다. id 값은 실제로는 hidden 필드로 저장된다. form을 통해 값을 쉽게 넘길 수 있지만, 데모를 위해 예제와 같이 했다. text 필드에 입력된 값은 phone 필드를 업데이트 하는데 사용된다.

파라미터는 SqlCommand 객체에 추가할 수 있으며 저장 프로시저의 파라미터 값과 정확히 일치해야한다. 이 작업은 SqlCommand 객체의 ExecuteNonQuery를 통해 실행된다. 이것이 실행되면, 파라미터를 통해 리턴 값을 받을 수 있다.

다음 예제는 리턴 값을 검사 하고(-1은 문제가 있음을 뜻한다) Label 컨트롤에 메시지를 표시한다. 추가로 데이터베이스 처리 중에 발생할 수 있는 치명적인 에러를 잡기 위해 try/catch 블록이 사용되었다. 예제 2. 예제 3은 같은 작업을 하는 VB.NET 코드이다

필요한 모든 것 제공
.NET 애플리케이션 코드에서 예외를 처리하는 것은 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 하지만, 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, SQL 서버의 T-SQL은 코드를 실행하면서 확인할 수 있는 모든 것들을 제공한다.@


http://www.zdnet.co.kr/builder/dev/etc/ ··· 2C00.htm

차주언 | SQL 컨설턴트

잠금

번호 수칙 체크
1 시간이 너무 오래걸리는 트랜잭션이 있는가?
2 데드락을 모니터링해서 개발자에게 해결을 요청했는가?

수칙1과2.시간이 너무오래걸리는 트랜잭션이나 데드락을 모니터링 해서 뽑아내는가?

위의 두가지 사항은 손쉽게 프로필러로 뽑아낼 수 있습니다. 다음의 따라하기는 단순한 모니터링일뿐 근원적인 해결책은 아 닙니다. 해결은 도움말 잠금편을 읽어보고 해결해야만 합니다. 또는 외부에 해결을 의뢰하기 위해 파일로 저장할 수도 있습 니다.

[따라하기]
01.데드락 유발쿼리 입니다 쿼리창을 열어 각각의 쿼리를 거의 동시에 수행해 봅시다. 물론 아래의 데드락 해결방법은 쿼리 실행 순서를 둘다 동일하게 tb_test,tb_test2순으로 작성하면 아무런 문제가 없어집니다.






02.프로필러를 실행 시킵니다. 새 추적을 클릭합니다






03. 데드락을 감시하고자 하는 서버에 접속합니다.






04. 데드락을 검사하려면 이름을 정하고






05. 이벤트텝에서 잠금 항목의 데드락 및 체인을 선택합니다.






06. 데드락으로 표시되는부분은 붉게 표시되어 나타납니다. 개발자와 상담하여 데드락을 유발시키는 쿼리를 라이브락으로 변경 시켜주어야합니다.






07.기다리면 시스템에서 발생하는 쿼리들중 데드락인 부분은 붉게 표시되어 나타납니다. 추가로 앞서 제작해 두었던 sp_blocker_pss80 도 꼭 실험을 해보도록 해야합니다.

그렇다면 보다 자세한 잠금관련 공부를 해보겠습니다

잠금관련 권고사항
가. 트랜잭션은 가능한 짧게 한다
나. 데드락을 피하기 위해
A. 같은방향으로 트랜잭션을 진행합니다.
B. 잠금수준을 올려줄 수도 있어야 합니다
다. 잠금수준을 내려서 불필요한 잠금을 없애야합니다(read uncommitted)
라. 트리거를 사용하지 않습니다.
마. 대규모 데이터 변경시에만 커서를 사용합니다.

[따라하기- 트랜잭션은 왜 짧아야 하는가?]
트랜잭션이 길어지면 잠금이 길어지고 그만큼 다른 작업도 지연된다 다음 대표적인 잠금대기 실험을 해보겠습니다.

1.다음과 같이 창을 두개 만듭니다. CTRL+N을 클릭하여 새창을 만든 후 메뉴에서 창 > 새로 바둑판식 배열을 선택합니다.






2.왼쪽창에선 트랜잭션 중간에 멈추고 오른쪽에서 그 트랜잭션이 잠그는 부분과 충돌하는 장면을 연출해 봅시다.

3.왼쪽창을 다 실행한 다음 오른쪽을 실행합니다.(price는 기존의 값이 아닌값이면 됩니다)






3.실행결과 오른쪽 쿼리는 무한대기가 걸린다. 언제까지 무한대기냐면 왼쪽에서 commit tran (rollback tran)할때까지 입니 다. 즉 트랜잭션이 끝날때까지 입니다. 이를 라이브락 이라합니다.





4.성공적으로 오른쪽 쿼리가 실행됩니다. 따라서 트랜잭션은 빠르면 빠를 수 록 좋습니다.

[따라하기 - 잠금수준을 낮춰서 잠금 충돌을 회피합시다]
1.왼쪽 쿼리를 commit tran을 하지 않은 상태에서 다음과 같은 오른쪽 쿼리를 실행합니다.





2.실제 데이터가 아닌 버퍼에 있는 값을 읽어오므로 잠금과는 상관이 없는 readuncommitted를 사용한 예제입니다.

[따라하기 - 데드락을 피하는 노력들!]
1.실험에 사용할 테이블 두개를 만듭니다.





2.데드락 중에서 순환데드락을 구현해 보겠습니다 순환 데드락이란 서로가 서로 업데이트할 내용을 막고 있는 형태입니다.





3.위의 상황은 업데이트 잠금 때문에 다른 트랜잭션의 업데이트가 실행되지 못하는 것 입니다 해결하려면 다음과 같이 해줘 야 합니다. 같은 방향으로 트랜잭션을 진행합니다.





[따라하기 - 변환 데드락]
1.같은 방법으로 이번엔 잠금수준을 변경함으로써 데드락을 해소해 보겠습니다. 우선 단순히 양쪽쿼리는 select 후 update 를 시도합니다. 여기서 repeatableread를 한 이유는 select(공유)잠금이 트랜잭션 동안 유지되게 하기 위해서입니다.




2.select 와 공유는 동시에 되지 않습니다. 차라리 select 할때 다른 트랜잭션도 select못하도록 잠금 수준을 향상 시킵니다 .




데드락의 대표예 두가지를 성공적으로 해결했습니다. 결론을 다시 반복하자면 순환 데드락의 경우는 트랜잭션 방향을 일방 통행으로 변환데드락은 잠금 수준을 조정함으로써 해결 해야합니다.

제공 : DB포탈사이트 DBguide.net

출처명 : 한국마이크로소프트

다음 다섯 개의 팁이 성능을 향상 시킬 것이다.

Kalen Delaney

SQL 서버 매거진의 편집자는 지난 5년간 필자가 얻은 최고의 팁이라고 생각되는 것 다섯 개를 제출하도록 요구 해왔다. 처음에는 필자가 쓰고 있는 정보는 전형적으로 팁이라고 불리기에 적합하지 않은 것이라고 생각했지만 곧 그렇지 않다는 것을 발견했다. 종종, 필자는 SQL 서버의 내부 동작에 대해 다루어왔으며 이런 정보들을 어떻게 사용하여 이점을 얻을 수 있는지 제안 하곤 했다. 그래서 이런 내부 정보들의 가치를 발휘할 수 있도록 팁으로 만들어보고자 한다.

쿼리가 커버 되도록 한다.
TIP 넌클러스터 인덱스에 하나 혹은 두개의 컬럼을 추가하여 중요한 혹은 자주 수행되는 쿼리가 커버 되도록 해보자.
클러스터 인덱스는 데이터를 인덱스 키 순서대로 정렬하여 저장하기 때문에 그것은 많은 양의 중복된 데이터나 일정 범위의 데이터를 가져올 때 대단히 효율적이다. 그러나, 테이블은 오직 하나의 클러스터 인덱스만 가질 수 있다. 넌클러스터 인덱스 역시 정렬된 데이터를 가질 수 있지만 오직 인덱스의 리프레벨에서만 그렇다. 따라서, city 컬럼에 걸린 넌클러스터 인덱스는 모든 도시에 대해 알파벳 순서로 정렬되어 있지만 이들 도시와 관련된 고객의 이름까지 원한다면 각 데이터들과 연결되어 이들이 가리키고 있는 포인터(즉 책갈피, bookmark)를 따라가야만 한다. 수많은 고객에 대해 이런 작업을 해야 한다면 이것은 많은 양의 데이터 페이지를 처리하는 엄청난 부하를 가질 것이다. 하지만 고객 이름이 인덱스의 한 부분이라면 어떻게 될까? SQL 서버는 넌클러스터 인덱스에 최고 16개의 컬럼을 포함 할 수 있으며, 결국 인덱스 리프 레벨에 16개 컬럼을 포함 시킬 수 있다. 만약 쿼리하는 모든 데이터가 인덱스의 부분이라면 SQL 서버는 데이터 페이지를 가져와야 할 필요가 없으며 쿼리는 매우 빨라진다. 인덱스만 액세스해서 원하는 모든 데이터를 가져올 수 있는 쿼리를 커버된 쿼리 라고 하며 쿼리에 포함된 모든 컬럼을 가진 인덱스를 커버하는 인덱스라고 한다.
생각하는 것 보다 더 많은 커버를 하는 인덱스를 가질 수 있다. 테이블이 클러스터 인덱스를 이미 가지고 있다면 모든 넌클러스터 인덱스의 리프레벨을 위해 클러스터 인덱스가 책갈피로 사용됨을 기억하자. 즉, 모든 넌클러스터 인덱스는 항상 부수적인 키를 가진 셈이 된다. 만약 테이블의 lastname 과 firstname에 넌클러스터 인덱스가 있고, employeeID에 클러스터 인덱스가 있다면 모든 넌클러스터 인덱스의 리프 레벨 행은 이 세 개의 컬럼 값을 모두 가지고 있다. 이 넌클러스터 인덱스는 firstname, lastname, employeeID 만을 찾는 어떤 쿼리에 대해서도 커버한다.

힌트에 대해 바로 알자

TIP쿼리에 힌트 사용을 피하도록 하자. 어떤 상황에서 테스트 한 결과, 힌트가 필요하다고 하면, 주기적으로 재 테스트 하도록 계획을 세우자.
비록 SQL 서버 2000 이 30개 이상의 최적화기가 특정행동을 하도록 강제화 시키는 최적화 힌트를 제공하긴 하지만, 대부분의 공식적인 튜닝 가이드는 이 힌트를 쓰지 말라고 권장하고 있다. 대부분 경우에 필자는 이 제안에 동의 한다. 최적화기는 SQL 서버 쿼리 엔진에서 가장 복잡한 부분이다. 힌트를 사용함으로써 SQL 서버가 이런 복잡한 코드의 일부분을 수행하는 것을 방지하는 이점을 얻을 수 있다. 특정 경우에는 그 당시 그 쿼리를 위해서는 적절한 방법일 수 있다. 그러나 데이터가 변하거나, 최적화기가 향상된 기능을 가진 새로운 서비스 팩을 적용하면 그것은 더 이상 유용하지 않을 수 있다. 실제 서버에서 쿼리 힌트를 사용했다면 SQL 서버는 데이터 분포가 변한 경우에도 항상 그 쿼리를 지정된 방법으로만 수행한다. 즉, 힌트는 더 이상 최적의 방법을 제공하지 못함에도 불구하고, 지정된 방법으로만 수행되는 것이다.
필자는 기존의 자유롭게 최적화기 힌트를 사용하고 있는 응용 프로그램을 튜닝 하는 무수한 작업을 해왔다. 처음에는 유용했을 수도 있는 쿼리 힌트가 수 개월, 수 년이 지난 후에는 오히려 대부분 필요없거나 오히려 해가 된다고 늘 생각했고 이들 힌트를 성능 저하의 범인으로 의심해왔다. 그래서 이런 경우에 어떻게 할 것인가? 실제 사용하는 모든 코드를 뒤져서 이들 힌트를 제거하고, 성능 테스트를 할 것인가? 다행히도 SQL 서버는 더 쉽고 완벽한 방법을 제공한다. 세 개의 문서화 되지 않은 추적 플래그를 사용할 수 있는데, 이들은 SQL 서버의 시작 매개변수로 사용할 수 있으며 SQL 서버로 하여금 특정 종류의 힌트를 무시하도록 할 수 있다. 추적 플래그 8602 는 SQL 서버가 모든 인덱스 힌트를 무시하게 한다. 추적 플래그 8722 는 SQL 서버로 하여금 모든 쿼리 힌트 (OPTION 절에서 사용하는)를 무시하게 하며 추적 플래그 8755 는 SQL 서버로 하여금 모든 잠금 힌트를 무시하게 한다.
이 세 추적 플래그와 함께 응용 프로그램을 수행하면 이들 힌트를 제거했을 때 얼마나 이점을 얻을 수 있는지 알아낼 수 있다. 이것을 제거하는 것이 더 잇점이 있다고 판단했으면 실제로 힌트를 제거하고 다시 성능을 테스트해 볼 계획을 세우면 된다. 어떤 힌트들은 여전히 유용할 수 있지만 이 추적 플래그를 사용하면 기존의 모든 힌트를 가진 것보다 아무 힌트도 가지지 않은 것이 더 낫다는 것을 알려줄 것이다.

트랜잭션을 엉키게 하지 말자

TIP @@trancount 를 트랜잭션 시작하기 전에 점검하여 SQL Server 가 불필요한 잠금을 갖지 않도록 하자.
필자는 잠금과 트랜잭션의 관계에 관한 몇 개의 기사를 썼다. 필자는 항상 SQL 서버가 트랜잭션이 commit되거나 rollback되기 전에는 결코 배타 잠금을 풀지 않음을 목격했다. 대부분 사람들은 이런 관계를 알고 있지만 많은 사람들은 트랜잭션을 중첩 시켰을 때 어떤 일이 벌어지는지 알고 있지 못하다. 논리적으로 SQL 서버안에서는 어떤 트랜잭션도 중첩되는 것이 아니다. 한 컨넥션을 위해서 최대 하나의 트랜잭션만 가질 수 있다. T-SQL 코드가 여러 개의 BEGIN TRANSACTION 문장을 수행하면 문법적으로는 중첩된 트랜잭션을 갖게 된다. 다른 트랜잭션이 활성화 된 상태에서, 두 번째 BEGIN TRANSACTION 문장을 수행하면 SQL 서버는 내부적인 카운터만 증가시킨다. 이것이 바로 @@trancount 함수가 보여주는 값이다.
COMMIT TRANSACTION을 수행하면 SQL 서버는 @@trancount의 값을 감소시킨다. 이 값이 0 이 되었을 때만 SQL 서버는 활성화 된 트랜잭션을 commit 하고 모든 잠금을 풀게 된다. 그래서 활성화 된 트랜잭션을 commit하기 위해서는 정확하게 COMMIT TRANSACTION 문장을 BEGIN TRANSACTION 문장을 수행한 만큼 수행해야 한다. 그러나 단 하나의 ROLLBACK TRANSACTION 문장은 @@trancount 값을 0으로 리셋하며, 그래서 SQL 서버는 즉시 활성 트랜잭션을 rollback 하며 모든 잠금은 풀려진다.
특정 상황에서 트랜잭션이 롤백 되었다고 생각할 수 있지만 아닌 경우가 있다. 많은 오류들이 오류 메시지를 돌려주고 트랜잭션의 취소 없이 현재 문장을 취소한다. 필자는 응용 프로그램 개발자가 오류가 났음에도 불구하고 @@trancount 를 검사하지 않은 채, 새로운 BEGIN TRANSACTION 문장으로 트랜잭션을 다시 시작하는 것을 보아왔다. 그리고 COMMIT TRANSACTION 이 실행되면 개발자는 생각하기를 트랜잭션이 커밋되었다고 생각하지만 트랜잭션은 여전히 활성화 되어있고 잠금은 풀리지 않는다.
사용자나 프로세스가 SQL 서버에 CANCEL 명령을 보냈을 때도 이런 비슷한 상황에 처하게 된다. CANCEL 명령은 현재 수행 중인 배치를 취소하는 명령이지만 트랜잭션을 롤백하지는 않는다. 그래서 현재의 배치를 취소한 후에 새로운 BEGIN TRANSACTION 을 수행하면 빠져 나오기 위해서는 여러 개의 COMMIT TRANSACTION 명령이 필요한 문법적으로 중첩된 트랜잭션에 처하게 되는 것이다.
프로세스가 중첩된 트랜잭션에 엉켜있는지 아닌지를 판단하는 한가지 방법은 sysprocesses 테이블을 조회해보는 것이다. sysprocesses 테이블에 open_tran이란 컬럼이 있는데, 이 값이1 이상인 것을 찾으면 된다. 만약 프로세스가 계속적으로 1 이상의 값을 가지고 있다면, 이 프로세스가 가지고 있는 잠금을 조사하고 왜 프로세스가 중첩 트랜잭션에 빠졌는지 조사해 보아야 한다.

Blackbox 를 설정한다

TIP blackbox 가 수행되게 하고, 필요할 때 그것이 있다는 것을 잊지말자.
blackbox는 SQL 서버에 보내진 마지막 5MB 분량의 T-SQL 문장을 가진 특별한 추적 파일이다. 이것은 진정한 롤오버(rollover) 파일인데 5MB 의 분량에 이르면 자기 자신을 덮어쓴다. 2001년 2월호 "프로필러의 블랙박스 기능" 기사에서 필자는 이 추적 파일을 설정하고 관리하는 완전한 지침을 제공했으며 SQL 서버가 시작될 때 마다 저장 프로시저를 이용하여 이를 자동 수행하는 방법에 대해 다루었다.
blackbox 로 인한 오버헤드는 매우 작으며 그래서 필자는 늘 이것이 수행될 것을 권장한다. 설명할 수 없는 동작을 보거나, SQL 서버가 명확하지 않은 이유로 갑자기 죽는다면 이 블랙박스 기록을 조사하는 엄청난 이점을 얻을 수 있다.

성능 모니터를 개인화 하자
TIP user-defined counters 를 Performance Monitor에 포함시키므로써, SQL 서버 데이터나 메타 데이터로부터 가져올 수 있는 어떤 숫자 값이던 모니터 할 수 있다.
고객 중 하나는 과도한 블로킹 문제를 가지고 있었으며, 그래서 성능 모니터를 설정하여 SQL 서버 시스템의 잠금 수를 추적하고 싶어했다. 하지만, 이 정보는 약간의 도움만 되었다 왜냐하면 대부분의 잠금은 일시적이며 그 중 일부만이 나쁜 것이지 모든 잠금이 나쁜 것은 아니기 때문이다. 잠금은 SQL 서버가 적절한 방법으로 풀지 않아서 잠금을 얻으려는 다른 프로세스를 방해할 때만 나쁜 것이다.
필자는 다른 프로세스를 차단 하고 있는 프로세스의 개수를 추적하도록 제안했다. "Track Down Troublemakers" 기사에서 필자는 자신이 차단 당하지 않고 다른 프로세스를 차단하고 있는 프로세스를 보여주는 스크립트를 제공했다. 이런 프로세스는 잠금 체인의 선두(heads of lock chains)라고 불린다. 잠금 체인의 선두 프로세스를 모니터하는 것은 유용하지만 성능 모니터는 기본적으로 이를 모니터 할 방법을 제공하지 않는다. 하지만 SQL 서버는 10개의 사용자 정의 카운터를 제공하는데 이를 성능 모니터에서 모니터 할 수 있다. SQL Server: User Settable 개체를 선택하고 10개 인스턴스 중 하나를 선택하면 된다. 예를 들어, User Counter 1에 시스템 저장 프로시저 sp_user_counter1 을 사용하여 이 카운터의 값을 설정할 수 있다. (SQL 서버는 다른 카운터에 대해 비슷한 이름의 저장 프로시저를 가지고 있다). 이 프로시저를 호출하고, 숫자 값을 전달하면 성능 모니터는 전달받은 이 값을 보여준다. 나는 [리스트 1] 에서 보여주는 루프를 작성했는데 이것은 User Counter 1의 값에 매 5초마다 잠금 체인의 선두 값을 전달한다.
SQL 서버의 내부 동작을 이해하는 것은 작업을 단순히 하거나 더 나은 성능을 가져다 준다. 찾는 자만이 이런 팁을 얻을 수 있다. 여기 마지막 팁이 있다. SQL 서버 매거진 은 매달 이런 팁을 찾을 수 있는 놀라운 곳이다.

[리스트 1] 현재 잠금 체인의 선두 개수를 User Counter 1 값에 넣는 루프
WHILE 1 = 1 BEGIN DECLARE @numheads int SELECT @numheads = count(*) FROM master..sysprocesses WHERE ((blocked = 0 AND spid IN (SELECT blocked FROM master..sysprocesses))) EXEC sp_user_counter1 @numheads WAITFOR DELAY '0:0:05' END

출처: Windows & .NET [2004년 4월호]

세션빈에서의 트랜잭션 관리

김주현 (ERP 개발자)  
2004/03/30        

① 세션빈에서의 DB 접근전략 및 엔터티빈 사용시 주의사항
② 세션빈에서의 트랜잭션 관리
③ 비즈니스 프로세스 구현 최적화하기
④ 능률 높여주는 유틸리티를 사용하자

지난 강좌에서는 세션빈 내에서 DB를 핸들링하는 데에는 JDBC 드라이버를 이용한 Connection 객체를 사용하는 방법과, 엔터티빈을 이용하는 방법이 있다고 언급했다. 중요한 점은 두가지 방법을 혼용하는 것이 아니라 엔터티빈 사용시 발생하게 되는 문제점을 미리 알고 엔터티빈 사용을 지양하자는 것이다.

지난번 강좌에 이어 엔터티빈 사용을 지양할 수 밖에 없는 사례 한 가지를 더 예로 들겠다.

통상 SI 프로젝트에서 인사시스템과 같이 개인의 정보보안이 엄격히 다루어져야 하는 성격의 시스템인 경우, 업무 전반 모두를 웹기반으로 구현하지는 않는다. 물론 웹으로 구현하는 것과 보안과는 별개의 문제이지만, 아직도 공기업 등에서는 웹기반 구현을 꺼리는 경우가 많기 때문이다.

급여계산 및 업무평가점수계산 등 민감한 성격의 정보를 다루는 프로그램은
클라이언트/서버(C/S) 환경으로 구축돼 업무 담당자만 사용 할 수 있도록 하고, 웹으로는 일반사원들이 자신의 정보를 확인하는 조회성 프로그램만으로 구성되도록 요청하는 사례가 많다.

조회성 기능만 갖고 있는 웹기반 프로그램을 설계한다면, 설계자는 DB의 레코드를 SELECT하고 화면에 보여주는 기능만을 수행하므로 쿼리보다는 엔터티빈을 활용하겠다는 기준을 세우고, 실제 개발도 엔터티빈을 이용해 진행하게 된다.

C/S 환경 시스템과 웹프로그램은 서로 업무 범위 및 구축된 환경만 다를 뿐이지 물리적으로 같은 DB를 사용하고 있다. 이러한 환경에서 간혹 발생하는(주로 동시사용자수가 증가할 때 발생한다) EJB 컨테이너의 ‘NoSuchObjectException’ 장애물을 알아둬야 한다.

C/S 프로그램으로 업무를 하는 담당자와 일반 사용자가 같은 레코드를 조회한 상태에서, 담당자가 해당 레코드를 수정 혹은 삭제했고, 웹의 클라이언트 모듈이 해당 레코드를 표현하는 엔터티빈을 계속 참조하고 있는 상황이라면 NoSuchObjectException을 보게 될 것이다.

이 Exception을 던지는 일은 EJB 컨테이너의 기능 중 하나로 엔터티빈의 객체정보와 실제 DB의 정보가 일치하지 않을 경우 동작하게 된다. EJB 컨테이너는 실제 DB와 엔터티빈 객체를 동기화하고 있기 때문에 다른 세션이 자신이 참조하고 있는 DB의 레코드를 변경했을 경우 Exception을 던지는 것이다.

이 Exception이 발생하면 이를 해결하는 방법은 컨테이너가 참조하고 있는 엔터티빈을 핫디플로이 시켜서 객체를 다시 참조케 하거나, 핫디플로이 방법을 쓸 수 없다면 EJB 서버를 RESTART하는 수 밖에 없다.

컨테이너가 NosuchObjectException을 던질 경우 자동으로 엔터티빈을 Passivate시키고 Activate시키면 편리하겠지만 아쉽게도 그런 기능은 없다. 개발자가 향후에 NosuchObjectException이 생기지 않도록 프로그램마다 빈의 라이프싸이클을 제어하는 부분을 추가하는 방안도 생각해 볼 수 있겠지만 그 작업은 시간 소모가 크다.

해당 엔터티빈의 실행 중 정보를 갖고 있는 XML 데이터의 값 중에서 이러한 에러를 일으키지 않도록 해주는 속성이 무엇인지 안다면, XML 데이터의 값을 고치고 다시 디플로이 하는 방법도 있겠다.

하지만 개발자 입장에서는 본인의 판단 착오와는 전혀 관련이 없는 에러를 맞닥뜨리게 된 것이며, 이미 에러를 발견한 이후에 작업을 하는 것이므로 이미 사용자의 프로그램에 대한 신뢰도가 떨어져있는 상황이겠다. 또한 시스템 내의 모든 엔터티빈을 찾아내 작업을 하기도 꽤 번거로운 일일 것이다.

컨테이너의 동기화 기능이 유발시키는 NoSuchObjectException과 엔터티빈과 매핑되는 테이블이 컬럼이 추가되는 등의 변경 사항이 있을 시 엔터티빈의 소스를 고쳐 다시 디플로이 해야 한다는 문제점으로 인해, 항상 시간에 쫓기는 프로젝트 개발자 입장에서는 엔터티빈의 스펙때문에 자신의 보폭이 좁아지는 느낌을 받을 것이다
.
그리하여, 기존 시스템은 엔터티빈을 이용하되 추가 프로젝트에서는 엔터티빈을 전혀 사용하지 않고, DB핸들링은 100% SQL문으로 처리하고 있는 싸이트도 생기게 된다.

엔터티빈을 쓰지 않으면 DB의 테이블마다 SELECT, DELETE, INSERT, UPDATE 등의 쿼리를 일일이 개발자가 생산해야 하지 않느냐는 의견도 나올 수 있으나 프로젝트 프로토타이핑 단계시점 등에 테이블당 쿼리문을 자동으로 생성시켜주는 간단한 유틸 프로그램을 직접 만들어 사용한다면 생산성이 비약적으로 높아질 수 있다.(4회 강의에서 이 유틸을 소개할 예정이다.)

엔터티빈 사용시 발생하게 되는 문제점에 대한 이야기는 여기서 마무리하고, 초점을 세션빈으로 돌리자. 세션빈 내에서 비즈니스 처리를 할 때 유의해야 할 점이 무엇인지 알아본다.

비즈니스 프로세스와 트랜잭션
하나의 비즈니스 프로세스가 처리될 때 가장 중요하게 지켜져야 할 사항은
트랜잭션이 결함없이 처리되어야 한다는 점이다. 예를 들어 보험금 수납시에 보험담당자의 실적이 체크됨과 동시에, 회계장부에 보험금이 기록되어야 하는 [보험금수납]이란 이름의 업무정의가 있을 경우, 이 업무가 성공적으로 완료되었다고 판단할 수 있는 것은 단 2가지 외에는 없다.

실적도 체크되지 않고 장부에도 기록되지 않은 경우와, 실적 체크와 장부 기록 둘 다 성공한 경우다. 이 외 둘 중 하나만 성공한 경우는 트랜잭션이 실패한 것이다. 시스템 운영상 결코 발생해서는 안되는 치명적인 결함이다.

세션빈이 수행하는 메소드안에서 반드시 트랜잭션이 성공해야 함은 아무리 강조해도 지나치치 않을 것이다.


EJB 컨테이너와 트랜잭션
EJB 개발자들은 세션빈의 메소드 단위로 트랜잭션 레벨을 설정할 수 있다는 것을 알고 있다. 또한 컨테이너가 개발자가 설정한 트랜잭션의 레벨에 따라 메소드 내의 트랜잭션을 하나로 묶어준다는 것도 알고 있다.

EJB의 실행중 속성정보를 갖고 있는 XML 파일안을 조작함으로써 트랜잭션 관리가 필요한 메소드를 지정할 수 있고(기본값은 ALL이다), 레벨 또한 조작할 수 있다.
설정 레벨은 REQUIRED로 지정한다(트랜잭션을 관리할 필요가 없는 업무정의가 존재하는 경우는 없다고 봐도 좋다).

컨테이너는 이 두 가지 정보를 가지고 메소드 시작시점부터 완료시점까지 Exception이 발생하게 되면 레벨이 REQUIRED일 경우 Exception발생 전까지 수행했던 작업을 모두 롤백시킨다.

설정레벨을 하위로 조정하면 Exception을 던지되 롤백시키지는 않는 현상이 벌어진다. 그러므로 이 XML의 기본값을 고치는 경우는 없고, 고쳐서도 안된다. VisualCafe와 같이 자동으로 EJB의 XML을 생성시켜주는 툴에서부터 모두 이 옵션을 기본적으로 REQUIRED로 설정하고 있다.

이렇게 트랜잭션을 컨테이너가 메소드 단위로 관리하므로 개발자 입장에서는 명시적으로 트랜잭션을 ON/OFF 하는 기능을 구현할 필요 없이 일정부분 태스크가 줄어든 상태로 볼 수 있다.

하지만 메소드 내에서 Connection을 씀에 있어 언제 Connection을 얻어오고 반환해야 할 지, 또는 Connection 객체를 몇 개 선언해 생성시켜 쓸 것인지와 같은 고민들은 컨테이너의 기능과 별개로 전체 프로젝트에 일관성 있게 적용되어야 할 표준으로 정해져야 하며, 이는 설계자와 개발자의 몫이다.

Connection 객체 핸들링을 하는 기준
Conection 객체를 어느 클래스가 생성하며 반환할 것인가, DB 래퍼 클래스의 메소드와 통신할시 Connection 객체를 넘길 것인가 말 것인가, 넘긴다면 어떤 방법으로 넘기는가에 대한 기준을 세워야 한다.

세션빈 Connection을 선언하고 생성시켜 얻어오는 주체는 1회 강의에도 말했듯이 세션빈 클래스이다. 세션빈 클래스 안에서 Connection 객체는 전역변수로 선언하지 말아야 한다.

각각의 메소드마다 지역변수로 선언하여 사용하는 것이 효과적이다. 또한 메소드안의 프로세스가 진행 완료됐을 경우, finally 절에서 반드시 Connection을 반환해야 하는 것도 중요한 원칙 중 하나다. 커넥션을 얻어올 때, SQLException을 던져야 하므로 try, catch문을 사용하게 되는데, finally 절에서 반드시 커넥션을 반환하도록 하자.

생성된 Connection 객체는 DB와의 작업을 모두 한 이후 반드시 close 메소드를 통해 닫혀져야 하는데, Connection 객체가 반환되지 않는 경우 심각한 시스템 자원낭비를 유발하게 된다. 이는 곧 프로그램의 운용성능 저하로 이어진다.

Connection 객체생성은 언제나 close() 메소드와 쌍을 이루어야 한다는 식의 사고습관을 가져야 하겠다.

다음으로 언급할 것이 DB 래퍼 클래스와의 통신 패턴이다. DB 래퍼 클래스는 DB에 SELECT, INSERT, DELETE, UPDATE 쿼리를 전달하는 역할을 하는 메소드 집합체로써, DB와 가장 가까운 층이다.

세션빈 메소드는 복잡한 비즈니스 프로세스를 구현하게 되는데, 메소드 시작 부터 끝까지 Connection 객체를 통한 쿼리실행이 모두 그 안에 나열돼 있다면 유지보수 하는 사람 뿐 아니라 개발하는 당사자조차도 가독하기 힘들다.

따라서 DB 래퍼 클래스를 만들어서 쿼리실행에 필요한 인자(사용자가 입력한 조회조건 또는 사용자가 입력한 정보)를 세션빈이 전달하고, 실행 결과를 서로 주고 받는 패턴을 사용한다.

이처럼 DB래퍼 클래스를 통해 레코드를 조작하게 될 때, 세션빈이 생성한 Connection 객체를 DB 래퍼 클래스의 메소드에 콜할 때 인자로 함께 넘겨야한다.

세션빈이 Connection을 넘기지 않고 DB래퍼 클래스에게 Conenction을 핸들링하는 것을 맡길 경우, 5개의 DB래퍼 클래스 메소드를 호출하면 Connection 생성을 5번 하고, close() 명령을 통한 반환도 5번 일어나게 되는 셈이다. 다분히 비효율적이다.

이에 비해 DB래퍼가 자신을 호출한 클래스로부터 항상 Connection을 받아서 쓴다면 필요없는 자원낭비를 막을 수 있다.

이와 같은 원칙은 전체 시스템 구조에서 통일성 있게 적용돼야 할 것이다. 통일성 있게 적용되지 않으면 이런 원칙을 세우는 의미가 없다. 어떤 서브 패키지는 DB래퍼 클래스를 생성할 때 생성자에서 Connection을 받는다던가,
또는 아예 DB래퍼 클래스로 하여금 Connection을 관장하도록 한다던가 하면 전체 설계구조에 통일성이 없고 이를 바탕으로 한 추가 개발에 있어서도 비효율이 따르게 된다. 일관성 없는 구조의 클래스들을 재활용하는 신규 모듈도 역시 흐름에 일관성이 없을 것이다.

이제까지 언급했던 원칙을 패턴으로 사용하는 샘플소스를 보도록 하자.

아래 메소드는 사용자가 입력한 전표 내용으로 전표 등록을 하는 메소드이다. 이 세션빈 이름은 SlipManager이다.
public class SlipManagerEJB implements javax.ejb.SessionBean{  /* SlipInfo 객체는 전표 내용을 담고 클래스간에 통신할 수 있게 하는 구조체성격의 클래스임 */  public int regSlip(SlipInfo[] sInfos) throws Exception{  {    Connection conn = null;    int maxSlipNo = 0;    /* DBWrapper 클래스 선언 및 생성 */    DBWrapper db = new DBWrapper();    try{      conn = DBManager.getConnection();      /* 마감여부체크한다 */      db.checkClosingState(conn, ...);      /* 전표번호 MAX값을 받아온다*/      maxSlipNo = db.getMaxSlipNo(conn, ...);      /* 전표 TABLE에 인서트를 한다.*/      for( int i=0 ; i < sInfos.length; i++){        sInfos.setSlipNo( maxSlipNo);        db.insertSlip( conn, sInfos[i]);      }          }    catch(Exception e){      throw new Exception(e.getMessage());    }    finally{      try{        DBManager.closeConnection(conn);      }catch(Exception se){        throw new Exception(se.getMessage());      }    }    return maxSlipNo;  }  ... ( 이하 생략 ) }   public class SlipManagerEJB implements javax.ejb.SessionBean{  /* SlipInfo 객체는 전표 내용을 담고 클래스간에 통신할 수 있게 하는 구조체성격의 클래스임 */  public int regSlip(SlipInfo[] sInfos) throws Exception{  {    Connection conn = null;    int maxSlipNo = 0;    /* DBWrapper 클래스 선언 및 생성 */    DBWrapper db = new DBWrapper();    try{      conn = DBManager.getConnection();      /* 마감여부체크한다 */      db.checkClosingState(conn, ...);      /* 전표번호 MAX값을 받아온다*/      maxSlipNo = db.getMaxSlipNo(conn, ...);      /* 전표 TABLE에 인서트를 한다.*/      for( int i=0 ; i < sInfos.length; i++){        sInfos.setSlipNo( maxSlipNo);        db.insertSlip( conn, sInfos[i]);      }          }    catch(Exception e){      throw new Exception(e.getMessage());    }    finally{      try{        DBManager.closeConnection(conn);      }catch(Exception se){        throw new Exception(se.getMessage());      }    }    return maxSlipNo;  }  ... ( 이하 생략 ) }


전표를 등록하는 비즈니스 프로세스는 사용자가 입력한 전표정보를 받아서 마감여부를 체크한 후, 전표 번호 MAX 값을 알아내고 전표 TABLE에 INSERT 하는 작업으로 요약된다. 물론 실제 업무는 이보다 훨씬 더 복잡한 경우의 수를 가지고 있다. 법인카드사용정보가 있는 경우 법인카드 사용정보에도 등록을 시켜야 하고, 예산에서 지출되는 전표일 경우 예산 정보에도 그 내역을 기록해야 하는 등 다양한 업무 정의를 포함하고 있다.

소스를 보면 알겠지만 위에서 정한 원칙대로 Connectin 객체는 지역변수로 선언하여 사용하고 있으며, DB래퍼 클래스의 메소드에 Connection 객체를 넘기고 있다.

메소드 오버로딩(OVERLOADING)
이제 다른 시스템의 업무가 이 메소드를 사용해야 하는 업무 요구사항이 생길 경우에 대해 살펴보겠다. 인사시스템에 복리후생비 신청정보가 결재자의 승인을 받으면 자동으로 전표가 발행되어야 하는 요구사항이 나왔다 치자.

프로그램 설계자는 전표등록이라는 비즈니스 프로세스를 구현한 메소드가 SlipManager라는 세션빈에 이미 있다는 것을 알고 있으므로, 이 부분은 따로 전표 등록 메소드를 만들지 않고 기존 것을 재사용하기로 한다.

아래소스는 이처럼 위의 세션빈을 전표등록 메소드를 호출하는 복리후생비 신청정보 승인 프로세스를 보여주고 있다.

소스보기
 
 /* SubmitInfo 객체는 복리후생비 신청정보 */  public boolean approveWelfare(SubmitInfo info) throws Exception{  {    Connection conn = null;    int maxSlipNo = 0;    boolean success = false;    /* DBWrapper 클래스 선언 및 생성 */    DBWrapper db = new DBWrapper();    try{      conn = DBManager.getConnection();      /* 현재 신청상태임을 확인한다. */      db.checkState(conn, ...);      /* 개인별 쓸 수 있는 복리후생비 금액을 조회해서 초과하면 경고메세지로 알림 */      success = db.checkWonAmt(conn, ...);      if( success == false){        Exception e = new Exception();        e.setMessage(" 초과 ... ");        throw e;      }      /*        전표를 등록하기 위해 전표 등록 세션빈의 리모트 객체를        Home의 create 메소드를 이용 , 생성시킨다      */      SlipManagerHome slipMgrHome = (SlipManagerHome)HomeFactory.getHome(SlipManager.class);      SlipManager slipMgr = slipMgrHome.create();      maxSlipNo = slipMgr.regSlip( info.getSlipInfos());      if( maxSlipNo > 0 ) success = true;    }    catch(Exception e){      throw new Exception(e.getMessage());    }    finally{      try{        DBManager.closeConnection(conn);      }catch(Exception se){        throw new Exception(se.getMessage());      }    }    return success;  }


위 소스를 보면 전표등록 메소드를 재사용 하고 있으나 한가지 우려되는 점이 있다. 바로 세션빈의 메소드를 콜 하는 것 까지는 문제가 없으나, 전표 등록 호출시 세션빈에게는 Connection 객체를 던지지 않으므로 호출되는 세션빈이 Connection을 따로 생성시켜 작업을 하고 있다.

위와 같은 업무에서는 세션빈의 메소드를 한번 호출하고 있지만 만약 사용자가 업무 복리후생비전표를 100장 생성시켜야 하는 경우라면 아래와 같은 for loop 문으로 콜 하게 될 것이다.

소스보기
 
 SlipInfo[] infos = info.getSlipInfos();  for( int i=0; i < infos.length ; i++){    slipMgr.regSlip( infos[i]);  }



결국 Connection은 100개가 생성되고, 100개가 반환되는 작업이 일어난다. 역시나 시스템 자원운용 면에서 보면 상당한 문제점을 야기시키는 부분일 것이다.

따라서 이와같이 타 세션빈에서 호출하는 메소드일 경우, DB 래퍼와 통신시에 Connection 객체를 넘겼듯 세션빈 메소드와 통신시에 Connection을 넘겨서 사용하는 패턴으로 가야 할 것이다.

위의 전표생성메소드를 Connection을 받아서 작업하는 메소드로 고쳐서 설계해보자. 물론 이 Connection을 생성시키고 닫아주는 주체는 전표생성 메소드를 콜한 세션빈이 하게 된다. 그렇다면 아래 소스처럼 regSlip 메소드를 오버로딩 한 메소드를 추가로 개발한다.

소스보기
 
 public int regSlip(Connection conn , SlipInfo[] sInfos) throws Exception{  {    int maxSlipNo = 0;        boolean success = false;    DBWrapper db = new DBWrapper();    try{      db.checkClosingState(conn, ...);      maxSlipNo = db.getMaxSlipNo(conn, ...);      for( int i=0 ; i < sInfos.length; i++){        sInfos.setSlipNo( maxSlipNo);        db.insertSlip( conn, sInfos[i]);      }    }    catch(Exception e){      throw new Exception(e.getMessage());    }    return maxSlipNo;  }


이러한 메소드가 추가되면 먼저 만들어 두었던 regSlip 메소드는 아래와 같이 regSlip(Connection conn , SlipInfo[] sInfos)를 호출하자. 실제 비즈니스 프로세스는 regSlip(Connection conn , SlipInfo[] sInfos)안에 모두 있으므로, 이 메소드를 기존 그대로 놔두면 중복코드가 있게 된다.

소스보기
 
 public int regSlip(SlipInfo[] sInfos) throws Exception{  {    Connection conn = null;    int maxSlipNo = 0;    try{      conn = DBManager.getConnection();      maxSlipNo = regSlip( conn , sInfos );    }    catch(Exception e){      throw new Exception(e.getMessage());    }    finally{      try{        DBManager.closeConnection(conn);      }catch(Exception se){        throw new Exception(se.getMessage());      }    }    return maxSlipNo;  }



이와 같이 세션빈 메소드는 다른 세션빈 메소드가 업무상 호출할 수 있는 경우가 빈번하므로, 세션빈 메소드를 Connection 받는 것과 안 받는 것으로 오버로딩하는 패턴을 사용하게 된다.

세션빈 메소드는 다수 쿼리를 실행시키는 집합체
EJB 기반 프로젝트의 가장 큰 영역은 세션빈이다. 세션빈 내에서 모든 비즈니스 프로세스가 구현되므로, 세션빈 안에서 기타 클래스들을 어떠한 패턴으로 사용할 지 고민하며 설계하고 구현해야 재사용성이 높고 강력한 모듈을 만들 수 있다.

다음 강의에서는 실제 업무에서 일어나는 복잡한 프로세스를 구현함에 있어서 DBMS의 펑션이나 프로시져를 사용하는 것의 장점을 살펴보도록 하겠다.

2회까지는 세션빈 사용에 있어서의 클래스 패턴과, 시스템 성능 측면에서 어떤 점을 피해가야 하는지가 주된 논점이었다. 실제 프로젝트에서는 이 정도로 대부분의 이슈가 커버되겠지만, 막상 구현 결과물을 보면 세션빈 메소드가 수많은 쿼리를 실행시키는 집합체로 보인다.

그렇다면 이것을 단 한 번의 실행으로 끝내버리는 방법은 없을까. 다음 강의에서 그 방법에 대해 논의해보자. @  


http://www.zdnet.co.kr/techupdate/lectu ··· 2C00.htm