When database objects are read or modified, it gets locked so that the database integrity can be maintained. The purpose of Lock is to prevent modification while performing reads or other modifications. But there are occasions when we don’t want these kind of locks. For instance a query that takes a long time to get executed will prevent us from working on the same object if these locks are in operation. Our general tendency is to wait for it to finish and perform other operation. But there are ways to inform database not to do so, so that we can work without delay. Among them, NOLOCK is one.
NOLOCK is a table hint and as the name suggests it informs engine not to lock an object. Shared locks are replaced by schema locks. Schema locks us from changing the schema of the object. It is also known as READUNCOMMITTED meaning we will be reading uncommitted changes as well. This can be handy at.
1. Lets create a table and insert few values.
2. Now that the values are inserted run an uncommited statement in an transaction.
4.Now Use NOLOCK table hint and run the following query.
5. Run the following in the first query window.
Since NOLOCK comes with the power to read the uncommitted changes as well , there can be times when it may be misleading .So NOLOCK should be used with care.