Using WITH NOLOCK 1

[poli13]

Hi there,

We have an ERP system which is not one of the best out there and as a result we have quite a lot of ... challenges. We are also using Crystal Reports to get data from the MS SQL database used by the ERP. Recently, we were advised to use WITH NOLOCK on all out reports when querying the database. They (ERP tech support) say that by not locking the tables the number of problems that we have with their system will be reduced. What do you people think? Would it be a smart idea to modify all our existing reports (cca. 100) and add WITH NOLOCK with the SELECT statements? Is there any negative effect on doing this?

Thanks,
poli

 

[lyanch]

It is my understanding that Crystal standardly sents SQL requests with dirty reads allowed...

Lisa

 

[poli13]

So Lisa, if I understand correctly, WITH NOLOCK won't make any difference. Am I right?

 

[synapsevampire]

I don't think that NOLOCK prevents Crystqal from locking tables, it means that it won't pay attention to the locking in the database (meaning that you might get a dirty read).

As for whether Crystal's connectivity uses a dirty read, I've never seen the SQL passed to the database (using Trace) where a NOLOCK was included.

You might get a healthy performance boost this way.

And you may get bad data (uncommited transactions).

-k

 

[BuGlen]

I can tell you from our experience that the NOLOCK option in the queries does help with blocking issues that can occur on the database. As mentioned, this will allow for "dirty reads", but since most reports include filters such as date/time parameters it should not be an issue. An alternative to including NOLOCK to each table reference would be to include a transaction isolation level statement at the top of the query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Just for clarity, Crystal does not actually negotiate the connection or data retrieval. The standard drivers (ODBC, RDO, OLEDB, etc.) are responsible for connectivity and transactional functions between the client and server. This is the same for almost all applications that interact with RDBMS server data.

Hope this helps.

- Glen

Know thy data.