Question :
We had an issue on our live DB, where a query executed from the test instance, that referenced the live instance caused blocks due to it showing as ‘suspended’, which caused user requests to back up, all due to this one query.
Looking at the query, there is one request at the start that references the live DB:
SELECT [Columns]
INTO #TempTable
FROM [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].Users AS sv
RIGHT JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].Company AS co
INNER JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].Company_Marketing AS cm ON co.co_ref = cm.cmk_co_ref
INNER JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].Property AS pr ON co.co_pr_ref = pr.pr_ref
LEFT JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].Company_Types AS ct ON co.co_cot_id = ct.cot_id
LEFT JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].Users AS cb ON cm.cmk_called_by = cb.us_id
LEFT JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].Marketing_Disciplines AS md ON cm.cmk_mdi_id = md.mdi_id
LEFT JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].REGIONS AS rg ON sv.us_reg_id = rg.reg_id
LEFT JOIN [LIVESERVERLIVEINSTANCE].[LIVEDB].[dbo].REGIONS AS rg1 ON CB.us_reg_id = rg1.reg_id
WHERE cm.cmk_appointment > '2017-01-01'
AND md.mdi_mdg_id <> 2
ORDER BY cm.cmk_appointment
This was being run against a test instance, but referencing live (there is a linked server). Changing it to run on the correct live instance means that it ran in a matter of seconds, and no blocking issues.
We are tidying up the query, as it is far from efficient anyway, but, what I am wanting to understand is, how does SELECT INTO behave in terms of blocks/locks? Would the query above mean that blocks are present on the tables in the query whilst #TempTable is populated?
Thanks for your assistance.
Answer :
I would expect the same behavior with or without the INTO
. Remote queries run with default READ COMMITTED isolation level behavior acquire and release shared locks during execution just like local queries. Those locks can block writers of the remote tables, especially with poorly written queries and if shared table-level locks are held for the duration of the query.
Separately, the local SELECT INTO query consists of two separate transactions when run in autocommit mode. This is done to avoid holding long-term locks on the system catalog objects which could otherwise block object creation by other sessions. Creation of the new table is done in the first transaction and then the table is loaded using the remote query source in the second transaction.