Why can a select statement acquire more than 1 Sch-S lock on one table?

Question

I have a repeating deadlock in my production environment. I cannot reproduce it in staging.

Script 1 is a scheduled data import procedure.

-- Create and populate new_TableImported

if object_id('old_TableImported') is not null drop table old_TableImported
set transaction isolation level serializable;
set xact_abort on;
begin try
    begin tran
        if object_id('TableImported') is not null exec sp_rename 'TableImported', 'old_TableImported'
        exec sp_rename 'new_TableImported', 'TableImported'
    commit
end try
begin catch
    if (xact_state() <> 0) rollback tran;
    throw;
end catch
set transaction isolation level read committed;
if object_id('old_TableImported') is not null drop table old_TableImported

Script 2 is a scheduled data export procedure: just select from a view.

CREATE VIEW [export].[vSomeDataNeededOutside]
AS
select
    t.*,
    e.SomeField
from dbo.OneOfMyTables t
    left join (
        select ...
        from TableImported
        group by ...
    ) e on ...
where ...

According to data collected with SQL Server Profiler.

1. Select (2) starts. Sch-S lock is obtained on TableImported.
2. Data import (1) runs up to sp_rename block. Sch-M lock is requested, and the query waits.
3. (2) for some reason requests additional Sch-S lock on TableImported.
4. Deadlock. (1) waits (2) to release the first Sch-S lock, (2) waits (1) to acquire the second Sch-S lock.

What is going on? Why do select try to obtain the second lock of the same type on the same table?

Answer 1

Your query is creating more than one lock because your derived table is getting executed more than once.

Why the sub-query is being executed more than once is another issue?

Check your execution plan. its quite possible the Estimated Number of executions on TableImported matches closely to the Estimated Number of rows for OneOfMyTables
There is some discussion around this here

If you could change the the left join to an inner join, it would only scan (or seek) TableImported once.

As for the difference between Staging and prod... or a solution ¯\_(ツ)_/¯ (sorry)