http://vinoth1989.blogspot.com/2012/07/withnolock-advantages-and-disadvantages.html
Usage of with(NOLOCK)
When
data in a database is read or modified, the database engine uses
special types of controls, called locks, to maintain integrity in the
database. Locks basically work by making sure database records involved
in a transaction cannot be modified by other transactions until the
first transaction has committed, ensuring database consistency.
INSERT INTO Orders
The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script,
When
designing database applications, you should keep in mind the different
types of locks that will be issued, and the different levels of
isolation your transactions will occur. Typically, the SQL Server
defaults work fine for what you are trying to accomplish. However, there
will be times when it is advantageous to manually make hints to how
locks are issued on your tables in your SQL statements.
This
article focuses on table : NOLOCK. I'll set up a table to use for our
example queries. Execute the script in to create the Orders table and
populate the table with data.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'Orders')
DROP TABLE Orders
GO
CREATE TABLE Orders ( OrderId INT, ProductName VARCHAR (30), OrderDare DATE, PriceMONEY, )
GO
--Insert values to the table
INSERT INTO Orders VALUES (1,'Bag',GETDATE(),1500.00)
INSERT INTO Orders VALUES (1,'Book',GETDATE(),200.00)
GO
NOLOCK
This
table hint, also known as READUNCOMMITTED, is applicable to SELECT
statements only. NOLOCK indicates that no shared locks are issued
against the table that would prohibit other transactions from modifying
the data in the table.
The
benefit of the statement is that it allows you to keep the database
engine from issuing locks against the tables in your queries; this
increases concurrency and performance. The downside is that, because the
statement does not issue any locks against the tables being read, some
"dirty," uncommitted data could potentially be read. A "dirty" read is
one in which the data being read is involved in a transaction from
another connection. If that transaction rolls back its work, the data
read from the connection using NOLOCK will have read uncommitted data.
This type of read makes processing inconsistent and can lead to
problems. The trick is being able to know when you should use NOLOCK.
The
following example shows how NOLOCK works and how dirty reads can occur.
In the script below, I begin a transaction and insert a record in the
SalesHistory table.
BEGIN TRANSACTION
INSERT INTO Orders
(OrderId, ProductName (30), OrderDare, Price)
VALUES
(3,'Shoes',GETDATE(),2000.00)
The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script,
SELECT * FROM Orders
The above query is in Executiong stage only and resultwon’t display.
Since the table Orders is locked by the insert statement.
With using the NOLOCK table hint in returning the records in the Orders table.
SELECT * FROM Orders WITH(NOLOCK)
The number of records returned is 3. Since the transaction that entered the record into theOrders table has not been committed, I can undo it. I'll roll back the transaction by issuing the following statement:
ROLLBACK TRANSACTION
This
statement removes the record from the SalesHistory table that I
previously inserted. Now I run the same SELECT statement that I ran
earlier:
SELECT COUNT(*) FROM Orders WITH(NOLOCK)
This time the record count returned is 2. My first query read a record that was not yet committed -- this is a dirty read.
When to use WITH (NOLOCK)
WITH
(NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction
isolation level. So, you stand the risk of reading an uncommitted row
that is subsequently rolled back, i.e. data never made it into the
database. So, while it can prevent reads being deadlocked by other
operations, it comes with a risk. In a banking application with high
transaction rates, it's probably not going to be the right solution to
whatever problem you're trying to solve with it IMHO.
Most
banking applications can safely use nolock because they are
transactional in the business sense. You only write new rows, you never
update them
Most of the Real time scenario’s when we used to read the date we need to use theWITH(NOLOCK)
