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?
Best Answer
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 forOneOfMyTables
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)