How to retry a caught database deadlock?

[Chris Velevitch]

I have a serializable transaction inside a cftry block. How do I retry
my cftry block when I cfcatch a database deadlock error? I can't seem
to find a cfretry or equivalent tag.

Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

[Andrew Mercer]

how about putting it inside a condition loop and set the exit condition once the transaction has been completed.

I do something like setting a counter to 5 (for 5 retries) and loop while not 0 and then set counter to 0 on sucess.

[Chris Velevitch]

On 12/9/05, Andrew Mercer <ajme...@gmail.com> wrote:
> how about putting it inside a condition loop and set the exit condition once
> the transaction has been completed.
>
> I do something like setting a counter to 5 (for 5 retries) and loop while
> not 0 and then set counter to 0 on sucess.

Sounds messy.

[Andrew Mercer]

something like this:

<cfset keepTrying = 5 />

<cftry>
    <cfloop condition="keepTrying">
        <cftry>
            <!--- do something --->
           
            <cfset keepTrying = 0 />
           
            <cfcatch type="Any">
                <cfset keepTrying = keepTrying -1 />
               
                <cfif not keepTrying>
                    <cfrethrow>
                </cfif>
            </cfcatch>
        </cftry>
    </cfloop>

    <cfcatch>
        <!--- handle failure here --->
    </cfcatch>

</cftry>

On 12/9/05, Andrew Mercer <ajme...@gmail.com> wrote:

[Chris Velevitch]

It would be far cleaner and easier to understand if you could say:-

<cftransaction deadlockretrycount="5">
<!--- database commands --->
</cftransaction>

That is keep retrying the specified number of times, then throw a
deadlock error.

Chreis

[Shib71]

You could always write a tag that does that, encapsulating Andrew's code.

Blair

[Dale Fraser]

Something like this will work.

<cfloop index="retry" from="1" to="5">
<cfset error = false />
<cftry>
<cftransaction>
<cfquery name="yourQuery">
select *
from Blah
</cfquery>
</cftransaction>
<cfcatch type="database">
<cfset error = true />
</cfcatch>
</cftry>
<cfif error eq false>
<cfbreak />
</cfif>
</cfloop>

Regards
Dale Fraser

[Patrick Branley]

See code below...

You could get funky with CFASSOCIATE and use nested tags, but here is a simple custom tag that wraps the logic...

the only problem being that you would have to rely on putting all the statements in the one SQL string, which may not be possible on all JDBC drivers ?

According to the docs generatedContent can be a variable so maybe you could set up an array of sql statements inside the <CFMODULE tags to do the job. but then youre starting to get messy again, so maybe investegating how to use CFASSOCIATE to build sub tags to do the individual queries would be the go.

hth

Pat

<cfif thisTag.ExecutionMode eq "start">
     <cfif not isDefined("Attributes.retry") >   
         <cfset Attributes.retry = 1 >
     </cfif>
    <!--- <cfoutput>#Attributes.retry#</cfoutput> --->
<cfelse>
       
   
       <cftry>
                <cftransaction>
                    <cfquery name=" stLocal.q" datasource="#Application.DsName#" username="#Application.DsUserName#" password="#Application.DsPassword#" >
                    #thisTag.GeneratedContent#
                    </cfquery>
                </cftransaction>
               
                <cfset thisTag.GeneratedContent = "" ><!--- stop tag outputting content --->
                <cfexit method="exittag" >
               
               <cfcatch type="any">
                       <cfset thisTag.GeneratedContent = "" ><!--- stop tag outputting content --->
                        <cfif Attributes.retry LT Attributes.retryCount>                           
                            <cfset Attributes.retry = Attributes.retry + 1 >
                            <cfexit method="loop">
                        <cfelse>
                            <cfexit method="exittag" >
                        </cfif>
               </cfcatch>
       </cftry>
</cfif>

[Patrick Branley]

Okay heres the funky version...

retryTag.cfm
------------------------------------------

<cfif thisTag.ExecutionMode eq "start">
     <cfif not isDefined("Attributes.retry") >   
         <cfset Attributes.retry = 1 >
     </cfif>
   
    <!--- <cfoutput>#Attributes.retry#</cfoutput> --->
