The scripts that I have lock up occasionally are writing (usually setting a UDF flag for an automated / stepped process), not just read only queries.
I have also experienced issues with OPENQUERY, but they seem to have gone away when I updated the 64-bit ODBC driver used to the version that came with v2014 (...I was originally working with the driver that came with v2013). Another issue I had was the user context of the SQL statement execution. Using a trusted connection often let me down, but with a native SQL login it has been more consistent.

Edit:

For your burstmode question, I set up the connection as documented elsewhere in SageCity.  I'm sure you've seen that article already, but if not let me know and I'll hunt down a link.

Thanks again for the info.   I wonder why you had any issues with trusted connection.  Our stored procs are executed as owner, and all the sessions belong to the schema owner, so this might not be relevant to me.  But I'm curious....did you run any trace when you had your issues in Profiler or Extended Events?  What kind of issues were you having?   I'm thinking of tracing through DSN but I'm scared of the probable huge size of trace file...

As for burstmode....i found some explanations 

With Burst mode set, the ODBC driver locks the file header for either 20 file operations or three-tenths of a second, whichever occurs first. This decreases the number of times the file must be locked, and the number of times that internal buffers may need to be reloaded

Normally, when the ODBC driver accesses data files, it must place a temporary lock on the file. This temporary lock guarantees that the driver reads key tables and
structures that are in a consistent state and not in the process of being altered. Once the temporary lock is established, the driver checks the file header to see if it
has been changed since the last time the file was accessed. If the file has not been altered, then the ODBC driver can use any of the data still maintained in its buffers.
If the file has been altered, then all data in the buffers is discarded. When the driver has completed its access to the data file, the temporary lock is released.

The process is repeated for each file accessed by the driver, for each operation on the file.

I kinda get the implied performance boost and potential penalty in other areas, but I'm not certain how this could impact my transaction.

I looked at the connection strings for the linked-server (not the System DSN setting), and i see burstmode=1.  I specified it to zero to see if this will help.  Thanks again!

Hi BigLouie,

Thanks for chiming in. I've read many expert advice you left on this forum, some religiously, and I want you to know that I have lots of respect for you (that's not to say I don't respect you Kevin. You've helpmed me a few times already and I owe you big time!). I completely understand the need for DirtyReads to be off. Except in development and troubleshooting, and possibly in a very specialized queries, I can't think of a good reason for it. As for Burstmode, I saw you mentioned that somewhere after i last responded to Kevin. Thanks for sharing the info again here. Did Sage or anyone ever made it clear why burstmode should be off in this sort of applications? I'm wondering what the risk is when...say...2 Silent DSN on the network, one with burstmode on and the other off, do heavy read operations at the same time. With my limited knowledge, I can't think of anything other than some performance penalty.

That said....I've tested out the new connection string with BurstMode set to zero to turn if off (DirtyReads was zero already). And just to avoid any funny business, I made sure the System DSN with the same name had the same options. I was really hoping this would, but didn't help. I ran a very simple openquery statement,, eg 'select * from so_soheader where So# IS NULL', 100 times concurrently spread out in 4 different sessions. All but one of them got stuck within seconds.

At least I might be on to something on how to replicate the issue at will. If you have any suggestion, even if shooting in dark, I'd very much appreciate it! Many thanks again.

I'm using the 32-bit driver 4.40.0004. The 64-bit driver is installed, but with the SQL Sever 2008 being 32-bit i don't think 64-bit is a viable option for me....unfortunately

Last night I confirmed that I could replicate the issue at will using the method I described above by having multiple sessions run concurrently. Run the batch of 100 executions at the same in 2 separate sessions, or one right after another will lock up one session. Each sessions can successfully get data back for at least one execution.  If more than 2 session are involved, at least one or more sessions will hang while one or none finishes.

Now I know how to cause it. Still need help on what causes it, what else (logs etc) might give me a clue on how to circumvent it. A bit hopeless at the moment....