Opening query blocks all other threads - OCI problem?

Neil · Post by **Neil** » Thu 25 May 2006 22:03

I run a lot of different queries and have run into a problem.
Most queries are very fast, but some take up to a minute.

Whilst waiting for a long running query to open, it would be nice for the user to see other queries being processed, but they can't

Tried multiple threads with one Connection, small queries blocked by long queries.

Tried multiple connections each with a thread, a long running query on one connection blocks the opening of small queries on others.

Tried different connections to different physical servers, long running Query on server A blocked query opening on server B!
Isolated problem to call on OCIStmtFetch where the blocking happened.

Any ideas? Blocking on a process level seems too restrictive, I feel there must be a way to sove this

Many thanks
Neil

Challenger · Post by **Challenger** » Fri 26 May 2006 06:40

Please specify the version of Oracle client you use.

npoulton · Post by **npoulton** » Fri 26 May 2006 07:00

8174 on the development machine.
Clients will be using anything >= 8

Challenger · Post by **Challenger** » Fri 26 May 2006 08:40

There are two global variables OCIThreaded and OCIMutexed declared in modue OraCall. By default
they are set to True. Please try to set them to False in initialization section of your main unit
(Only for Oracle 8 client).

npoulton · Post by **npoulton** » Fri 26 May 2006 09:13

That works a charm - Thanks

Will this cause any problems when client is >8?
Customers will have various versions of clients, and possibly different Oracle Homes which will be connected to at the same time.

Challenger · Post by **Challenger** » Mon 29 May 2006 11:17

This problem can be reproduced only using Oracle 8 client. That's why
there is no need to set these variables under client version > 8. You
can check client version using OCIVersion variable.