Sql Server DBMS is build such that selecting from a table requires a share lock on the table ( except if hint NOLOCK is used) , and in some situations this can lead to deadlocks .
On the other side, the hint NOLOCK can lead to dirty reads.
Typical case , if we have in DWH a dimension that can filled simultaneosly by more than one ETL process .
The solution is to take a table share lock before inserting, this way
insert into dim_IMSI
select distinct a.SERVED_IMSI from Source_table_A a
left join dim_IMSI exi with(tablock) on
a.SERVED_IMSI= exi.imsi where 1=1 and
exi.imsi_id is null
This insert statement is deadlock safe and is not blocked by long running selects from dim_imsi, because a select takes a share page lock while the insert takes a table exclusive lock , which does not require all the pages of the table to be locked
