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'
end try
begin catch
    if (xact_state() <> 0) rollback tran;
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]
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?

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

  • If I manually execute (2) and watch actual execution plan, it shows one execution of subquery and merge join with OneOfMyTables. I understand that sometimes optimizer can decide to perform nested loops. But sounds very strange: does SQL Server really request new Sch-S for each loop without releasing the previous one? So, if there are (for example) 20000 nested loops, 20000 Sch-S are being aquired simultaneously?
    – aleyush
    Jun 5, 2019 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.