WD24 - sql native driver - WITH NOLOCK option

Hi,

I noticed that WD (using the native driver, not sure if it is the same with OLED 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?

Arie

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

Cheers

Peter Holemans



Edited 1 time(s). Last edit at 04/28/2021 09:10PM by PeHoBe.

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

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

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

Arie

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

Hoi Arie,

Use sqlexecute (). with this synatx [ tekst between these signs ]

works as well . Then you have full control over what you want.


regards

Renko