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
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 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