NOLOCK or NOSHAREDLOCK? Which name is apt for this hint?

“Is NOLOCK hint in SQL Server acquires any locks?”

I’m not joking.  The answer is yes! Let me show this with an example. Run the below query

— Session 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

GO

BEGIN TRAN

— Example Query

SELECT           SOD.SalesOrderID, SOD.CarrierTrackingNumber,

ProductName = P.Name, P.ProductNumber, P.Color

FROM        dbo.SalesOrderDetail SOD

INNER JOIN  dbo.Product P

ON                P.ProductID = SOD.ProductID;

In a second session run the below script.

— Session 2

— Lock Info. Query

SELECT

TL.resource_type, TL.request_mode, TL.request_type

FROM sys.dm_tran_locks TL;

You can see a lot of Shared locks (S) and Intent Shared (IS) locks from the Lock Info Query result.

Shared Locks

Again in Session 1, commit the transaction

COMMIT TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Now run the same query with NOLOCK hint in session 1.

— Session 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

GO

BEGIN TRAN

— Example Query

SELECT

SOD.SalesOrderID, SOD.CarrierTrackingNumber,

ProductName = P.Name, P.ProductNumber, P.Color

  FROM        dbo.SalesOrderDetail SOD WITH (NOLOCK)

INNER JOIN  dbo.Product P WITH (NOLOCK)

ON                P.ProductID = SOD.ProductID;

In the second session run again Lock Info. Query.

No Shared Locks

You still can see some kind of lock there! This is Schema Stability Lock (Sch-S) that prevents any schema modification in the table during the scanning for rows when NOLOCK hint is present. Think about a situation when we read data from a table in READ UNCOMMITTED isolation level (synonym to NOLOCK) hint and another transaction removes a column! Schema Stability Locks prevent that from happening.

Don’t forget to commit the transaction in session 1.

COMMIT TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

What I’m thinking is the hint is wrongly worded as NOLOCK. It should have been called NOSHAREDLOCK to avoid the confusion.

What do you think?

Advertisements

Leave a comment

Filed under Internals

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s