//
you're reading...
SQL Server, T-SQL

Concurrency and NOLOCK

There is generally confusion over the effect of the NOLOCK query hint and locking in general when selects are run. A SELECT in SQL Server will place a shared lock on a table row – and a second SELECT would also require a shared lock, and those are compatible with one another.

So no – one SELECT cannot block another SELECT.

What the WITH (NOLOCK) query hint is used for is to be able to read data that’s in the process of being inserted (by another connection) and that hasn’t been committed yet.

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation’s transaction has been committed (or rolled back).

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren’t going to be inserted at all, in the end (if the INSERT transaction is rolled back) – so your e.g. report might show data that’s never really been committed to the database.

There’s another query hint that might be useful – WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any “dirty” un-committed data – but it might skip some rows, e.g. not show all your rows in the table.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

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

%d bloggers like this: