Relational Databases use locks to prevent Concurrency issues. In Databases, locks can be acquired on different objects, which can be databases on the whole, or table, pages, keys, indexes, ranges of keys or rows. No configuration by the user is required for the locks. SQL server dynamically makes decision for the lock at the appropriate level. And it is recommended that if user wants to change the default locking behavior, Table level locks should be used but only when necessary, as it will affect concurrency.
Locking hints can be used to specify the lock type. Changing a lock will change the isolation level of the object for that session. If there is a lock specified for an object, multiple users cannot access that object. In this case, users have to wait for the access to that object until the lock is released.
Where can we use this?
To avoid such conditions, NOLOCK keyword can be used with the SELECT statement for accessing the data while it is updating. NOLOCK is equivalent to READUNCOMMITTED. It will cause dirty reads and can only be used in the situation where dirty reads can be avoided.
Syntax:
In SQL Server 2008, NOLOCK is depreciated. And now can be used as “WITH NOLOCK”. WITH NOLOCK works as NOLOCK and can be used in place of it. For Example,
SELECT * FROM TABLE
WITH (NOLOCK)
WHERE ColumnId = 323
