Copy link to clipboard
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?
Copy link to clipboard
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.
<cfquery datasource="#MyDSN#" name="test">
Sorry, the datasource is not available.
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>
<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#">
<cfcatch> <!--- catch any error and perform actions below--->
<cftransaction action="rollback"><!--- reverses above queries --->
<p>This transaction failed ! Error Type: <cfoutput>#cfcatch.type#</cfoutput></p>
<cfif queries_completed is 1>
</cftransaction><!--- end of transaction --->
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
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.
adminObj = createObject("component","cfide.adminapi.administrator");
datasource = createObject("component", "cfide.adminapi.datasource");
res = datasource.verifyDSN("peanutbutterjellytime", true);
Here was his post:
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.
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:
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>
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.
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.
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.
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)