Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Brad Brewer
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Brad Brewer

ColdFusion Does Not Appear To Use The Connection Pool When Issuing KILL QUERY Commands

By Ben Nadel on
Tags: ColdFusion, SQL

Yesterday, I started to explore the Timeout attribute of the CFQuery tag, which is used to terminate long-running queries in ColdFusion. When looking at the MySQL General Log, I could see that ColdFusion was establishing a new connection to the databasing and issuing KILL QUERY commands in order to perform the termination. It made me curious as to what would happen if the connection pool was exhausted at the time when the timeout threshold was reached - could ColdFusion actually issue the KILL QUERY command? To find the answer to this question, I re-ran the previous test with a new datasource whose connection pool was limiting to a single connection.

First, I went into the ColdFusion Administrator and created a new MySQL 5 datasource - "testing_tiny_pool" - that was limited to a single connection:


 
 
 

 
 Creating a tiny connection pool for ColdFusion. 
 
 
 

Then, I wanted to create a control experiment in order to ensure that ColdFusion actually adheres to the single-connection limitation. To test this, I spawned two long-running queries in separate asynchronous CFThread bodies and checked to see if they ran in series, not in parallel:

  • <cfset startedAt = getTickCount() />
  •  
  • <!---
  • As a control to our experiment, we want to test that the pool-size of ONE is actually
  • going to be blocking concurrent requests. To do this, we're going to try and spawn 2
  • long-running queries and then check to confirm that they actually ran in series and
  • NOT in parallel.
  • --->
  •  
  • <!--- Async query one: 5-seconds. --->
  • <cfthread name="a" action="run">
  •  
  • <cfquery name="thread.results" datasource="testing_tiny_pool">
  • SELECT
  • SLEEP( 5 ),
  • ( UTC_TIMESTAMP() ) AS ranAt
  • ;
  • </cfquery>
  •  
  • </cfthread>
  •  
  • <!--- Async query two: 5-seconds. --->
  • <cfthread name="b" action="run">
  •  
  • <cfquery name="thread.results" datasource="testing_tiny_pool">
  • SELECT
  • SLEEP( 5 ),
  • ( UTC_TIMESTAMP() ) AS ranAt
  • ;
  • </cfquery>
  •  
  • </cfthread>
  •  
  • <!--- Now, let's wait for all the threads, and therefore all the queries, to return. --->
  • <cfthread action="join" />
  •  
  • <cfoutput>
  •  
  • <!---
  • What we expect to see is that this page took ( 5 + 5 ) seconds to execute
  • since the connection pool will have forced the queries to run in series, not
  • in parallel.
  • --->
  • Page ran in #fix( ( getTickCount() - startedAt ) / 1000 )# seconds.
  •  
  • <cfdump var="#cfthread.a#" label="Thread A" />
  • <cfdump var="#cfthread.b#" label="Thread B" />
  •  
  • </cfoutput>

As you can see, each query should run for 5-seconds thanks to the SLEEP() command. And, since both are using the "testing_tiny_pool" datasource, only one should be allowed to execute at a time; which means that one will block the other until it has returned. When we run this code, we can confirm that the connection pool works as expected:


 
 
 

 
 Control experiment for CFQuery timeout attribute with a tiny connection pool. 
 
 
 

As you can see, the page ran for 10-seconds in total, which means that one 5-second query did exhaust the connection pool and blocked the other 5-second query, causing both queries to run in serial.

Now that we know that ColdFusion adheres to the size-one connection pool, we can re-run the experiment from yesterday (with a few modifications) using the tiny connection pool. This time, we're going to run two INSERT statements that use the SLEEP() command. The first one will execute successfully while the second one should be interrupted by the timeout:

  • <!---
  • First, let's enable and clear the GENERAL LOG so that we can see what queries
  • actually execute in the MySQL database server.
  • --->
  • <cfquery name="setup" datasource="testing_tiny_pool">
  • SET GLOBAL general_log = 'ON';
  • SET GLOBAL log_output = 'TABLE';
  •  
  • TRUNCATE TABLE
  • mysql.general_log
  • ;
  • TRUNCATE TABLE
  • timeout_test
  • ;
  • </cfquery>
  •  
  • <cftry>
  • <!---
  • Now that we are using the tiny connection pool, let's run a query that we know
  • will need to be interrupted and see if ColdFusion can successfully send the
  • KILL QUERY command (while the connection pool is exhausted).
  • --->
  • <cfquery timeout="3" datasource="testing_tiny_pool">
  • INSERT INTO
  • timeout_test
  • SET
  • value = SLEEP( 1 )
  • ;
  •  
  • <!--- This is the query we expect to be interrupted by the timeout. --->
  • INSERT INTO
  • timeout_test
  • SET
  • value = SLEEP( 10 )
  • ;
  • </cfquery>
  •  
  • <cfcatch>
  •  
  • <cfdump
  • var="#cfcatch#"
  • label="Query Error"
  • show="type,message,detail"
  • />
  •  
  • </cfcatch>
  • </cftry>
  •  
  • <!--- Let's see which test values were actually inserted. --->
  • <cfquery name="test" datasource="testing_tiny_pool">
  • SELECT
  • id,
  • value
  • FROM
  • timeout_test
  • </cfquery>
  •  
  • <!--- Let's see which query statements were actually executed. --->
  • <cfquery name="log" datasource="testing_tiny_pool">
  • SELECT
  • CONVERT( l.argument USING utf8 ) AS argument
  • FROM
  • mysql.general_log l
  • WHERE
  • l.argument NOT LIKE '%general_log%' -- That's THIS query.
  • AND
  • l.argument NOT LIKE '%PROCESSLIST%' -- That's me looking at the processlist.
  • ORDER BY
  • l.event_time ASC
  • </cfquery>
  •  
  • <cfdump var="#test#" label="Test Values" />
  • <cfdump var="#log#" label="MySQL General Log" />

As you can see, the CFQuery timeout of 3-seconds should interrupt the second INSERT statement, which will take 10-seconds to complete. And, when we run this code, we get the following output:


 
 
 

 
 Testing the KILL QUERY command when the ColdFusion connection pool is exhausted. 
 
 
 

As you can see, the page only ran for 3-seconds, which means that the KILL QUERY command was issued successfully. An interesting little side-quirk is that the second INSERT statement wasn't interrupted - only the SLEEP() function was interrupted - allowing the second INSERT to complete. But, that's not really the main take-away of this result - the real take-away is that, despite the exhausted connection pool, ColdFusion was able to establish a new connect to the MySQL database and issue the KILL QUERY command. From this, we can deduce that ColdFusion does not use the current [exhausted] connection pool in order to terminate queries. I guess it creates one-off connections in order to perform this function.

It's super interesting to understand the mechanics of the ColdFusion CFQuery Timeout attribute. Not only can we see that ColdFusion is implementing the Timeout with KILL QUERY commands (at least with the MySQL 5 driver), we can also see that it creates one-off connections in order to issue said command. This means that it won't be affected by the state of the current connection pool. However, the MySQL server itself will still need to be in a state in which it can accept new connection (presumably) in order for the query timeouts to work.



Looking For A New Job?

Ooops, there are no jobs. Post one now for only $29 and own this real estate!

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.