드디어 Strored Procedure(이하 SP)를 사용하는 프로젝트에 참여하고 있습니다. 아쉽게(?) 실질적으로 SP를 호출하고 결과를 받아서 UI에 보여주는 작업은 아닙니다.
SP로 실질적인 프로젝트를 하지 않아서 전부터 궁금하던 사실이 있었습니다. 'SP를 사용하면 굳이 서버쪽에 Layer가 필요한 것일까?' 란 의문이였습니다. 프로젝트의 규모가 작고 하나의 SP에서 대부분의 로직을 처리할 수 있다면 굳이 필요하지 않아보입니다만 또 하나의 의문은 SP로 작성된 구문 사이의 트랜잭션의 범위에 대한 의문이였습니다. 일반적으로 Biz Layer에서 트랜잭션을 발생시켜 주고 Dac Layer에서 여러 SP를 호출해줄 경우 Biz에서 발생하는 트랜잭션과 SP내의 트랜재션과의 관계와 그 결과에 대한 의문이였습니다.
조금 더 이해를 돕기 위해서 Biz Layer에서는 TransactionScope 클래스를 사용하였다고 가정하고 SP내에서는 ADO.NET 트랜잭션 메소드(BeginTransaction/Commit/Rollback)를 사용하였다는 가정입니다. TransactionScope는 System.Transaction 네임스페이스 하위의 클래스이며 System.Transaction 네임스페이스에서 관리하는 트랜잭션은 항상 LTM(Lightweight Transaction Manager)을 통해서 관리된다고 합니다. 따라서 해당 테스트는 LTM과 ADO.NET 트랜잭션 메소드 사이의 관계에 대해서 설명하자고 하는 글입니다.
LTM에 대해서는 아래의 사이트를 참고하세요.
http://www.simpleisbest.net/articles/996.aspx
그럼 이제 테스트를 해보도록 하겠습니다. (참고 : 테스트 DB는 SQL Server 2008(R2는 아님)입니다.)
DB에 데이터 베이스는 ID(identity), Name를 가지고 있는 테이블입니다.
이제 SP를 DB에 만들어 보겠습니다. SP의 내용은 아래와 같습니다.
CREATE PROCEDURE EFSP
@name nvarchar(Max), @txOutput int OUTPUT
AS
BEGIN TRAN
insert [dbo].[Entity1Set]([Name]) values (@name) select [Id] from [dbo].[Entity1Set] where @@ROWCOUNT > 0 and [Id] = scope_identity()
-- 위의 insert 구문 2개 더 추가
SET @txOutput = @@TRANCOUNT
COMMIT TRAN GO |
Entity Framework에서 해당 SP를 Function으로 Import를 할 수 있습니다. System.Data.SqlClient를 사용하셔도 됩니다. 여기서 @@TRANCOUNT로 발생하는 트랜잭션의 개수를 SP의 output 파라미터에 대입해주는 것을 확인하실 수 있습니다. ADO.NET 트랜잭션 메소드는 SQL Profiler의 SQLTransaction으로 표현이 되는데 SQLTransaction이 해당 테스트와 상관없이 너무나 자주 발생하여 트랜잭션이 발생하는 개수를 파악하기 위한 방법으로 사용되었습니다.
그러면 이제 단순히 SP를 실행해보도록 하겠습니다.
=> 위에 말했던 대로 SP내부에서 사용한 ADO.NET 트랜잭션 메소드는 SQLTransaction으로 표시가 되며 EventSubClass 열로 트랜잭션이 어느 부분부터 시작되며 어느 부분에서 종료가 되는지 아실 수 있습니다. ObjectName를 통해서는 사용자가 발생한 트랜잭션인지 SQL Server내부에서 발생하는 트랜잭션인지를 구분할 수 있습니다.
그러면 이제 TransactionScope 클래스와 함께 SP를 실행해보도록 하겠습니다.
=> TransactionScope를 클래스를 사용하였기 때문에 TM(LTM)이 관여하는 것을 보실 수 있으며 DBM_INIT이라는 내부 Object에 의해서 무엇인지는 정확하게 알 수 없지만 SQLTransaction이 발생한 것도 볼 수 있습니다. 여기서 중요한 것은 바로 sp_reset_connection 함수입니다. 이 함수는 커넥션 풀링이 되었다는 의미입니다. 이상합니다. 하나의 SP만을 실행준 것인데 왜 커넥션 풀링이 필요한 것일까요?
TransactionScope 클래스를 사용한 구문을 보도록 하겠습니다.
using (TransactionScope scope = new TransactionScope()) { // 하나의 SP실행
scope.Complete(); } |
=> 앞에서 말했듯이 System.Transaction 네임스페이스의 트랜잭션은 TM이 관리하였습니다. 단순히 SP만을 실행해줄 경우에는 SQLTransaction에서 commit이 된 이후에 커넥션이 닫혔습니다. 그러나 TransactionScope안에서 SP를 실행할 경우에 SQLTransaction이 commit되기 전에 커넥션이 먼저 닫히게 됩니다. 이것으로 보아 TransactionScope를 사용하게 되면서 ADO.NET 트랜잭션 메소드가 TM이 관리하는 트랜잭션으로 관리된다는 것을 알 수 있습니다. TM이 Commit이 되기 전에 커넥션이 닫혔기 때문에 TM에서는 커넥션 풀링을 통해서 다시 커넥션을 맺고 내부의 SQLTransaction이 발생한 이후에 SQLTransaction이 commit해주며 그 이후에 TM도 Commit이 되고 마지막으로 커넥션을 닫아주는 것입니다.
몇 가지 테스트를 통해서 TransactonScope의 옵션(TransactionScopeOption)에 따라서 ADO.NET 트랜잭션 메소드가 참여하게 된다는 사실을 확인할 수 있었습니다.
이렇게 하여 LTM과 SQLTransaction의 관계에 대해서 대부분(?!) 파악이 되었습니다. 한 가지 의문점은 SP안에서는 트랜잭션의 Scope 범위(TransactionScopeOption)를 지정하는 부분은 없어보이지만 IsolationLevel에 대해서는 지정을 할 수 있습니다. 또한 TransactionScope에서도 IsolationLevel에 대해서 지정할 수 있습니다. 그래서 IsolationLevel에 따른 LTM과 SQLTransaction의 관계가 어떻게 될지도 살짝 궁금해집니다. 하지만 실질적으로 이러한 것을 프로젝트에서 정확하게 알고 사용하는 개발자가 얼마나 될지도 의문이며 IsolationLevel은 트랜잭션 사이의 잠금(lock)에 대한 것이기 때문에 성능보다는 안전성을 중요시하는 우리 나라의 대부분의 프로젝트에서는 ReadCommitted로 사용하는 것이 개발자의 정신건강에 이로워 보입니다. ^^
