Adding cftransactions to prevent deadlocks in SQL Server

I recently had to dive into errors from one of our most busy web applications. The errors came from SQL Server, saying "Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

This error comes from SQL Server, and more or less says "I could not execute your query, because it got in the way of another query". Veeery simply said, I know.

First thing I did, was asking the Railo team whether we could create a new option in the datasource screen "retry deadlocked query", with a number indicating the maximum number of retries. Initial reactions were positive, so I added a feature request into the Railo JIRA.

But, problem still remains for now. In my situation, it was always a SELECT query which was chosen as the deadlock victim. Now, I did a lot of googling and reading about preventing or circumventing this problem, and the most common answer was "use Read Uncommitted with your select statements, if you don't mind dirty reads". After discussing a bit at the office, we decided we don't mind, since we couldn't think of a really problematic scenario with it.

So, we had to change all select queries from

<cfquery name="q" datasource="#mydsn#">
SELECT col1, col2 FROM mytable
</cfquery>

to

<cftransaction isolation="read_uncommitted">
<cfquery name="q" datasource="#mydsn#">
SELECT col1, col2 FROM mytable
</cfquery>
</cftransaction>

A poor colleague of ours was already given the assignment to do it manually, but lucky for him I came up with the following script, which does it automatically. The only thing the script lacks, is indenting the cfquery one more level.

<cfset dir = "/developing/mysite/" />
<cfdirectory name="q" action="list" directory="#dir#" recurse="yes" filter="*.cfm|*.cfc" />
<cfloop query="q">
<cfset file = "#q.directory#/#q.name#" />
<cfoutput>#file#<br /></cfoutput>
<cfset data = fileread(file) />
<cfset newdata = rereplaceNoCase(data
, "([\r\n]{1,2}[\t ]*)(<cfquery [^>]+>[\r\n\t ]*SELECT.*?</cfquery>)"
, "\1<cftransaction isolation=""read_uncommitted"">\1\2\1</cftransaction>"
, "all") />
<cfif data neq newdata>
<cfset filewrite(file, newdata) />
</cfif>
</cfloop>
done.

The script recursively scans a directory for cfm and cfc files, and then adds the cftransaction tag around any cfquery which begins with the word "select". Hope it helps you out :-)

Btw: if you were already using cftransactions in your code which includes one or more select queries, then make sure that you are running Railo 3.2.0.004 or later, since support for nested transactions was added then. Adobe Coldfusion also supports nested transactions since version 9.

del.icio.us Digg StumbleUpon Facebook Technorati Fav reddit Google Bookmarks
| Viewed 6103 times
  1. zac spitzer

    #1 by zac spitzer - juni 15, 2011 at 15:28

    Ugly solution, feels soo wrong
  2. Dan G. Switzer, II

    #2 by Dan G. Switzer, II - juni 15, 2011 at 16:16

    @Paul:

    IMO, you're better off using table hints (e.g. NOLOCK) instead read uncommitted transactions. Table hints give:

    * The ability to dictate which specific tables you want to lock--instead forcing all tables to the same level of isolation

    * The ability to specify the type of isolation on each table -- I often find I write JOINs where I may only want to allow dirty reads on certain tables (ones I know almost never change)

    * If your use CFCs for any of your queries, trying to wrap the queries in CFTRANSATION may lead to problems -- I normally leave transactions outside of my data objects, that way I can control commit/rollbacks from the business logic

    * Generally less code -- you can obtain the same affect with your query by re-writing it as:

    SELECT col1, col2 FROM mytable (nolock)

    Just be aware that dirty reads can potentially cause unexpected issues -- reading uncommitted is not a panacea:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
  3. Paul Klinkenberg

    #3 by Paul Klinkenberg - juni 17, 2011 at 8:11

    Hi Dan,
    Thanks for all your suggestions!
    I did look at all the points you mention, before I wrote this blog post.
    Using table hints instead of a cftransaction, makes sense if there were tables that I wanted to be sure to not get uncommitted data from. But as I wrote in the blog post, that's not the case. We can't think of one case in our application where displaying dirty data would be a real problem.
    The link you suggested is indeed very interesting, but simply states the point "watch out! You might show dirty data". Well, as I blogged, we do not care.

    It could be that in the near future, I will encounter a situation where unexpected results due to dirty reads led to problems. If that's going to happen, I'll sure blog about it :)

    Thanks again for your suggestions, Paul
  4. Paul Klinkenberg

    #4 by Paul Klinkenberg - juni 17, 2011 at 8:14

    @zac: a little technical clarification would sure help in understanding your reaction...
  5. zac spitzer

    #5 by zac spitzer - juni 17, 2011 at 10:06

    Hi Paul,

    I'm interested to know if the same issue occur with adobe cf?

    What are the background queries (update insert) which are causing this problem?

    I use Oracle and SQL Server with CF (abobe mostly) and while oracle is much smarter in it's locking algorithms, that said I haven't seen this problem in years with SQL Server either in years.

    Rather than update every single query, wouldn't it be better to apply this only to a few select queries?

    z
(will not be published)
Leave this field empty

overwrought