Transactions in SP and connection pooling | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transactions in SP and connection pooling

I have a question concerning transactions in stored procedures and OLE DB connection pooling. I have created a stored procedure which basically does the following: BEGIN TRANSACTION INSERT INTO T1
SET @@ERRORNO = @@ERROR IF @@ERRORNO = 0
BEGIN
INSERT INTO T2
SET @@ERRORNO = @@ERROR
END IF @@ERRORNO = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION So my stored procedure assures that either both inserts are commited or rollbacked. If I get an ADO Timeout on the either of the inserts. The SQL Server gets an exception and stops running the stored procedure but the connection has an open transaction e.g. @@TRANCOUNT is 1. I have a general error routine which choices to close the connection after almost all errors. After the close I open a new connection and might get the old one due to connection pooling and the big surprise (for me at least) is that the connection still has a an open transaction e.g. @@TRANCOUNT is still 1? I fear that this behaviour by design, can anyone elaborate on this? I am currently connecting to SQL Server 7.0 but I assume that connecting to SQL Server 2000 will show the same behavior as the problem arise in ADO. I have thought of several ways to circumvent this behavior: 1) Disable OLE DB connection pooling.
2) Execute the statement "IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION" just before closing the connection. Currently I am using circumvention no. 2. That way I still can use the performance gains in connection pooling. I can remark that I have tried to use the RollbackTrans method on the ADO connection. I even tried to first call the BeginTrans and then the RollbackTrans method. But both these tries did not help as ADO according to SQL Profiler choices not to send any statements to the SQL Server (I assume due to an internal state in the ADO connection). Kind regards
Hans

It is better to do your transaction processing in SQL Server, as you have noted you have had some problems with ADO transactions, and so have I. I see no problem with your solution #2, and as far as I’m concerned I would use that solution to keep connection pooling alive.
"How do you expect to beat me when I am forever?"
]]>