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