Table Locking When using Unions

Question

When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed?

For example, 100 individual select statements are concatenated together via UNION/UNION ALL. (Assuming they are resolved sequentially) If the first SELECT in the transaction is completed and another transaction in a different session attempts to access the same data, is the data still locked by the UNION?

I am just trying to get an understanding on how UNIONs behave with the physical tables while they are still executing.

For the query which prompted this there are 7 different statements in the UNION (Not UNION ALL). With this particular question I was just wanting to get an understanding on the back-end architecture of how the UNION/UNION ALL worked. The query which prompted this question causes a lot of blocking but only runs for about a minute, so I was trying to get an understanding on if it was the whole query causing it or just a few of the individual pieces.

I have added a follow-up question here: Backend Table/Row Locking Structure using CTE Queries

Answer 1

All the select .. union .. select .. bits count as a single statement. I can't find an article in the documentation to justify this but I can offer supporting evidence.

First, the semicolon is the Transact-SQL statement terminator. There is only one place in your query it will be accepted and that's at the very end of the very last SELECT. Second, the execution plan consists of a single tree, indicative of a single statement. In contrast two queries submitted as a single batch would cause two disjoint execution trees.

If there is an explicit transaction in place the select..union becomes part of that and the locks are held until the COMMIT or ROLLBACK. If you have an implicit transaction, by which I mean autocommit, not SET IMPLICIT_TRANSACTIONS, it spans all of the selects. Locks will be held for however long they would be held absent the UNION, governed by isolation level, query options (TABLOCKX etc.), lock escalation, trace flags .. and all the other things that affect this.