Backend Table/Row Locking Structure using CTE Queries

Question

This question is an extension of a previous question that I asked. Table Locking When using Unions

With the same query we are using that is causing blocking the various queries used in these UNIONS are CTE queries.

For example:

CREATE TABLE #TempTable (
    [Columns]
)

WITH CTE1 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE2 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE3 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE4 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE5 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE6 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
),
CTE7 (Columns)
(
    Select [Columns] FROM [Tables] WHERE [WHERE-Condition]
)

INSERT INTO #TempTable
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2
UNION
SELECT * FROM CTE3
UNION
SELECT * FROM CTE4
UNION
SELECT * FROM CTE5
UNION
SELECT * FROM CTE6 WHERE [Where-Condition]
UNION
SELECT * FROM CTE7

Assuming this particular scenario (and we do not have any explicit transactions declared) does the physical table locking structure change, because we are using CTE queries?

When the SELECT for CTE1 is finished as part of the UNION, are the physical tables still locked or are the locks still released?

Based on the answer from my previous question, I was under the impression that in the context of a UNION when the SELECT for one of the various SELECT statements completes the tables are released. Is that incorrect?

We have not modified the isolation level for this transaction. As I understand it, that means we are using READ_COMMITTED. The question is still applicable with just 2 SELECT statements. I am trying to get an understanding on how this functionality works. The particular query I am working with uses 7, which is the only reason I specified 7.

Answer 1

No, the locks will not change because of CTEs. What you have shown are non-recursive CTEs. These are just another way of introducing a sub-query into the SQL statement. The query

with some_CTE as
(
  select < whatever > from < something >
)
select * from some_CTE;

is semantically identical to

select * from
(
  select < whatever > from < something >
) as some_alias;

As the query optimizer is free to rearrange your statement into any logical equivalent it is likely these two statements will end up with identical execution plans. (For very complex queries, where the CTE or the main query has many joins, or the CTE is referenced many times, the execution plans may be different, but this is an artefact of the optimization, not a fundamental property of how CTEs work.)

This paragraph is tangential to your main point about locking, but worth knowing. You say "When the SELECT for CTE1 is finished.." It doesn't necessarily work like that. The execution may proceed in any manner that satisfies the meaning of our query but does not have to follow the sequence in which it was written. See here and here for examples of how execution order may differ to written order.

As to your main question "I was under the impression that .. when the SELECT .. completes the tables are released. Is that incorrect?" I'll refer you to this Microsoft page:

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

Here is another explanation which may explain it better. Both sources make clear that the granularity of locking (row, page or table) affect when the lock is released. There are many things that affect this, including system configuration and lock escalation.

I'll note also that your example uses UNION. This removes duplicates from the results. You may get different lock behaviour using UNION ALL, but that leaves duplicates and could return more rows, depending on your data.