NOLOCK, READPAST and SQL table locking

chris_johnson — Wed, 14 Oct 2015 08:59:45 GMT

Hi all,

I have a bit of a tricky problem that seems to be happening in QV but not SQL Management Studio.

I have been using NOLOCK previously for extracting data from a frequently used database (I know table hints are a last resort!). As I have been using this, one of the problems I encounter is occasionally there is a "dirty read" of uncommitted data. This isn't very often but enough to wonder if there is another solution.

I have seen that using the READPAST table hint can solve issues with table locking and also mean that I do not extract any uncommitted data. Testing this in SQL Management Studio confirms that READPAST does not extract data still open (in an uncommitted transaction).

Great!

But the problem seems to be that when I try this in QV it doesn't work like that (is this due to OLEDB?). QV will extract to a point and then wait until the open transaction has been committed or rolled back.

Does anyone have any ideas why this is?

Thanks,

Chris

Re: NOLOCK, READPAST and SQL table locking

roccosdg — Wed, 20 Feb 2019 04:19:42 GMT

Hey,

Did you by chance ever find a solution/work around? Having same issue.

topic NOLOCK, READPAST and SQL table locking in QlikView

NOLOCK, READPAST and SQL table locking

Re: NOLOCK, READPAST and SQL table locking