ODBC timeouts or errors

Forum|Forum|19 years ago
January 9, 2007
4 replies
2326 views

We have a developer that is running queries using Oracle RDB ODBC driver. When he runs his queries it times out and locks up the server that noone can run an odbc connection to anything. We try to stop the coldfusion ODBC server and it hangs to where the server cannot stop it as it loses communication. It mentions in the logs about increasing the Jrun threads, but we cannot find them. We increased the session threads from 8 to 16 and that did not help. Once he crashes then everyone gets timeouts when running anything or running queries. The developer has error traps everywhere and aborts his queries after timeouts and we just get dropped connections in the logs. We have to reboot the server everytime we get this issue. We are running it on a P2 Xeon 450 with 512 meg and it is just a development server nothing else. Please give some insight if you can on what to try.

Database access

This topic has been closed for replies.

BKBK

Community Expert

Following MikerRoo's suggestion, remove all custom locks. Apply the timeout attribute in your cfquery tags.

BKBK

Community Expert

JRun is a process in Coldfusion. It's probably running because you've given it something to do. Let's have a look at the query code.

K

klcoyneAuthor

Known Participant

Yeah the jrun is runing when we are doing our data access to our vax machine. We currently have one processor and have the simultaneous threads set at 8 and the stacked thread limit is at 100. Lately we have been able to run the program continuously for awhile and then stop it and start it again and it is fine. then we have a second person run it about the same time and that will work for a couple passes and then fail. yesterday we wrote just a simple query to pull back data from the vax. we could run it without a problem. we then added a second person running it, again no problem. We added a 3rd and it still ran and we added a 4th and then the 3rd froze during the retrieval and display of the data. He could refresh and it started again. then the 4th did it but not 1 and two. finally it locked to where 1 and 2 got timeout errors and then 3 and 4 were locked also. this is a test box for us and currently it is a xeon 450 processor with 512 meg ram. Here is some of the code that we keep getting the timeout errors on:

A

Anonymous

Where do the timeouts occur? Is it the first query, 2nd or varied. How much memory is getting used - turn on metrics to see what is going on in coldfusionmx. Are you maxing out your memory with these queries?

Oracle RDB supports stored procedures from version 6 on. Your first query looks like a great candidate for stored procedure. And you did not show us PressQuery. If your resultset is very large your looping through and modifying the queryobject could eat up all sorts of memory and be scanned constantly since there is no index in play there. Whatever tools RDB gives you to do this work on the backend, should be employeed - temporary tables, storedprocs, etc. I must confess, I have not used RDB much in the past 12 years. So, I can only toss ideas out. But joining 5 tables has to work better in a SP vs. a query. Are your join columns all on indices? Is Cure_Unit.Close_Date indexed?

Oracle appears to have both a thin JDBC and fat JDBC driver for RDB. You should test your results with them. JDBC is a much better option for CFMX vs. ODBC.

BKBK

Community Expert

Don't forget why you decided to use cflock. You might have to work around scoped variables. For example, what once was

<cflock scope="session">
<cfquery>
SQL involving session.myVar
</cfquery>
</cflock>

now becomes

<cflock scope="session">
set request.myVar=session.myVar
</cflock>

<cfquery>
SQL involving request.myVar
</cfquery>

A

Anonymous

You do not say which version of CFMX. But, review this thread and update to sequelink 5.4 with the latest patches.

K

klcoyneAuthor

Known Participant

Thanks Ken but we are running coldfusion MX 7 so that is not it. On this particular situation, the developer had cflock before every query and from what I read cflock will put all the other threads into a queue waiting for the cflock to finish. Well there is a timeout in the queue and we have not adjusted it, it is just the defualt. What I think was happening is that the threads were timing out in the queue and getting dropped causing the program then to hang while trying to do odbc calls since the threads were no longer there. We removed all the cflocks and the program runs fine. Does this sound logical to you??

A

Anonymous

This means that the cflocks were not quite right and you must have been getting deadlocks.

Drastically reducing the cflock timeouts may make the system freeze less permanent but the correct solution is careful analysis to remove the potential deadlock situation.

For starters, cflock should only be used on "serial type" hardware access or key global variables or scopes. (DB calls must not use cflock, use the DB's transaction methods if needed.)

There should only be one "write" lock for any given resource and ideally only one "read" lock (if any read locks at all).
Nested locks should only be used by the most expert coders and any overlapping locks must be hunted down mercilessly and deleted.

Anyway, google "deadlocks" for more thought on this.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded