Sample Header Ad - 728x90

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

0 votes
1 answer
181 views
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. 1. Data import (1) runs up to sp_rename block. Sch-M lock is requested, and the query waits. 1. (2) for some reason requests additional Sch-S lock on TableImported. 1. 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?
Asked by aleyush (101 rep)
Jun 2, 2019, 06:20 PM
Last activity: Jun 29, 2025, 07:02 PM