Connection not reset after each CFQuery| View in Tracker
Failure Type: Data Corruption
Found In Build/Fixed In Build: CF11_Final /
Priority/Frequency: Critical / All users will encounter
Locale/System: ALL / Windows 8.1 Update 2 x(64)
Vote Count: 6
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.