Locking on database from SELECT INTO #TempTable

Posted on

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.

Leave a Reply

Your email address will not be published. Required fields are marked *