Welcome! Log In Create A New Profile


WD24 - sql native driver - WITH NOLOCK option

Posted by ArieM 
WD24 - sql native driver - WITH NOLOCK option
April 28, 2021 05:10PM

I noticed that WD (using the native driver, not sure if it is the same with OLEDcool smiley that the WITH NOLOCK switch is added to all SELECT queries.

NOLOCK (or READ UNCOMITTED) means one reads data which is not yet committed and could possibly be inconsistent or get rolled back. Or read a record with a BLOB-field at a point where the data is already there but the (large) BLOB is being written.

In itself not directly a bad thing (it depends), but is there a way to discard or overrule this option?

Re: WD24 - sql native driver - WITH NOLOCK option
April 28, 2021 09:06PM
Hi Arie

To my knowledge your interpretation of the "with (nolock)" option is wrong and to my knowledge it are two different query options that you can use individually or combine.
So "with (nolock)" is not the same as "with (readuncommitted)".

The (with nolock) option has many advantages on the other hand. It will execute faster and above all will prevent potential dead locks on the other hand.

This is because of how (relational) database systems work. Without the "with (nolock)" option to your select statement, the records will be read with what is known as a "share lock", meaning that some sort of preemptive locking of the record (or typically data page) is requested during the read operation (select). Next upon update or delete this will escalate to an exclusive lock request for your process id on that record (or typically data page).

The "with (nolock)" doesn't read uncommitted data apart from the uncommitted data within your own process ID which you would also see without the "with (nolock)" option. If you want to see uncommitted data from other process IDs (users/sessions) within your result set, you will need to add the "with (readuncommitted)" as well in that case.

That is caused by the "ACID" compliancy that about any relational database system adheres to...
Atomicity, Concurrency, Isolation and Durability

If you want to learn more about it, either read C.J Date's bible "An introduction to database systems" or this wiki page: https://en.wikipedia.org/wiki/ACID


Peter Holemans

Edited 1 time(s). Last edit at 04/28/2021 09:10PM by PeHoBe.
Re: WD24 - sql native driver - WITH NOLOCK option
April 29, 2021 10:57AM
Hi Peter,

Afaik the NOLOCK is used on individual tables at the moment of the SELECT query and the READ_UNCOMITTED is set as an isolation level on the entire session/connection, meaning for all tables.

But that's food for other forums smiling smiley

The help (should have looked there better...) says I can use the extendedInfo property of the connection to fiddle around with this option.

It looks like the default though is without NOLOCK, so I'm curious why I see it in all my queries on the server.

I always use no lock in select statement if not a 10 minutes query can endup 40 minutes to finish.

don't anyhow use lock in select , it can cause big problem .
I learn this lesson on exact system with database 120GB and 100 terminal . exact use lock heavy in the system . anyhow use lock can cause slow and if unlucky big issues

Your Email:


Spam prevention:
Please, enter the code that you see below in the input field. This is for blocking bots that try to post this form automatically. If the code is hard to read, then just try to guess it right. If you enter the wrong code, a new image is created and you get another chance to enter it right.