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.
- Select (2) starts.
Sch-Slock is obtained on
- Data import (1) runs up to
Sch-Mlock is requested, and the query waits.
- (2) for some reason requests additional
- Deadlock. (1) waits (2) to release the first
Sch-Slock, (2) waits (1) to acquire the second
What is going on? Why do select try to obtain the second lock of the same type on the same table?