tracker issue : CF-4062006

Problem Description:
sp_reset_connection is not being called after each CfQuery tag, therefore if Maintain Connections is enabled in the CFIDE, the following items will leak between CFQuery's across multiple requests.
1. Temp Tables,
2. Transactions.
3. SET TRANSACTION ISOLATION LEVEL statements
4. @@error info
5. @@rowcount

This means that temp tables created in 1 CfQuery generate an exception in subsequent CFQueries if they are not explicitly dropped.  Other implementations of connection pooling such as in DotNet call sp_reset_connection which means that these objects to not leak.

Perhaps the most dangerous set option is SET TRANSACTION ISOLATION LEVEL, where set is one query will make all subsequent queries use that option.

Here is some more info on how to call sp_reset_connection via a flag on the TDS packets.  

http://www.sqlskills.com/blogs/bobb/sql-server-and-pooled-vs-non-pooled-connections/


Steps to Reproduce:

Run the following code multiple times with Maintain Connections enabled

	DECLARE @StartingIsolationLevel varchar(100),
				@startingTranCount int = @@trancount,
				@OrigRowCount int = @@rowcount
				

		SELECT @StartingIsolationLevel = CASE transaction_isolation_level 
		WHEN 0 THEN 'Unspecified' 
		WHEN 1 THEN 'ReadUncommitted' 
		WHEN 2 THEN 'ReadCommitted' 
		WHEN 3 THEN 'Repeatable' 
		WHEN 4 THEN 'Serializable' 
		WHEN 5 THEN 'Snapshot' END 
		FROM sys.dm_exec_sessions 
		where session_id = @@SPID
		
		SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
			BEGIN TRANSACTION
				BEGIN TRANSACTION
		
		DECLARE @EndingIsolationLevel varchar(100)
		
		SELECT @EndingIsolationLevel = CASE transaction_isolation_level 
		WHEN 0 THEN 'Unspecified' 
		WHEN 1 THEN 'ReadUncommitted' 
		WHEN 2 THEN 'ReadCommitted' 
		WHEN 3 THEN 'Repeatable' 
		WHEN 4 THEN 'Serializable' 
		WHEN 5 THEN 'Snapshot' END
		FROM sys.dm_exec_sessions 
		where session_id = @@SPID
		
		SELECT @StartingIsolationLevel as StartingIsolationLevel, @EndingIsolationLevel as EndingIsolationLevel, @startingTranCount as StartingTranCount, @@trancount as CurrentTranCount, @@spid as Spid, @OrigRowCount as OrigRowCount
		
	</cfquery>	
	
	<cfquery name="qry2" datasource="JO3_UNITTEST">
		SELECT @@trancount as Q2tranCount,  @@SPID as spid, @@rowCount as Q2rowCount
	</cfquery>	

Actual Result:
OrigRowCount=1
StartingTranCount=Increment by 1 for each execution
StartingIsolationLevel='Repeatable'

Expected Result:
OrigRowCount=0
StartingTranCount=0
StartingIsolationLevel='ReadComitted'

Any Workarounds:
None - we cannot call sp_reset_connection in code. Disabling connection pooling results in poor query performance.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4062006

External Customer Info:
External Company:  
External Customer Name: david ames
External Customer Email:  
External Test Config: My Hardware and Environment details:

SQL Server 2014 database, occurs on CF9.02 & CF11.

Attachments:

Comments: