Highlighted

How can I manage CF hanging when waiting for release of a lock held by 2nd Application on same datasource?

Community Beginner ,
Apr 15, 2015

Copy link to clipboard

Copied

My CF application links to the MS SQL database of my accounting application.

If a specific record is being edited in the accounting application and I try to update that specific record using the coldfusion application, the coldfusion system hangs until the accounting application releases the record.

This is fine except the user in the accounting application may have left the editing screen open and the record may not be released for minutes or longer. Once this is the case the hanging coldfusion request creates further locks.

How can I manage the situation so that I can send a message to the user that they cannot gain exclusive access to the record and so cannot update it until the record is free thus preventing the hanging situation which escalates the problem?

Regards

Richard

Views

1.1K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How can I manage CF hanging when waiting for release of a lock held by 2nd Application on same datasource?

Community Beginner ,
Apr 15, 2015

Copy link to clipboard

Copied

My CF application links to the MS SQL database of my accounting application.

If a specific record is being edited in the accounting application and I try to update that specific record using the coldfusion application, the coldfusion system hangs until the accounting application releases the record.

This is fine except the user in the accounting application may have left the editing screen open and the record may not be released for minutes or longer. Once this is the case the hanging coldfusion request creates further locks.

How can I manage the situation so that I can send a message to the user that they cannot gain exclusive access to the record and so cannot update it until the record is free thus preventing the hanging situation which escalates the problem?

Regards

Richard

Views

1.1K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 15, 2015 0
Engaged ,
Apr 15, 2015

Copy link to clipboard

Copied

You could try a call to the datasource within a CFTRY and a specific timeout setting.  If the timeout is hit, most like due to being locked out of the datasource, then inform the user in the CFCATCH.

    

     <cftry>

          <cfsetting requestTimeout="100">

            <cfquery datasource="#MyDSN#" name="test">

                SHOW TABLES;

            </cfquery>

            <cfcatch type="any">

                Sorry, the datasource is not available.

            </cfcatch>

        </cftry>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Community Beginner ,
Apr 15, 2015

Copy link to clipboard

Copied

The transaction is already within a <cftransaction> with a <cftry> and <cfcatch> - see simplified version below. If anything is caught by the cfcatch then the process should stop and an error be given.

However when the lock occurs, there is no error reported, it just hangs. When the lock is released there is a general timeout error rather than an error caught by the <cftry> and <cfcatch>

<cfset queries_completed=1> 

<cftransaction><!--- this is to make all the updates as one transaction which either completes or fails--->   

<cftry><!--- this is for error catching if any of the queries fail - errors will be caught lower down by cfcatch tag --->

<cfquery name="update_customer" datasource="#request.odbc_datasource#">       

           Update scheme.slcustm
           SET control_digit='#control_digit#'
           where customer='#customer_id#'       

  </cfquery>

       

<cfcatch> <!--- catch any error and perform actions below--->

<cfset queries_completed=0>

<cftransaction action="rollback"><!--- reverses above queries --->

<p>This transaction failed ! Error Type: <cfoutput>#cfcatch.type#</cfoutput></p>

<p><cfoutput>#cfcatch.message#</cfoutput></p>

<p><cfoutput>#cfcatch.detail#</cfoutput></p>

<cfoutput>

<p><a href="javascript:history.back()">Go back to amend</a></p>

</cfoutput>

<cfabort>

</cfcatch>

</cftry>

<cfif queries_completed is 1>

<cftransaction action="commit">

</cfif>

</cftransaction><!--- end of transaction --->

Regards

Richard

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Community Beginner ,
Apr 15, 2015

Copy link to clipboard

Copied

Some more detail - I did try the cfsetting as above but made no difference to the end result. The system hangs until the lock is released and then reports a timeout error of the query and a timeout error on the output in the <cfcatch> tag

14:36:19.019 - Application Exception - in C:/ColdFusion11/cfusion/wwwroot/sagelink_diaries/sales/call_contact_2.cfm : line 117

         The request has exceeded the allowable time limit Tag: CFQUERY 

