SQL deadlock in ColdFusion thread

Question

I'm trying to figure out why I would be getting a deadlock error when executing a simple query inside a thread. I'm running CF10 with SQL Server 2008 R2, on a Windows 2012 server.

Once per day, I've got a process that caches a bunch of blog feeds in a database. For each blog feed, I create a thread and do all the work in inside it. Sometimes it runs fine with no errors, other times I get the following error in one or more of the threads:

[Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

This deadlock condition happens when I run a query that sets a flag indicating that the feed is being updated. Obviously, this query could happen concurrently with other threads that are updating other feeds.

From my research, I think I can solve the problem by putting a exclusive named lock around the query, but why would I need to do that? I've never had to deal with deadlocks before, so forgive my ignorance on the subject. How is it possible that I can run into a deadlock condition?

Since there's too much code to post, here's a rough algorithm:

thread name="#createUUID()#" action="run" idBlog=idBlog {

   try {

       var feedResults = getFeed(idBlog);
       if (feedResults.errorCode != 0)
          throw(message="failed to get feed");

       transaction {
           /* just a simple query to set a flag */
           dirtyBlogCache(idBlog); /* this is where i get the deadlock */
           cacheFeedResults(idBlog, feedResults);
       }

       } catch (any e) {
           reportError(e);
       }
   }

} /* thread */

Answer 1

This approach has been working well for me.

 <cffunction name="runQuery" access="private" returntype="query">
 arguments if necessary
 <cfset var whatever = QueryNew("a")>
 <cfquery name="whatever">
 sql
 </cfquery>
 <cfreturn whatever>
 </cffunction>

 attempts = 0;
 myQuery = "not a query";
 while (attempts <= 3 && isQuery(myQuery) == false) {
 attempts += 1;
 try {
 myQuery = runQuery();
 }
 catch (any e) {
 } 

}  

After all, the message does say to re-run the transaction.