<cfelse>
       
   
       <cftry>
                <cftransaction>

                    <cfloop index="i" from="1" to="#ArrayLen(thisTag.AssocAttribs)#">
                        <cfquery name="stLocal.q" datasource="#Attributes.DsName#" username="#Attributes.DsUserName#" password="#Attributes.DsPassword#" >
                        #thisTag.AssocAttribs[i].sSql#
                        </cfquery>
                    </cfloop>

                </cftransaction>
               
                <cfset thisTag.GeneratedContent = "" ><!--- stop tag outputting content --->
                <cfexit method="exittag" >
               
               <cfcatch type="any">
                       <cfset thisTag.GeneratedContent = "" ><!--- stop tag outputting content --->
                        <cfif Attributes.retry LT Attributes.retryCount>                           
                            <cfset Attributes.retry = Attributes.retry + 1 >
                            <cfexit method="loop">
                        <cfelse>
                            <cfexit method="exittag" >
                        </cfif>
               </cfcatch>
       </cftry>
</cfif>

-----------------------

retryQuery.cfm
-----------------------------
<cfif thisTag.ExecutionMode eq "start">
    <cfassociate basetag="CF_retryTag" >
<cfelse>   
    <cfset attributes.sSQL = thisTag.GeneratedContent>
    <cfset thisTag.GeneratedContent = "" >
</cfif>
-----------------------------


test.cfm
----------------------------
<cfmodule template="/taglibs/retryTag.cfm" retryCount="5" dsName="" dsUserName="" dsPassword="">

<!--- this one dies in the ass because someone used plural table name--->
    <cfmodule template="/taglibs/retryQuery.cfm">  
    select * from users
    </cfmodule>

    <cfmodule template="/taglibs/retryQuery.cfm">   
    select * from user
    </cfmodule>

</cfmodule>

Ok now im gonna go do some real work...

Pat

[Dale Fraser]

Hey,

Why do people do

<cfif not isDefined("Attributes.retry") >
<cfset Attributes.retry = 1 >
</cfif>

Instead of

<cfparam name="Attributes.retry" default="1 />

Is there something I don't understand?

Regards
Dale Fraser

> -----Original Message-----
> From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On
> Behalf Of Patrick Branley
> Sent: Monday, 12 December 2005 11:32 AM
> To: cfau...@googlegroups.com
> Subject: [cfaussie] Re: How to retry a caught database deadlock?
>

> > www.flashdev.org.au <http://www.flashdev.org.au>
> >
> >
>
>
>
>
>

[Patrick Branley]

old habbits die hard...

cfparam is a 'better' way of doing it.

[Chris Velevitch]

The custom tag solutions posted won't work for complex queries. Eg.

q1 - select t1
if q1.recordcount = 0
insert into t1
q2 - select currval(t1_pk_seq)
insert (using currval from last insert
else
update t1
update t2 using t1.pk
end

because, I have to create lots of custom tags which are reuseable.

I tried creating a custom tag like

<cf_transaction deadlockcount=5>
....
</cf_transaction>

But I get an error with the following code

<cfif thisTag.ExecutionMode IS "start">
<cfset lvCount=0>
<cftry>
<cftransaction>
<cfelse>
</cftransaction>
<cfcatch type="database">
<cfset lvCount = lvCount+1>
<cfif lvCount le #attributes.deadlockcount#>
<cftransaction action="rollback" />
<cfexit method="loop">
<cfelse>
<cfrethrow>
</cfif>
</cfcatch>
</cftry>
</cfif>

but coldfusion is too restrictive.

Chris

[Patrick Branley]

that way *might* work but you cant have

<cftransaction>

<cfelse>

</cftransaction>

its just invalid syntax. you cant CFTRY then CFCATCH on a custom tag in 2 diff execution modes. again invalid syntaxt.

you would have to do <cftransaction action="begin"> and <cftransaction action="commit"> /<cftransaction action="rollback"> where appropriate. from my initial testing of this i couldnt get it working.