일반적으로 트랜잭션이 SQL 서버에서 끝나지 (Commit 되지) 않은 경우, 클라이언트에서 Connection이 끊어질 때, SQL Server는 완결되지 않은 트랜잭션을 Rollback하게 된다.

string sql = "BEGIN TRAN INSERT Tab VALUES (10)";
string strConn = "Data Source=.;Initial Catalog=MyDB;Integrated Security=SSPI;Pooling=false";

SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
                        
conn.Close(); // Close시 트랜잭션 롤백

위의 cmd.ExecuteNonQuery() 메서드가 실행되면, SQL Server에 새 Worker Thread가 (따라서 새 SPID가) 생성되고 클라이언트가 보내온 SQL 명령을 실행하게 된다. TSQL문인 BEGIN TRAN 문장이 실행되면, @@trancount가 1 로 증가한다. INSERT 문이 실행되고, COMMIT TRAN을 실행해야 하지만, 이 예제에서는 트랜잭션이 완결되지 않은 것을 보이기 위해 COMMIT을 생략하였다. 이렇게 되면 미완결 트랜잭션은 서버에 남게 되는데, 이는 클라이언트가 연결을 끊을 때 서버에서 자동으로 롤백된다. 즉, 맨마지막의 conn.Close()가 호출되면 SQL Server에서 해당 SPID가 종료되게 된다.

그런데, 이러한 메카니즘은 Connection Pooling이 사용되지 않은 경우에만 적용된다. 위의 예제에서 Connection String을 자세히 보면, Pooling=false로 설정되어 있음을 알 수 있다.

ADO.NET에서 Pooling은 디폴트로 true 설정되어 있어 기본적으로 (즉, Pooling을 별도로 지정하지 않으면) System.Data.SqlClient는 Connection Pooling 메카니즘을 사용하게 된다. 만약 Connection Pooling을 사용하게 되면 위의 트랜잭션 처리는 어떻게 될까? 아래는Connecton Pooling을 사용한 예이다.

string sql1 = "BEGIN TRAN INSERT Tab VALUES (10)";
string sql2 = "INSERT Tab VALUES (99)";
string strConn = "Data Source=.;Initial Catalog=MyDB;Integrated Security=SSPI;";

SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand cmd = new SqlCommand(sql1, conn);
cmd.ExecuteNonQuery();
conn.Close(); //1. Connection Pooling에 리턴

conn = new SqlConnection(strConn);
//2. Connection Pooling에서 동일 Connection 오픈
conn.Open();

SqlCommand cmd2 = new SqlCommand(sql2, conn);

//3. 아래 EXECUTE로 트랜잭션 취소
cmd2.ExecuteNonQuery();

conn.Close();

예제에서 처음 Connection이 생성된 후 BEGIN TRAN INSERT Tab VALUES (10) 을 실행한다. 첫 Connection이 Close() 된 후, 해당 Connection은 Connection Pooling에 리턴된다. 이것은 SQL Server 상에 클라이언트 연결에 상응하는 SPID 가 계속 남 아 있다는 것을 의미하고, Network Layer 상의 연결이 끊어지지 않았다는 것을 의미한다. 이는 SQL 서버에 sp_who2를 사용하여 해당 SPID가 계속 살아있음을 쉽게 확인할 수 있다. 이어 동일한 Connection String을 사용하여 새 SqlConnection 객체를 생성하고 오픈하면, Conneciton Pooling에서 처음에 사용되었던 Conneciton을 가져온다. 하지만 두번째 Connection의 conn.Open()을 실행하는 것만으로는 옛 Connection에 있던 트랜잭션을 취소하지는 않는다. 이는 SQL Server에서 DBCC OPENTRAN 을 실행하여 이전 SPID가 가지고 있는 트랜잭션이 계속 유지되고 있음을 확인할 수 있다.

DECLARE @dbid INT
 SET @dbid = DB_ID('MyDB')
 DBCC OPENTRAN(@dbid)

-- 결과
Transaction information for database 'MyDB'.

Oldest active transaction:
    SPID (server process ID): 51
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (261:45:9)
    Start time    : Aug 18 2013 12:19:34:727PM
    SID           : 0x010500000000000515000000917f3811633c5e31c23e991fe8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

이 트랜잭션은 옛 Connection에서 잔존하던 트랜잭션으로 이는 새 SQL문이 실행될 때 (cmd2.ExecuteNonQuery()) 롤백된다. 즉, 동일 Connection에 있는 잔존 트랜잭션을 취소하고 새로운 TSQL문을 보내 실행하는 것이다.

만약 cmd2.ExecuteNonQuery()에 Breakpoint를 설정하고, 이 문장이 실행되기 전 SQL 서버에 다른 Connection으로 SELECT * FROM Tab 문장을 실행하면 SELECT 문을 계속 Blocking 상태로 대기하게 된다. 이는 해당 테이블에 대한 Exclusive Lock을 예전 Connection이 가지고 있으므로, 이 테이블에 대한 쿼리가 Lock을 기다리는 상황에서 발생하는 것이다.

위의 예제는 테스트 환경에서 가상으로 개념을 이해하기 위한 것이지만, 실무에서 어떤 의미를 갖는 것일까? 이는 만약 트랜잭션을 갖는 어떠한 TSQL문이 - 그것이 Stored Procedure이든, Function이든, 일련의 DML 문장이든 - ADO.NET을 통해 실 행 중 문제를 발생시킨 경우, 그리하여 해당 트랜잭션이 제대로 COMMIT 되거나 ROLLBACK 되지 않은 경우, 트랜잭션은 Connection Pooling 에서 일정 기간 존속할 수 있다는 것을 의미하며, 따라서 해당 트랜잭션 내의 테이블에 걸린 LOCK이 계속 유지될 수 있다는 것을 의미한다. 그리고 일반적으로 이는 DEADLOCK 혹은 BLOCKING 현상을 야기하는 주범이 될 수 있다. 실제 복잡하게 돌아가는 Production 서버 에서는 마치 언제 터질 지 모르는 시한폭탄이 갖고 있는 것과 같으며, 일반적으로 시스템이 가장 바쁘게 돌아갈때, 연속적인 데드락이나 블럭 깅으로 시스템을 마비시키는 원인이 된다.