1

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 */
Redtopia
  • 4,947
  • 7
  • 45
  • 68
  • 2
    [Here are some tips for troubleshooting deadlocks](https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/) – Andomar May 20 '15 at 05:51
  • 4
    It's pretty difficult to say anything sensible about this without a repro case or at least an idea of what *some* of the code looks like. Also some hint as to what remedial steps you've already tried would help. – Adam Cameron May 20 '15 at 06:52
  • It sounds like you may be setting the flag to say that the feed is being updated within the same transaction that is updating the records. If that's the case then try moving that outside of the transaction. – John Whish May 20 '15 at 08:06
  • Thanks @AdamCameron... I posted the basic code flow. I have not yet tried anything to fix the problem because I'm not sure what could be causing the deadlock. As I said, it works about 75% of the time with no errors. I would prefer not to put an exclusive named lock around the query that sets the flag until I know why. – Redtopia May 20 '15 at 15:41
  • 1
    (Edit) There is still nothing above about the actual queries, structure of your db table(s) and indexes - so it is impossible to offer much beyond guessing. There is not a single answer/cause. You need to do some tracing/troubleshooting (see first comment). – Leigh May 20 '15 at 16:50
  • Thanks for looking @Leigh. All I can say is that the query that generates the deadlock is about as simple as it gets... just a single table with a PK and a few fields. It's too much code to post. I was hoping that the problem could be narrowed down a bit. At this point, I have no idea why sometimes two threads would be waiting for the same resource, and I'm not sure what resource is being waited on. – Redtopia May 20 '15 at 20:52
  • 1
    *not sure what resource is being waited on* Tracing will help you answer that question :) This is really a SQL Server issue, so you need to use its tools to troubleshoot it. Deadlocks are often caused by table or page locks conflicts, sometimes due to insufficient indexes - but there are other causes as well. See [Detecting and Ending Deadlocks](https://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx). But again, it is very difficult to offer much in the way of suggestions without seeing at least a) the generated SQL and b) the table DDL. – Leigh May 20 '15 at 22:29

1 Answers1

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Thanks for the suggestion, and I can see how this might work. However, I want to know why my code is generating a deadlock condition because I'm pretty sure that if a deadlock exists, you want to figure out why and fix it rather than live with it and write code that works around it. – Redtopia May 20 '15 at 20:54
  • Thanks @DanBracuk. Being that this is the first time *ever* that I've had to deal with deadlocks, I'm not yet convinced that it's SQL Server. I have a feeling it has to do with the way my code is structured, and more specifically having to do with threads. – Redtopia May 21 '15 at 15:05
  • Microsoft took the time and effort to create that specific error message. Also, the queries that inspired me to come up with this approach were simple select commands. No threads, no transactions, just select queries. – Dan Bracuk May 22 '15 at 13:05
  • Sure threads are "involved", but every request to the CF server involves threads. It is definitely a SQL server issue. But like I said earlier, *you* have to do some tracing and investigation to find out what specifically about the db structure is causing it. Beyond that, it is impossible to help further unless you post your SQL and DDL. Is there some reason you cannot post that information? – Leigh May 22 '15 at 22:55
  • @Leigh, I don't want to ask anyone to spend that much time on this, which is why I'm reluctant to post the CFC and schema. Thanks for trying to help! – Redtopia May 26 '15 at 17:20
  • Well respecting everyone's time is appreciated. However, unless there is more to it than you described ie *"... just a single table with a PK and a few fields"* it would have been quicker to just post the table DDL and the generated SQL (NOT the cfquery). Given that is it ultimately a SQL Server issue, it is impossible to assist without any ... well... SQL ;-) – Leigh May 31 '15 at 16:58