SQL Server 2008: how does NOLOCK works for CTE?

Question

I have CTEs which all uses NOLOCK inside. But then selects from those CTEs in parent CTEs using children CTEs doesn't use NOLOCK in presumption that it is already NOLOCK'd. And the final select doesn't use NOLOCK either.

Something like that:

with cte1 as
(select * from tab1 (nolock)),
cte2 as
(select * from cte1)

select * from cte2

or shall I write

with cte1 as
(select * from tab1 (nolock)),
cte2 as
(select * from cte1 (nolock))

select * from cte2 (nolock)

thanks

I wouldn't use `SELECT *` or `NOLOCK` at all. Seeing that CTEs are supported on SQL 2005 and higher, there are better ways to ensure you're reading committed but historic data than NOLOCK. — , Jan 05 '11 at 18:18
I dont want long running report to be locking a table.. whats my other options are? — Boppity Bop, Jan 06 '11 at 11:28

Martin Smith · Accepted Answer · 2011-01-05T18:25:18.440

5

You don't need the outer nolock to avoid taking shared locks on tab1. You can easily verify this by setting up a SQL Profiler trace capturing the various events in the locks category, filtering on the spid of an SSMS connection and trying both versions.

nolock is quite a dangerous setting though, are you aware of all of the possible downsides to using it (dirty reads, reading data twice or not at all)?

edited Jan 05 '11 at 18:25

answered Jan 05 '11 at 18:19

Martin Smith

438,706
87
741
845

score 3 · Answer 2 · edited May 23 '17 at 12:03

NOLOCK on an "outer" query applies to any inner queries too. A CTE is just a macro like a view or inline table udf: nothing more, nothing less. So you actually have (ignoring NOLOCK hints)

select * from (
               select * from (
                             select * from tab1
                             ) t1
               ) t2

From Table Hints on MSDN, under "Remarks"

All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view.

In this case, you need only one. Doesn't matter where.

Where it does matter is where you have JOINs. If cte1 was a join of 2 tables, you'd need it for each table,. Or specify it once at a higher/outer level.

Oh, and I'll join in with everyone else: NOLOCK is a bad idea

score 1 · Answer 3 · answered Jan 05 '11 at 18:20

1

The innermost nolock is sufficient, no need to repeat it for the outer selects.

You can test this by starting a transaction without ending it:

begin transaction
; with YourCte ( ...

Then you can view the locks using Management Studio. They'll be there until the transaction times out.

answered Jan 05 '11 at 18:20

Andomar

232,371
49
380
404

Depends on the isolation level. Shared locks are normally released as soon as the data is read. – Martin Smith Jan 05 '11 at 18:21

score 0 · Answer 4 · answered Jan 05 '11 at 18:37

0

NOLOCK for CTEs work the same way as with everything else: it causes inconsistent results. Use SNAPSHOT instead, see SQL Server 2005 Row Versioning-Based Transaction Isolation.

answered Jan 05 '11 at 18:37

Remus Rusanu

288,378
40
442
569

SQL Server 2008: how does NOLOCK works for CTE?

4 Answers4