0 Replies Latest reply: Oct 14, 2015 4:59 AM by Adam Abwat-Johnson RSS

    NOLOCK, READPAST and SQL table locking

    Adam Abwat-Johnson

      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