Introduction
It is quite
often in production environment that multiple application and/or users try to
access same resource at the same time which may create locking and blocking
issues. It is tricky to deal with this situation as there is no rocket science
which suits for all situations. It is all depends on situation because in some
production environment, it is not possible to resolve blocking issues easily
over the night.
The main
root cause for locking is, we have long running transaction which keeps your
object locked and meanwhile any request comes to access the same object, has to
wait until the current transaction complete its operation. Best choice should
be to minimize the transaction length so that it releases the lock quickly and
other request doesn’t need wait due to lock but unfortunately it is possible to
solve it easily in ALL environments.
Getting ready
I am
going to perform this example in my SQL Server 2012 RTM version but it may work
as it is in SQL Server 2005 / 2008 too.
Connect
to your SQL Server and open three query window. We will call these query
windows with following name:
·
Win1
·
Win2
·
Win3
How to do it...
1.
After
connecting to SQL Server Management Studio (SSMS), open Win1
2.
Create
one sample database, named SQLHub and create one table with sample rows with
following T-SQL script:
create database SQLHub
GO
USE SQLHub
GO
--if
orders table is already there. you can delete it than create new one with name
"Orders"
IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN
DROP TABLE orders
END
GO
--creating
table
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
--inserting
100000 sample rows into table
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),
ABS(a.object_id % 10),
CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
3. Execute one UPDATE statement in Win1 with BEGIN
TRANSACTION. Note that we don’t have COMMIT or ROLLBACK after the UPDATE
Statement at the moment. ROLLBACK is commented so it won’t be executed.
BEGIN TRANSACTION
UPDATE Orders SET Amount=5.00 WHERE OrderID<10
--ROLLBACK
4. In Win2, try to execute following SELECT statement:
select * from Orders Where orderID<=15
5.
You
will observe that SELECT statement wouldn’t return any results so in WIN3, try
to execute following T-SQL and know what is going on behind the screen:
sp_who2
go
6.
We
will get many rows in the result set from the above command but we have to look
at last two rows of SQLHub database as per given in following screen capture:
7.
We
can see that out UPDATE statement runs with SPID 54 and SELECT statement runs
with SPID 55. SELECT query is blocked by SPID 54 given in row no.2 column no.5
in screen capture. Now I have two ways. Either I issue COMMIT / ROLLBACK
command which is not a good idea to interrupt UPDATE or cancel SELECT query in
SSMS which I will do now.
8.
Since
we have cancelled SELECT query, we will not execute same SELECT statement with
NOLOCK hint.
select * from Orders WITH (NOLOCK) Where orderID<=15
9.
Here
is the result return by above query.
10.
Go
to Win 1 and execute “ROLLBACK” statement.
11.
Go
back to Win 2 and execute SELECT statement with or without “NOLOCK”. For eg:
select * from Orders Where orderID<=15
12.
Here
is the screen shot of result return by above query:
How it works...
When we
have executed UPDATE statement in Step no 3 without COMMIT or ROLLBACK, It
updates the records but didn’t release the lock it has acquired on the table so
SELECT query was not able read data and return it.
How do
you know that whether UPDATE has really updated the values or not? Since our
select query is not giving results. We have executed same select query with
“WITH (NOLOCK)” hint in step no. 8 and we can confirm that values are update
with the screen capture given in step 9. Isn’t it good situation? Yes, may be
as we were not even able to get the result of SELECT statement in step no 4. But
I would say we can’t decide whether it is a good or bad without evaluating the
business need.
Since
UPDATE statement has updated the value but it is not saved so there is a chance
to ROLLBACK. If you see resultset given in steps no 9, you will see “5.00” in
“Amount” column which may not be a proper value as after ROLLBACK, it comes
back to “2.00” again as per step no. 12.
Think if
this would be a result of stock exchange, what would happen? Can we run with
dirty read we seen in screen shot in step no.9? No certainly not that is why I
have conveyed that NOLOCK is all depends on the business need and situation.
Use it wisely as it is two sided sword.
See also
NOLOCK as
known as READUNCOMMITED concept is somehow related to ISOLATION level. Here are the links of some of my past article
on the subject.
Happy Reading!!!
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but
examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”
RSS
Feed
Saturday, July 28, 2012 | 
