Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

Working with keyword NOLOCK in SQL server 2008

Author: Zunnair

In this simple article you will learn that how to use NOLOCK keyword in PL/SQL and its advantages

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

Article Comments
there is No NOLOCK keyword in PL/SQL in Oracle .It is only available syntax in TSQL (SQL server)

Posted on 9/29/2010 6:23:44 PM by Reddy

Nice and simple

Posted on 11/6/2010 8:17:53 PM by mirundap

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< Get top n records in SQL Server 2008 using PL/SQL

Disclaimer - Privacy
© 2002-2017 DevASP.net