tracker issue : CF-4062006

select a category, or use search below
(searches all categories and all time range)

Connection not reset after each CFQuery

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/CannotReproduce

Reporter/Name(from Bugbase): david ames / david ames (david ames)

Created: 09/22/2015

Components: Database

Versions: 11.0

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.
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.

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
		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 name="qry2" datasource="JO3_UNITTEST">
		SELECT @@trancount as Q2tranCount,  @@SPID as spid, @@rowCount as Q2rowCount

Actual Result:
StartingTranCount=Increment by 1 for each execution

Expected Result:

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.



I'm glad somebody else finally found this I thought I was going mad.
Vote by External U.
5769 | September 22, 2015 08:37:21 PM GMT
Well, looks like I'm not the only one facing this problem after all!
Vote by External U.
5770 | September 22, 2015 08:40:13 PM GMT
Glad its not kust me withthis issue
Vote by External U.
5771 | September 22, 2015 08:41:17 PM GMT
Yes, please sort this thing out.
Vote by External U.
5772 | September 22, 2015 08:42:39 PM GMT
I'm having the same issue, hope this gets fixed soon.
Vote by External U.
5773 | September 22, 2015 08:52:58 PM GMT
If you want to set the isolation level, you should use cftransaction. Setting the isolation level inside the query without cftransaction would completely mess up the connection pooling. This is an invalid usage and hence it is not a valid bug.
Comment by Rupesh K.
5763 | September 22, 2015 09:11:47 PM GMT
Rupesh, OK, point taken on isolation level, however what about every other item that is not reset? IE, temp tables, transactions (begin tran/end tran, etc), @@rowcounts, @@error, etc. Bob Beauchemin (from is one of the world authorities on SQL Server & has the following to say about sp_reset_connection "If you consider sp_reset_connection as part of "connection pooling" (it's certainly essential for it to work properly)..." (refer above URL posted in bug). Microsoft's DotNet implementations of connection pooling call sp_reset_connection. It's a light-weight call. What is the justification for ColdFusion NOT calling it?
Comment by External U.
5764 | September 22, 2015 09:19:31 PM GMT
Being fundamental to how our applications run, resolving this issue has a *very* high priority.
Vote by External U.
5774 | September 22, 2015 09:22:53 PM GMT
David, I have tried the steps given above after removing isolation level from cfquery tag and it is working as expected. Can you please try and let us know if you are still facing this issue? Regards, Nimit
Comment by Nimit S.
5765 | November 05, 2015 01:40:38 AM GMT
David, Is there any update on this issue?
Comment by Nimit S.
5766 | December 08, 2015 08:47:51 AM GMT
David, We have not received any update on this bug hence, closing it. We can evaluate this again if anyone can provide a repro case.
Comment by Nimit S.
5767 | December 17, 2015 08:31:40 AM GMT
I think you are maybe missing the point of this issue. Any command issued in cfquery that creates an object that has a lifecycle of the current t-sql session will live beyond the life of the cfquery and leak into other cfqueries. It would be the equivalent of request scope not being cleared at the end of a request and instead being stored in the underlying tomcat worker thread and headed to the next thread. Examples of things that will randomly fail: Temp tables, unless dropped at the end of the Cfquery Create Table #MyTempTable(i int) SET @LockName = 'MyConcurrencyLock_' + OBJECT_NAME(@@PROCID) + '_' + db_name(); EXEC @res = sp_getapplock @Resource = @LockName, @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 0, @DbPrincipal = 'Elevated' This is a 'session' scoped named database lock to stop concurrent execution of a batch job on a cluster of servers (hence, cflock won't work). If the lock is never cleaned up then it lives for the life of the connection in the connection pool. The same applies for variables such as @@error info @@rowcount Other application servers that use connection pooling will set a flag for sp_reset_connection on the TDS packet at the end of the request. As this is a flag, I can't imagine much performance overhead.
Comment by External U.
5768 | June 08, 2016 04:40:41 PM GMT