14:36:20.020 - coldfusion.util.RuntimeWrapper Exception - in C:/ColdFusion11/cfusion/wwwroot/sagelink_diaries/sales/call_contact_2.cfm : line 271

         The request has exceeded the allowable time limit Tag: cfoutput 

Regards

Richard

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Engaged ,
Apr 15, 2015

Copy link to clipboard

Copied

Do you have access to the CF Admin API?  If so, Ray Camden wrote a blog post years ago talking about using that to verify DSNs.

<cfscript>
      adminObj = createObject("component","cfide.adminapi.administrator");
      adminObj.login("admin");

            datasource = createObject("component", "cfide.adminapi.datasource");
            res = datasource.verifyDSN("peanutbutterjellytime", true);
</cfscript>

Here was his post:

Did you know you could verify DSNs with ColdFusion code? | Raymond Camden's Blog

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Community Beginner ,
Apr 15, 2015

Copy link to clipboard

Copied

I do have access to CF Admin API but I am not sure how this would help because I have no problem with connecting to the datasource.

My issue is getting CF to act gracefully rather than hang when trying to update a datasource record which is locked by a second (non CF) application. There doesn't appear to be a way to terminate the Coldfusion update gracefully if a record is locked.

Regards

Richard

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Engaged ,
Apr 15, 2015

Copy link to clipboard

Copied

Can you query the DB and ask whether tables are locked?  I work mostly in MySQL and there is a query for that.  Not sure about MS SQL.  I didn't find a few postings.  Here's one:

sql - How to check which locks are held on a table - Stack Overflow

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Community Beginner ,
Apr 15, 2015

Copy link to clipboard

Copied

I think I may have sorted this out.

I have added the SQL command : SET LOCK_TIMEOUT timeout_period to the query in question and this now reports the error which is captured by the <cfcatch>.

This transaction failed ! Error Type: Database

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.

This also works if I just add a timeout to the update query - ie. <cfquery name="update_customer" datasource="#request.odbc_datasource#" timeout=timeout_period

Regards

Richard

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Community Beginner ,
Apr 15, 2015

Copy link to clipboard

Copied

Thanks for input - I do have some tools to identify the blocking process but it is the escalating impact that has caused me headaches. Using the timeout above (or indeed the SQL hint NOWAIT which sets LOCK_TIMEOUT = 0) should allow me to remove the headaches and point me to where the problem is.

Regards

Richard

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Advocate ,
Apr 15, 2015

Copy link to clipboard

Copied

Not sure why cfsetting timeout would be used here as there is a timeout setting in the cfquery tag. Either way, if your site deal with any web volume, you should rethink your SQL activity and schema as time consuming resource locks will kill any application and relying on "resource busy" alerts to the user will just annoy them.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 15, 2015 0
Community Beginner ,
Apr 16, 2015

Copy link to clipboard

Copied

Hi Steve

The issue is the lock by the accounting application (which I have no control of) affecting my CF application (fortunately internal without large web voumes). Once the CF application is hanging because of the initial block, this quickly escalates becuase it seems the second lock locks more than just the specific record. Now that I can give a 'resource busy' alert to the user - I can also send myself an update on which resource is busy and locate what/who is causing it and therefore try to minimise this root cause and its consequent impact.

For what is is worth, it seems likely that the problems stem from users creating or editing customer or stock items in the accounting system but then leaving the editing screen open without confirming changes as they move to a different screen to respond to some interrupt.

If I can find some pattern to this I can then use SQL to indicate if any such action is taking longer than a given time and take some action before any second lock is created - after all, the original application also gives a 'resource busy' warning if a second user tries to edit the same record which is already open and being edited.

Regards

Richard

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 16, 2015 0
Advocate ,
Apr 16, 2015

Copy link to clipboard

Copied

One option that we use a lot of is the (NOLOCK) locking hint in the queries for non-critical viewing type queries. For us it avoids many of the users stepping on users issues but only use these in non-critical quieries -- like not batch processing or block updates.

select x,y,x from sometable (NOLOCK)

where x='something'

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 16, 2015 0